hive - 解析 json

内置函数：get_json_object(json串,解析路径)

解析路径说明：

$ :跟对象

. :子对象

[] :数组下标

* :所有

举例：

数据样例：

{"movie":"1190","rate":"4.8","timestamp":"978300760","uid":"145325"}

{"movie":"1191","rate":"3.7","timestamp":"978200613","uid":"234566"}

{"movie":"1190","rate":"4.5","timestamp":"977500902","uid":"474532"}

...

将数据导入hive：

create table rating(line string);#建表，只有一列数据 load data local inpath '/user/rating.json' into table rating;#从本地导数据

 

1)求评分次数排名前3的电影

SELECT get_json_object(t.line, '$.movie') as movieid, count(*) as tatol FROM rating t GROUP BY 1 ORDER BY 2 LIMIT 3;

• t.line是要解析的json串，
• $定位到第一级目录{
• .定位到第二级目录"movie":"1190","rate":"4.8","timestamp":"978300760","uid":"145325"
• movie：通过key定位到value值1190

 

2)将整个json文件解析成一张表

1)建一个用于存放数据的表

create table rate(movie int,rate float,time bigint,uid bigint);

 

2)解析并加载数据

insert into table rate select get_json_object(t.line, '$.movie') as movie, get_json_object(t.line, '$.rate') as rate, get_json_object(t.line, '$.timestamp') as time, get_json_object(t.line, '$.uid') as uid FROM rating t;

 

3)解析较复杂json

{

"status": 0,

"data": {"search_data":[{

"name": "奈良市",

"location": {

"lat": 34.685087

}]

}

1-解析status

get_json_object(t.line, '$.status');

 

2-解析name

get_json_object(t.line, '$.data.search_data.[0].name');

 

3-解析lat

get_json_object(t.line, '$.data.search_data.[0].location.lat');