转载 https://www.cnblogs.com/qingyunzong/p/8746159.html

 

现有原始 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 或者自定义函数完成）

A.　get_json_object(string json_string, string path)

返回值: string  

说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。  这个函数每次只能返回一个数据项。

0: jdbc:hive2://hadoop3:10000> select get_json_object('{"movie":"594","rate":"4","timeStamp":"978302268","uid":"1"}','$.movie');

创建json表并将数据导入进去

0: jdbc:hive2://hadoop3:10000> create table json(data string);
No rows affected (0.983 seconds)
0: jdbc:hive2://hadoop3:10000> load data local inpath '/home/hadoop/json.txt' into table json;
No rows affected (1.046 seconds)
0: jdbc:hive2://hadoop3:10000>

 

0: jdbc:hive2://hadoop3:10000> select 
. . . . . . . . . . . . . . .> get_json_object(data,'$.movie') as movie 
. . . . . . . . . . . . . . .> from json；

B.　json_tuple(jsonStr, k1, k2, ...)

参数为一组键k1，k2……和JSON字符串，返回值的元组。该方法比 get_json_object 高效，因为可以在一次调用中输入多个键

复制代码
0: jdbc:hive2://hadoop3:10000> select 
. . . . . . . . . . . . . . .>   b.b_movie,
. . . . . . . . . . . . . . .>   b.b_rate,
. . . . . . . . . . . . . . .>   b.b_timeStamp,
. . . . . . . . . . . . . . .>   b.b_uid   
. . . . . . . . . . . . . . .> from json a 
. . . . . . . . . . . . . . .> lateral view json_tuple(a.data,'movie','rate','timeStamp','uid') b as b_movie,b_rate,b_timeStamp,b_uid;
复制代码