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中完全一致