列转行函数介绍
EXPLODE(col)
EXPLODE(col)
大白话是,如果你传递的参数是Array, 会给这个Array分隔转成多行 ,如果你传递的是Map,就会给Map里面的每个元素分隔成多行和多列. 参数只能是Array或者是Map
explode函数属于udtf,udtf在使用时候,不能和其它表达式一起出现在select子句后,言外之意只能单独出现在select子句中.
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
需求
将电影分类中的数组数据展开。结果如下：
比如说 疑犯追踪 属于 悬疑的,又属于动作的 又属于科幻的 又属于剧情 的
《疑犯》 悬疑,动作,科幻,爱情
《lie to me》 悬疑,警匪,动作,心理,剧情
《战狼》 战争,动作,灾难
转成下面的格式
《疑犯》 悬疑
《疑犯》 动作
《疑犯》 科幻
《疑犯》 爱情
《lie to me》 悬疑
《lie to me》 警匪
《lie to me》 动作
《lie to me》 心理
《lie to me》 剧情
《战狼》 战争
《战狼》 动作
《战狼》 灾难
准备数据
movie_info.txt
千万注意 电影名和类目之前要用 tab 分隔,不要弄错了,否则就出现问题.
《疑犯》 悬疑,动作,科幻,爱情
《lie to me》 悬疑,警匪,动作,心理,剧情
《战狼》 战争,动作,灾难查看是否用指定分隔符
有^I 说明是tab分割的
[root@zjj101 soft]# cat -T movie_info.txt
《疑犯》^I悬疑,动作,科幻,爱情
《lie to me》^I悬疑,警匪,动作,心理,剧情
《战狼》^I战争,动作,灾难
创建表
sql:
create table movie_info
(movie string,category array<string>
)row format delimitedfields terminated by "\t"collection items terminated by ",";
指定分隔符用 tab分隔的,也就是\t ,
指定集合类目用逗号进行分隔的
导入数据
sql
load data local inpath "/root/soft/movie_info.txt" into table movie_info;
思路
将类目进行行转列
sql:
select explode(category)
from movie_info;
现在想办法让每个类目和电影名字做关联,
sql:
select movie, explode(category)
from movie_info;
会报错.因为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 函数进行查询
sql:
select movie, category_name
from movie_infoLATERAL VIEW explode(category) tmpTable as category_name;
-- category_name 是给 explode(category) 列起的别名