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

好文链接

评论可见，请评论后查看内容，谢谢！！！
 您阅读本篇文章共花了：