Hive之json数据处理

测试数据：

{"movie":"2797","rate":"3","time":"978302205","userid":"1"}
{"movie":"3595","rate":"5","time":"978302103","userid":"2"}
{"movie":"5462","rate":"4","time":"978303212","userid":"3"}

建表：

create table rating_json(
json string
);

插入数据：

insert into table rating_json values ('{"movie":"2797","rate":"3","time":"978302205","userid":"1"}');
//注意：insert into values语法 Hive 是支持的，但是尽量少用。

重要函数：json_tuple

desc function extended json_tuple;

查询：

select json_tuple(json,'movie','rate','time','userid') as(movie, rate, time, userid) from rating_json;

select userid, movie, rate, time,
from_unixtime(cast(time as bigint)) as time,
year(from_unixtime(cast(time as bigint))) as year
from 
(select json_tuple(json,'movie','rate','time','userid') as(movie, rate, time, userid) from rating_json) t;