当前位置:   article > 正文

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

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';
  • 1
  • 2
  • 3
  • 4

【说明】
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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

【字段说明】: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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
DEPT_NO(部门编号)DEPT_TREE(部门层级树)BENIFIT(利润[万元])
101A.A1.10150
102A.A1.10220
201A.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';
  • 1
  • 2
  • 3
  • 4
DEPT_NOBENIFIT
A80
A280
20180
A20
A120
10220
A50
A150
10150

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;
  • 1
  • 2
  • 3
  • 4
  • 5
DEPT_NOBENIFIT
A150
A170
A280
10150
10220
20180
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/IT小白/article/detail/655736
推荐阅读
相关标签
  

闽ICP备14008679号