hive—炸裂函数explode/posexplode

1、Explode炸裂函数

1）explode(array)使得结果中将array列表里的每个元素生成一行

2）explode(map)使得结果中将map里的每一对元素作为一行，key为一列，value为一列

局限性：

2、posexplode()函数

1）实例1：无分隔符字符串炸裂

3、Lateral View

1）案例1：explode+split

2）案例2：explode+split

3）案例3：explode+split

4）多列炸裂 posexplode+split

5）案例5：多列一对一关系 posexplode+split

6）案例6：explode+map

1、Explode炸裂函数

将hive某列一行中复杂的 array 或 map 结构拆分成多行（只能输入array或map）

语法：

select explode(字段) as 字段命名 from 表名;

举例：

1）explode(array)使得结果中将array列表里的每个元素生成一行

select explode(array('1','2','3')) DD

2）explode(map)使得结果中将map里的每一对元素作为一行，key为一列，value为一列

select explode(map('A','1','B','2','C','3'));
select explode(map('Chinese','100','Math','88','English','38'));

局限性：

1、不能关联原有的表中的其他字段

2、不能与group by、cluster by、distribute by、sort by联用

3、不能进行UDTF嵌套

2、posexplode()函数

explode()：对一列进行炸裂可以使用

posexplode()：对两列进行多行转换，可以将index和数据都取出来，使用两次posexplode并令两次取到的index相等即可

举例：

select posexplode(collect_set('AA'))

collect_set：将某字段进行去重处理，返回array类型

1）实例1：无分隔符字符串炸裂


--数据准备
with tmp_cust_no as(
select '6738314' cust_no,'#$}' cd_str
union all
select '4198898' cust_no,'#$(}' cd_str
),tmp_cd_desc as(
select '#' cd, '大宗交易' cd_desc
union all
select '$' cd, '北交所股票' cd_desc
union all
select '}' cd, '不定项' cd_desc
union all
select '(' cd, '委托应急' cd_desc
),--1、用 posexplode 函数炸裂cd_str字段成行
tmp_cd_str as(
select 
t.cust_no,
t.cd_str, --需要遍历的字符串
pos, --索引
substr(t.cd_str,pos+1,1) cd_str1 , --获取字符串中索引位置的值
substr(t.cd_str,0,pos+1) cd_str2 --从起始位置开始到当前位置的字符串
from 
(select A.*, posexplode(split(space(length(cd_str)-1),' ')) 
from tmp_cust_no A 
where length(trim(A.cd_str))>0) t
),--2、匹配码表数据
--tmp_cd_str_desc as()
select A.cust_no,
A.cd_str,
concat_ws(',',collect_set(B.cd_desc)) cd_str_desc
from tmp_cd_str A
inner join tmp_cd_desc B on A.cd_str1=B.cd
group by A.cust_no,
A.cd_str

步骤1：用 posexplode 函数炸裂cd_str字段成行的输出：

码表匹配输出结果：

3、Lateral View

Lateral View配合 split, explode 等UDTF函数一起使用，它能够将一列数据拆成多行数据，并且对拆分后结果进行聚合，即将多行结果组合成一个支持别名的虚拟表。相当于拆出一张虚拟表，与原表进行关联。

语法：

select o.*, table_view.new_col
from table_origin o
lateral view UDTF(expression) table_view as new_col_1, new_col_2

lateral view：表示将UDTF分裂的字段放在虚拟表中, 然后和主表table_origin进行关联。
UDTF(expression)：使用的UDTF函数，例如explode()
table_view : 对应的虚拟表的表名
new_col: 虚拟表里存放的有效字段（可多个）
注意：

lateral view的位置是from后where条件前
生成的虚拟表的表名不可省略
from后可带多个lateral view
如果要拆分的字段有null值，需要使用lateral view outer 替代，避免数据缺失;

1）案例1：explode+split

假设有如下movies表，字段名分别为movie(string)和category(array<string>)

with tmp_movies as(
select '《疑犯追踪》' movie, array('悬疑','动作','科幻','剧情') category
union all
select '《战狼2》' movie, array('战争','动作','灾难') category
)select  movie,category_name
from tmp_movies
lateral view explode(category) table_tmp as category_name;

2）案例2：explode+split

注：explode函数输入了一个string类型的参数，搭配split()函数

假设有如下movies表，字段名分别为movie(string)和category(array<string>)


with tmp_movies as(
select '《疑犯追踪》' movie, '悬疑,动作,科幻,剧情' category
union all
select '《战狼2》' movie, '战争,动作,灾难' category
)select  movie,category_name
from tmp_movies
lateral view explode(split(category,',')) table_tmp as category_name;
-- split(category,",")相对字符串切割，得到数组

3）案例3：explode+split


--数据准备
with tmp_user_game as(
select 'a,b,c' uid,'王者荣耀,刺激战场' game_list
union all 
select 'e,b,c' uid,'极品飞车,天天飞车,实况足球' game_list
)select uid_split, game 
from ( select uid,gamefrom tmp_user_gamelateral view explode(split(game_list,",")) tmpTable as game
) a
lateral view explode(split(uid, ",")) m as uid_split

4）多列炸裂 posexplode+split

注：对于多个数组的行转列可以使用posexplode函数，此函数有index索引

--数据准备
with tmp_student_score as(
select '1001' stu_id,'张三' stu_name,'语文,数学,英语,历史,地理' courses,'88,87,94,86,84' scores
union all 
select '1002' stu_id,'李四' stu_name,'语文,数学,英语,历史,地理' courses,'78,89,75,79,68' scores
union all 
select '1003' stu_id,'王五' stu_name,'语文,数学,英语,历史,地理' courses,'66,63,64,67,68' scores
union all 
select '1004' stu_id,'朱六' stu_name,'语文,数学,英语,历史,地理' courses,'98,97,91,93,92' scores
)select stu_id, stu_name, course, score 
from tmp_student_score 
lateral view posexplode(split(courses, ',')) table_view1 as posa, course 
lateral view posexplode(split(scores, ',')) table_view2 as posb, score 
where posa = posb;

5）案例5：多列一对一关系 posexplode+split

--数据准备
with tmp_test as(
select 'a,b,c,d' id,'2:00,3:00,4:00,5:00' tim
union all 
select 'f,b,c,d' id,'2:30,3:30,4:30,5:30' tim
)select id,tim,single_id,single_tim 
from tmp_testlateral view posexplode(split(id,',')) t as posa, single_idlateral view posexplode(split(tim,',')) t as posb, single_tim
where posa = posb;

6）案例6：explode+map

--数据准备
with tmp_test as(
select 'A1' column1,'A2' column2,'A3' column3, '5' X1,'6' X2,'1' X3, '4' X4
)select column1, column2, column3, m_key, m_val from(select column1, column2, column3, map("X1", X1, "X2", X2, "X3", X3, "X4", X4) as map1from tmp_test) as t1
lateral view explode(map1) table_view as m_key, m_val

7）案例7：explode+str_to_map


--数据准备
with tmp_student_score as(
select '小明' Name,'88' Math,'80' English
union all
select '小红' Name,'68' Math,'99' English
)select A.name,
tmp.item,
tmp.score
from tmp_student_score A
lateral view explode(
str_to_map(concat('Math=',Math,'&English=',English),'&','=')
) tmp as item,score;select str_to_map(concat('Math=',Math,'&English=',English),'&','=') dd
from tmp_student_score --输出：
--{"English":"80","Math":"88"}
--{"English":"99","Math":"68"}

解析：首先使用str_to_map函数将math字段与english字段拼接后的结果转换为map类型，然后通过侧视图和explode函数将其爆炸开，给生成的临时侧视图一个名字，取名tmp并给列名取名为item,score，因为explode(map)爆炸的结果是每一个item为行，key为1列，value为1列，这样就恰好形成我们想要的结果。