[1179]hive的lateral view用法

周小董

发布于 2023-10-10 08:27:49

4560

发布于 2023-10-10 08:27:49

文章被收录于专栏：python前行者

1. lateral view 简介

hive函数 lateral view 主要功能是将原本汇总在一条（行）的数据拆分成多条（行）成虚拟表，再与原表进行笛卡尔积，从而得到明细表。配合UDTF函数使用，一般情况下经常与explode函数搭配，explode的操作对象（列值）是 ARRAY 或者 MAP ,可以通过 split 函数将 String 类型的列值转成 ARRAY 来处理。

【语法格式】

select col_A,col_B,tmp_table.tmp_col 
from test_table 
lateral view explode(split(col_C,'分隔符')) tmp_table as tmp_col
where partition_name='xxx';

【说明】 col_A，col_B，col_C：都是原表 test_table 的列（字段）； tmp_table：explode形成的新虚拟表，可以不写； tmp_col：explode 形成的新列（字段）；

2. 实操

2.1 建表(hive)

创建一个“部门利润表”，按照日期分区，一共三个字段，“部门编号”、“部门层级树”、“利润（万元）”。DDL语句如下：

drop table if exists zero_test_01:
create table zero_test_01 (
    DEPT_NO    string    comment'部门编号',
    DEPT_TREE  string    comment'部门层级树',
    BENIFIT    int       comment'利润（万元）'
)
comment '测试-部门利润表'
partitioned by (deal_date string comment '日期分区' )
stored as orc;

【字段说明】：DEPT_TREE 字段是按照“一级部门编号.二级部门编号.三级部门编号” 模式进行取值的。

2.2 插入数据

往“20220516”分区中插入三条数据。

alter table zero_test_01 drop if exists partition (DEAL_DATE='20220516');
insert into table zero_test_01 partition (DEAL_DATE='20220516')
select '101','A.A1.101',50;
insert into table zero_test_01 partition (DEAL_DATE='20220516')
select '102','A.A1.102',20;
insert into table zero_test_01 partition (DEAL_DATE='20220516')
select '201','A.A2.201',80;

DEPT_NO（部门编号）	DEPT_TREE（部门层级树）	BENIFIT（利润[万元]）
101	A.A1.101	50
102	A.A1.102	20
201	A.A2.201	80

2.3 转成多行

利用 lateral view 和 explode 函数将 DEPT_TREE（部门层级树）列按照.分割转成多行，通过结果可以看到，lateral view函数将 “部门层级树” 字段炸开进行了扩展，每个部门（DEPT_NO）都有与之对应的利润（BENIFIT），从三行数据直接变成9行数据。

select tmp_dept_no as DEPT_NO, BENIFIT
from zero_test_01 
LATERAL VIEW explode (split(DEPT_TREE, '\\.')) tmp as tmp_dept_no
where DEAL_DATE='20220516';

DEPT_NO	BENIFIT
A	80
A2	80
201	80
A	20
A1	20
102	20
A	50
A1	50
101	50

2.4 汇总求和

对部门利润进行向上汇总求和，可以看到每个部门的总利润。

select tmp_dept_no as DEPT_NO, sum(BENIFIT) as BENIFIT
from zero_test_01
LATERAL VIEW explode (split(DEPT_TREE, '\\.')) tmp as tmp_dept_no
where DEAL_DATE='20220516'
group by tmp_dept_no;