Hive练习题之炸裂函数(二)

参考文章: https://blog.csdn.net/guodong2k/article/details/79459282

建表

drop table test.explode_lateral_view;
create table test.explode_lateral_view
(`area` string,
`goods_id` string,
`sale_info` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS textfile;

源数据

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"}]

导入数据

load data local inpath "/doit16/explode_lateral_view.txt" into table test.explode_lateral_view;

+-------------------------------+--------------------------------+----------------------------------------------------+
|   explode_lateral_view.area   | explode_lateral_view.goods_id  |           explode_lateral_view.sale_info           |
+-------------------------------+--------------------------------+----------------------------------------------------+
| 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"}] |
+-------------------------------+--------------------------------+----------------------------------------------------+

explode函数的使用

拆解array 字段

select explode(split(goods_id,',')) as goods_id 
from explode_lateral_view;

– 结果

+-----------+
| goods_id  |
+-----------+
| 1         |
| 2         |
| 3         |
| 4         |
| 5         |
| 6         |
| 7         |
| 8         |
| 9         |
+-----------+

– 拆解map 字段

select explode(split(area,',')) as area 
from explode_lateral_view;

– 结果

+-------------+
|    area     |
+-------------+
| a:shandong  |
| b:beijing   |
| c:hebei     |
+-------------+

– 拆解json字段

select explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')) as  sale_info 
from explode_lateral_view;

+----------------------------------------------------+
|                     sale_info                      |
+----------------------------------------------------+
| "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" |
+----------------------------------------------------+

LATERAL VIEW的使用

侧视图的意义是配合explode（或者其他的UDTF），一个语句生成把单行数据拆解成多行后的数据结果集。

select goods_id2,sale_info 
from explode_lateral_view 
LATERAL VIEW explode(split(goods_id,','))goods as goods_id2;

+------------+----------------------------------------------------+
| goods_id2  |                     sale_info                      |
+------------+----------------------------------------------------+
| 1          | [{"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"}] |
| 2          | [{"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"}] |
| 3          | [{"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"}] |
| 4          | [{"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"}] |
| 5          | [{"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"}] |
| 6          | [{"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"}] |
| 7          | [{"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"}] |
| 8          | [{"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"}] |
| 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"}] |
+------------+----------------------------------------------------+