一、测试数据
user1;18;male;{"id":1,"ids":101,102,103],"total_number":3}
user2;20;female;{"id":2,"ids":[201,202,203,204],"total_number":4}
user3;23;male;{"id":3,"ids":[301,302,303,304,305],"total_number":5}
user4;17;male;{"id":4,"ids":[401,402,403,304],"total_number":5}
user5;35;female;{"id":5,"ids":[501,502,503],"total_number":3}
二、创建测试表并加载数据
CREATE TABLE IF NOT EXISTS jsont1(
username string,
age int,
sex string,
json string
)
row format delimited fields terminated by ';';
load data local inpath '/data/lagoudw/data/weibo.json'
overwrite into table jsont1;
三、hive解析json格式的函数
1、三个重要函数
get_json_object(string json_string, string path)
返回值：String
说明：解析json字符串json_string，返回path指定的内容；如果输入的json字符串无效，那么返回NUll；函数每次只能返回一个数据项；
json_tuple(jsonStr, k1, k2, ...)
返回值：所有的输入参数、输出参数都是String；
说明：参数为一组键k1，k2，。。。。。和json字符串，返回值的元组。该方法比get_json_object高效，因此可以在一次调用中输入多个键；
explode
使用explod将Hive一行中复杂的 array 或 map 结构拆分成多行。
2、get单层值
-- get 单层值
select username, age, sex, get_json_object(json, "$.id") id,
get_json_object(json, "$.ids") ids,
get_json_object(json, "$.total_number") num
from jsont1;
3、get 数组
select username, age, sex, get_json_object(json, "$.id") id,
get_json_object(json, "$.ids[0]") ids0,
get_json_object(json, "$.ids[1]") ids1,
get_json_object(json, "$.ids[2]") ids2,
get_json_object(json, "$.ids[3]") ids3,
get_json_object(json, "$.total_number") num
from jsont1;
4、使用 json_tuple 一次处理多个字段
select json_tuple(json, 'id', 'ids', 'total_number')
from jsont1;
这种解析方式有个缺点，当想在查询结果中加入其他字段时会报错
select username, age, sex, json_tuple(json, 'id', 'ids',
'total_number')
from jsont1;
5、使用 explode + lateral view
第一步，将 [101,102,103] 中的 [ ] 替换掉
select regexp_replace("[101,102,103]", "\\[|\\]", "");
第二步，将上一步的字符串变为数组
select split(regexp_replace("[101,102,103]", "\\[|\\]", ""),
",");
第三步，使用explode + lateral view 将数据展开
select username, age, sex, id, ids, num
from jsont1
lateral view json_tuple(json, 'id', 'ids', 'total_number') t1
as id, ids, num;
with tmp as(
select username, age, sex, id, ids, num
from jsont1
lateral view json_tuple(json, 'id', 'ids', 'total_number') t1
as id, ids, num
)
select username, age, sex, id, ids1, num
from tmp
lateral view explode(split(regexp_replace(ids, "\\[|\\]", ""),
",")) t1 as ids1;
