需求:
《疑犯》 悬疑,动作,科幻,爱情
《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