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;