explode与lateral view使用详解（spark及hive环境对比）
HIVE环境1.explode 炸裂函数
界说：explode函数可以大概将array及map范例的数据炸开，实现一行变多行
格式：select explode(array/map) from table
示例
原始数据tmp表
name
id
goods
a
1
book_a,food_a
b
2
book_b,food_b
c
3
null
with tmp as (
select 'a' name,'1' as id,'book_a,food_a' as goods
union all
select 'b' name,'2' as id,'book_b,food_b' as goods
union all
select 'c' name,'3' as id,null as goods
)
select explode(split(goods,','))as goods_col
from tmp输出结果
goods_col
book_a
food_a
book_b
food_b
注意
1.如果炸开字段为null值，则会被直接过滤掉
2.hive环境下使用explode函数只能select explode炸开的列，且仅能包含单个explode，其他列无法取到。如
select explode(split(goods,','))as goods_col
,name
,id
from tmp
则会报错
2.lateral view侧视图
界说：Lateral 也是用来对数组举行列转行的，Lateral View重要办理在select使用UDTF（如explode）做查询的过程中查询只能包罗单个UDTF，不能包罗别的字段以及多个UDTF的环境（及不能添加额外的select列的标题）
格式：tabelA lateral view UDTF(xxx) 视图别名（假造表名） as a,b,c
示例：
with tmp as (
select 'a' name,'1' as id,'book_a,food_a' as goods
union all
select 'b' name,'2' as id,'book_b,food_b' as goods
union all
select 'c' name,'3' as id,null as goods
)
select name,id,goods_col
from tmp
lateral view explode(split(goods,',')) tmp_table as goods_col输出结果
name
id
goods_col
a
1
book_a
a
1
food_a
b
2
book_a
b
2
food_b
注意
1.如果炸开字段为null值，则会被直接过滤掉
特殊的，如果想要生存炸开字段为空值的行，则可以使用lateral view 来实现，对上述查询逻辑举行改动
with tmp as (
select 'a' name,'1' as id,'book_a,food_a' as goods
union all
select 'b' name,'2' as id,'book_b,food_b' as goods
union all
select 'c' name,'3' as id,null as goods
)
select name,id,goods_col
from tmp
lateral view outer explode(split(goods,',')) tmp_table as goods_col
输出结果
name
id
goods_col
a
1
book_a
a
1
food_a
b
2
book_a
b
2
food_b
c
3
null
spark
1.explode 炸裂函数
spark中的炸裂函数explode具备hive中的explode的功能，差异的是，spark中使用explode函数在select时可以大概筛选额外的列
with tmp as (
select 'a' name,'1' as id,'book_a,food_a' as goods
union all
select 'b' name,'2' as id,'book_b,food_b' as goods
union all
select 'c' name,'3' as id,null as goods
)
select name,id,explode(split(goods,','))as goods_col
from tmp
输出结果
name
id
goods_col
a
1
book_a
a
1
food_a
b
2
book_a
b
2
food_b
因此在spark中explode 近似可明白为等同于 lateral view explode，同时，spark中也支持explode_outer 等同于 lateral view outer explode
2.lateral view侧视图
spark中使用lateral view侧视图与hive中完全同等
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！qidao123.com:ToB企服之家，中国第一个企服评测及软件市场,开放入驻,技术点评得现金
页:
[1]