五、Hive的JSON，复杂数据类型处理解读

• • 5.1. Hive处理JSON
  • 5.2. Transform语法
  • 5.3. Hive的复杂数据类型
  • • 5.3.1. array
    • 5.3.2. map
    • 5.3.3. struct

现有原始 JSON 数据（rating.json）如下：

{"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}
{"movie":"661","rate":"3","timeStamp":"978302109","uid":"1"}
{"movie":"914","rate":"3","timeStamp":"978301968","uid":"1"}
{"movie":"3408","rate":"4","timeStamp":"978300275","uid":"1"}
{"movie":"2355","rate":"5","timeStamp":"978824291","uid":"1"}
.....
{"movie":"1197","rate":"3","timeStamp":"978302268","uid":"1"}
{"movie":"1287","rate":"5","timeStamp":"978302039","uid":"1"}
{"movie":"2804","rate":"5","timeStamp":"978300719","uid":"1"}
{"movie":"594","rate":"4","timeStamp":"978302268","uid":"1"}

现在需要将数据导入到hive仓库中，并且最终要得到这么一个结果：

该怎么做？（提示：可用内置 get_json_object 或者 自定义函数完成）

1、尝试使用内置函数搞定。get_json_object()
2、如果你不曾知道有内置函数支持，尝试编写UDF
3、尝试编写处理脚本（shell，python）来处理json，依然还是在hive中。

解决：

1、先加载 rating.json 文件到 hive 的一个原始表 rate_json_1_raw

create database if not exists nx_de_json_db;
use nx_de_json_db;
drop table if exists rate_json;
create table rate_json(line string) row format delimited;
load data local inpath '/home/bigdata/rating.json' into table rate_json;

2、创建 rate_json_2_detail这张表用来存储解析 json 出来的字段：

create table rate_json_2_detail (movie int, rate int, unixtime int, userid int) row format delimited fields terminated by 't';

解析 json，得到结果之后存入 rate_json_2_detail表

insert into table rate_json_2_detail
select
get_json_object(line,'$.movie') as moive,
get_json_object(line,'$.rate') as rate,
get_json_object(line,'$.timeStamp') as unixtime,
get_json_object(line,'$.uid') as userid
from rate_json_1_raw;

3、检查

select * from rate_json_2_detail limit 3;

结果：

moive rate unixtime userid
1193 5 978300760 1
661 3 978302109 1
914 3 978301968 1

需求：把时间戳变成星期编号（0-7）

解决方案：

1、内置函数
2、自定义函数
3、使用Transform语法： 使用外部脚本充当自定义函数 y=f(x)

方案：

1、函数 或者 自定义函数 轻松实现转换
2、运维人员编写 Shell 脚本，或者 Python 脚本，hive 来调用执行转换。这就是 transform

先编辑一个 python 脚本文件：weekday_mapper.py

#!/bin/python
import sys
import datetime
for line in sys.stdin:
    line = line.strip()
    movie,rate,unixtime,userid = line.split('t')
    weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
    print 't'.join([movie, rate, str(weekday), userid])

然后，将文件加入 hive 的 classpath：

hive> add file /home/bigdata/weekday_mapper.py;

创建最后的用来存储调用 python 脚本解析出来的数据的表：rate_json_3_result

hive> create table rate_json_3_result(movie int, rate int, weekday int, userid int) row format delimited fields terminated by 't';

执行转换：

hive> insert into table rate_json_3_result
select transform(movie,rate,unixtime,userid) using 'python weekday_mapper.py' as(movie,rate,weekday,userid) from rate_json_2_detail;

最后查询看数据是否正确：

hive> select * from rate_json_3_result limit 10;
hive> select distinct(weekday) from rate_json_3_result;

复杂数据类型包括数组（ARRAY）、映射（MAP）和结构体（STRUCT），具体如下所示：

完美示例：

CREATE TABLE student(
    name STRING,
    favors ARRAY,
    scores MAP,
    address STRUCT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY 't'
COLLECTION ITEMS TERMINATED BY ';'
MAP KEYS TERMINATED BY ':' ;

说明：

1. 字段name是基本类型string，
   • favors是数组类型array，可以保存很多爱好，
   • scores是映射类型map，可以保存多个课程的成绩
   • address是结构类型struct，可以存储住址信息
2. ROW FORMAT DELIMITED是指明后面的关键词是列和元素分隔符的
3. FIELDS TERMINATED BY 是字段分隔符
4. COLLECTION ITEMS TERMINATED BY是元素分隔符（Array中的各元素、Struct中的各元素、Map中的key-value对之间）
5. MAP KEYS TERMINATED BY是Map中key与value的分隔符
6. LINES TERMINATED BY是行之间的分隔符
7. STORED AS TEXTFILE指数据文件上传之后保存的格式

总结：

• 在关系型数据库中，我们至少需要三张表来定义，包括学生基本表、爱好表、成绩表
• ；但在Hive中通过一张表就可以搞定了。
• 也就是说，复合数据类型把多表关系通过一张表就可以实现了。但是也要切记，字段也不应该太复杂，否则应该进行数据预处理，把数据打散分裂成多个字段。

数据：table_array.txt

huangbo-beijing,shanghai,tianjin,hangzhou
xuzheng-changchu,chengdu,wuhan
wangbaoqiang-dalian,shenyang,jilin

建表语句：

create table table_array(name string, work_locations array)
row format delimited fields terminated by '-'
collection items terminated by ',';

导入数据：

load data local inpath '/home/bigdata/table_array.txt' into table table_array;

查询语句：

Select * from table_array;
Select name from table_array;
Select work_locations from table_array;
Select work_locations[0] as first_location from table_array;

数据：table_map.txt

huangbo-yuwen:80,shuxue:89,yingyu:95
xuzheng-yuwen:70,shuxue:65,yingyu:81
wangbaoqiang-yuwen:75,shuxue:100,yingyu:75

建表语句：

create table table_map(name string, scores map)
row format delimited fields terminated by '-'
collection items terminated by ','
map keys terminated by ':';

导入数据：

load data local inpath '/home/bigdata/table_map.txt' into table table_map;

查询语句：

Select * from table_map;
Select name from table_map;
Select scores from table_map;
Select s.scores['yuwen'] as yuwen from table_map s;

数据：table_struct.txt

1-english,80,3.3
2-math,89,3.9
3-chinese,95,4.7

建表语句：

create table table_struct(id int, course struct)
row format delimited fields terminated by '-'
collection items terminated by ',';

导入数据：

load data local inpath '/home/bigdata/table_struct.txt' into table table_struct;

查询语句：

select * from table_struct;
select id from table_struct;
select course from table_struct;
select t.course.name as name, t.course.score as score, t.course.jidian as jidian
from table_struct t;