hive 的 lateral view用法以及注意事项

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语句如下：

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

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

2.2 插入数据

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

altertable zero_test_01 dropifexistspartition(DEAL_DATE='20220516');insertintotable zero_test_01 partition(DEAL_DATE='20220516')select'101','A.A1.101',50;insertintotable zero_test_01 partition(DEAL_DATE='20220516')select'102','A.A1.102',20;insertintotable zero_test_01 partition(DEAL_DATE='20220516')select'201','A.A2.201',80;

DEPT_NO（部门编号）DEPT_TREE（部门层级树）BENIFIT（利润[万元]）101A.A1.10150102A.A1.10220201A.A2.20180

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_NOBENIFITA80A28020180A20A12010220A50A15010150

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'groupby tmp_dept_no;

DEPT_NOBENIFITA150A170A280101501022020180