explode与lateral view_odps lateral view explode

概述

explode与lateral view在关系型数据库中本身是不该出现的，因为他的出现本身就是在操作不满足第一范式的数据（每个属性都不可再分），本身已经违背了数据库的设计原理（不论是业务系统还是数据仓库系统），不过大数据技术普及后，很多类似pv，uv的数据，在业务系统中是存贮在非关系型数据库中，用json存储的概率比较大，直接导入hive为基础的数仓系统中，就需要经过ETL过程解析这类数据，explode与lateral view在这种场景下大显身手。

创建表

create table if not exists explode_lateral_view(
    area string
    ,goods_id string
    ,sale_info string
)
COMMENT "explode_lateral_view";
1
2
3
4
5
6

插入数据

insert into explode_lateral_view 
values ('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"}]');
1
2

查询数据

select * from explode_lateral_view;
1

explode的使用

拆解area

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

拆解goods_id

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

拆解json

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

LATERAL VIEW的使用

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

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

其中LATERAL VIEW explode(split(goods_id,’,’))goods相当于一个虚拟表，与原表explode_lateral_view笛卡尔积关联。

也可以多重使用

select goods_id2,area2,sale_info
from explode_lateral_view
LATERAL VIEW explode(split(goods_id,','))goods as goods_id2
LATERAL VIEW explode(split(area,','))area as area2;
1
2
3
4

explode&lateral view

从sale_info字段中找出所有的monthSales并且行展示

select get_json_object(concat('{',sale_info_r,'}'),'$.monthSales') as monthSales from explode_lateral_view
LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{'))sale_info as sale_info_r;
1
2

把这个json格式的一行数据，完全转换成二维表的方式展现

select get_json_object(concat('{',sale_info_1,'}'),'$.source') as source,
    get_json_object(concat('{',sale_info_1,'}'),'$.monthSales') as monthSales,
    get_json_object(concat('{',sale_info_1,'}'),'$.userCount') as monthSales,
    get_json_object(concat('{',sale_info_1,'}'),'$.score') as monthSales
from explode_lateral_view
LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{'))sale_info as sale_info_1;
1
2
3
4
5
6

参考