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;
           

源資料

導入資料

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