[hive]lateral view炸开两层

一. 说明

1.lateral view 侧视图
  lateral view要和explode/split等UDTF函数一起使用,他可以在函数拆分成多行的基础上,
  将表其他字段按照拆分结果进行聚合.
2.用法:表名 lateral view explode(列) 表别名 as 列别名;

二.例子 

1、数据

create table test.test as
select 'A' name,'高级' level,'爬山,跳舞,看书' favs,'2021,2022' y
union all
select 'B' name,'中级' level,'看电视,跳舞' favs,'2021' y
union all
select 'C' name,'初级' level,'游泳' favs,'2020,2021' y
;

2、炸开一层

select
name,level,fav,y
from
test.test
lateral view explode(split(favs,",")) res as fav

 

3、炸开两层(炸开的两列做了笛卡尔积)

select
name,level,fav,year
from
test.test
lateral view explode(split(favs,",")) res as fav
lateral view explode(split(Y,",")) res2 as year
;