Hive列转行 (Lateral View + explode)详解

需求：
《疑犯》 悬疑,动作,科幻,爱情
《lies》 悬疑,警匪,动作,心理,剧情
《战狼》 战争,动作,灾难


转成如下格式：

《疑犯》 悬疑
《疑犯》 动作
《疑犯》 科幻
《疑犯》 爱情
《lies》 悬疑
《lies》 警匪
《lies》 动作
《lies》 心理
《lies》 剧情
《战狼》 战争
《战狼》 动作
《战狼》 灾难

思路解析：
explode函数：处理map结构的字段，将数组转换成多行

step1：建表movie_info：

--对电影的风格使用数组，所以建表时要标明数组的分隔符语句  ——  collection items terminated by ","

hive (felix)> create table split_explored_a (
            > id string,
            > item_array array<string>)
            > row format delimited
            > fields terminated by "\t"
            > collection items terminated by ",";

[hadoop@hadoop1 ~]$ cat  explored_test.txt
<疑犯>  悬疑,动作,科幻,爱情
<lies>  悬疑,警匪,动作,心理,剧情
<战狼>  战争,动作,灾难


-- 插入数据
hive (felix)> load data local inpath '/home/hadoop/explored_test.txt' overwrite into table split_explored_a;
Loading data to table felix.split_explored_a
OK
Time taken: 0.62 seconds

--查询表格：

hive (felix)> select * from split_explored_a ;
OK
<疑犯>  ["悬疑","动作","科幻","爱情"]
<lies>  ["悬疑","警匪","动作","心理","剧情"]
<战狼>  ["战争","动作","灾难"]
Time taken: 0.121 seconds, Fetched: 3 row(s)
hive (felix)> 

 

此时可以看到category是一个数组，并且分隔符为"，"；
step2：explode的使用：
explode作用：处理map结构的字段，将数组转换成多行
所以我们现在先对category使用category函数：

hive (felix)> select explode(item_array) from  split_explored_a;
OK
悬疑
动作
科幻
爱情
悬疑
警匪
动作
心理
剧情
战争
动作
灾难
Time taken: 0.355 seconds, Fetched: 12 row(s)
hive (felix)> 

如果想要得到题目的需求结果，那么需要在此结果上，每一部电影和该电影对应的category进行笛卡尔积，得到结果：

 

如果我们直接

hive (felix)> select id,explode(item_array) from  split_explored_a;
FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions
hive (felix)> 

查询直接报错，因为movie的结果只有三条，而explode(category)有 4 + 5 + 3 = 12条记录。

 

那么，我们由此引入LATERAL VIEW函数：

LATERAL VIEW：

• 1.Lateral View 用于和UDTF函数【explode,split】结合来使用。
• 2.首先通过UDTF函数将数据拆分成多行，再将多行结果组合成一个支持别名的虚拟表。
• 3..主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF，不能包含其它字段以及多个UDTF的情况。
• 4.语法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)

 

使用LATERAL VIEW + explode 函数进行查询，语句如下：

hive (felix)> select id,item_name from split_explored_a 
            > lateral view explode(item_array) tt  as item_name; --item_name 为别名列
OK
<疑犯>  悬疑
<疑犯>  动作
<疑犯>  科幻
<疑犯>  爱情
<lies>  悬疑
<lies>  警匪
<lies>  动作
<lies>  心理
<lies>  剧情
<战狼>  战争
<战狼>  动作
<战狼>  灾难
Time taken: 0.044 seconds, Fetched: 12 row(s)
hive (felix)> 

另一个经典例子【单列转多行】:

单列转多行
假设有数据表
col2row：
 
col1    col2    col3
a       b       1,2,3
c       d       4,5,6
 
现要将其转化为：
 
col1    col2    col3
a       b       1
a       b       2
a       b       3
c       d       4
c       d       5
c       d       6
 
这里需要使用UDTF（表生成函数）explode()，该函数接受array类型的参数，其作用恰好与collect_set相反，实现将array类型数据行转列。explode配合lateral view实现将某列数据拆分成多行。
 
HQL语句为：
 
select col1, col2, lv.col3 as col3
from col2row 
lateral view explode(split(col3, ',')) lv as col3;


col1    col2    col3
a       b       1
a       b       2
a       b       3
c       d       4
c       d       5
c       d       6