hive> select * from explode_lateral_view;
OK
a:shandong,b:beijing,c:hebei 1,2,3,4,5,6,7,8,9 [{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]
Time taken: 0.384 seconds, Fetched: 1 row(s)
hive> desc explode_lateral_view;
OK
area string
goods_id string
sale_info string
Time taken: 0.435 seconds, Fetched: 3 row(s)
[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},
{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},
{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]
json格式里面是四个字段值
// 先拆解goods_id字段值
hive> select split(goods_id,',') as goods_id from explode_lateral_view;
["1","2","3","4","5","6","7","8","9"]
hive> select explode(split(goods_id,',')) as goods_id from explode_lateral_view;
OK
1
2
3
4
5
6
7
8
9
列转行
// 再拆解area字段
hive> select split(area,',') as area from explode_lateral_view;
["a:shandong","b:beijing","c:hebei"]
hive> select explode(split(area,',')) as area from explode_lateral_view;
OK
a:shandong
b:beijing
c:hebei
// 拆解json字段:使用get_json_object函数:
hive> select split(regexp_replace(regexp_replace(sale_info,'\[\{',''),'}]',''),'},\{') from explode_lateral_view;
[""source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"",""source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"",""source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0""]
hive> select explode(split(regexp_replace(regexp_replace(sale_info,'\[\{',''),'}]',''),'},\{')) from explode_lateral_view;
OK
"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"
"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"
"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"
Time taken: 0.222 seconds, Fetched: 3 row(s)
select conditions
from table1
--结果只有一行
[{"redPoint":false,"latitude":39.13589859008789,"pingYin":"tianjin","label":"天津","hotRecommend":false,"type":1,"pinYin":"tianjin","gType":0,"scope":0,"selectedType":0,"conditionType":-1,"value":"66","longitude":117.18800354003906},
{"gType":0,"redPoint":false,"latitude":0.0,"scope":0,"selectedType":0,"conditionType":-1,"label":"离店日期","hotRecommend":false,"type":3,"value":"2020-06-02","longitude":0.0},
{"gType":0,"redPoint":false,"latitude":0.0,"scope":0,"selectedType":0,"conditionType":-1,"label":"入住日期","hotRecommend":false,"type":2,"value":"2020-06-01","longitude":0.0},
{"gType":4,"redPoint":false,"latitude":0.0,"scope":0,"selectedType":0,"conditionType":-1,"label":"推荐排序","hotRecommend":false,"type":4,"value":"1","longitude":0.0}]
使用lateral view explode函数后
select conditions
from table1
lateral view explode(udf.json_split(conditions)) tb as lable
--结果变成4行
{"row_id":0,"json_string":"{"redPoint":false,"latitude":39.13589859008789,"pingYin":"tianjin","label":"天津","hotRecommend":false,"type":1,"pinYin":"tianjin","gType":0,"scope":0,"selectedType":0,"conditionType":-1,"value":"66","longitude":117.18800354003906}"}
{"row_id":1,"json_string":"{"gType":0,"redPoint":false,"latitude":0.0,"scope":0,"selectedType":0,"conditionType":-1,"label":"离店日期","hotRecommend":false,"type":3,"value":"2020-06-02","longitude":0.0}"}
{"row_id":2,"json_string":"{"gType":0,"redPoint":false,"latitude":0.0,"scope":0,"selectedType":0,"conditionType":-1,"label":"入住日期","hotRecommend":false,"type":2,"value":"2020-06-01","longitude":0.0}"}
{"row_id":3,"json_string":"{"gType":4,"redPoint":false,"latitude":0.0,"scope":0,"selectedType":0,"conditionType":-1,"label":"推荐排序","hotRecommend":false,"type":4,"value":"1","longitude":0.0}"}
