Hive split()、explode()和lateral view 应用单列，多列炸裂

2023-11-28 23:39| 来源: 网络整理| 查看: 265

导航基本概念示例数据准备函数应用split()explode（）函数总结

基本概念

在对于SQL的使用中，常常会遇到列转行，或者将一列的数据散列成多行进行统计分析处理的需求，这时候，结合split()、explode()和lateral view 处理这些需求会使得我们得心应手许多。先来了解一下这三个基本的介绍吧。

1.Split(str, separator)：将字符串按照后面的分隔符切割，转换成字符array。第一个参数是我们需要进行分割的字符串，第二个参数是我们的分割符，其结果时一个数组。和Java中String的split()方法表现一致。2.EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。col可以是Hive中集合数据类型中的Array或Map，不能是struct类型。有小可爱还不清楚Hive的集合数据类型？那就贴心的带小可爱简单回顾一下。

Hive集合数据类型

数据类型描述语法示例struct和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段名.first来引用。structmapMAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素map(‘first’,‘JOIN’,‘last’,‘Doe’)ARRAY数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。Array() 例如array(‘john’,‘Doe’)

Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

3.LATERAL VIEW 用法：LATERAL VIEW udtf(expression) 虚拟表别名 AS 列别名 lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。 lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。tips ：Hive的函数大致可以分为三类：UDF（用户自定义函数）、UDAF（用户自定义聚合函数）、UDTF（用户自定义表生成函数）。大致的特定如下： UDF：一进一出，即遗憾数据输入，一行数据输出，如：substring（）UDAF：多进一出，多行数据输入，只有一个结果输出，如：sum()、count()等聚合函数UDTF：一进多出，如现在explode(),可以将一行数据输出为多行结果示例

在大致了解了split()、explode()和lateral view的介绍我们一一开始去看看是如何使用的。

数据准备 # 新建一个文本 [qingfeng@hadoop102 data]$vim movie.txt # 数据如下，电影的分类以及语言《疑犯追踪》悬疑,动作,科幻,剧情 English,Chinese,Janpanese,Krease 《Lie to me》悬疑,警匪,动作,心理,剧情 English,Chinese,Janpanese,Gernemic 《战狼2》战争,动作,灾难 English,Chinese,Janpanese -- 创建movie表 hive > create table movie(mname string,category string,language string) row format delimited fields terminated by '\t'; -- 加载数据 hive > load data local inpath "/home/qingfeng/data/movie.txt" into table movie; -- 查看数据 hive > select * from movie; INFO : OK +----------------+---------------------+-------------------------------------+ | movie.mname | movie.category | movie.language | +----------------+---------------------+-------------------------------------+ | 《疑犯追踪》 | 悬疑,动作,科幻,剧情 | English,Chinese,Janpanese,Krease | | 《Lie to me》 | 悬疑,警匪,动作,心理,剧情 | English,Chinese,Janpanese,Gernemic | | 《战狼2》 | 战争,动作,灾难 | English,Chinese,Janpanese | +----------------+---------------------+-------------------------------------+ 3 rows selected (0.087 seconds) 函数应用 split()

使用split函数，其结果为array数组，将分割的元素放在数组中。

使用explode（）函数在split基础上对电影分类的这一列转换为多行单个

hive > select explode(split(category,',')) from movie; +------+ | col | +------+ | 悬疑 | | 动作 | | 科幻 | | 剧情 | | 悬疑 | | 警匪 | | 动作 | | 心理 | | 剧情 | | 战争 | | 动作 | | 灾难 | +------+ 12 rows selected (0.075 seconds)

当我们需要将电影名称和对应的分类对应显示，我们应该怎么做？结果形如：

《疑犯追踪》悬疑《疑犯追踪》动作《疑犯追踪》科幻《疑犯追踪》剧情《Lie to me》悬疑《Lie to me》警匪《Lie to me》动作《Lie to me》心理《Lie to me》剧情《战狼2》战争《战狼2》动作《战狼2》灾难

我们在explode查询基础上添加mname字段，是否可以呢？

hive> select mname,explode(split(category,',')) cate from movie; Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)

报错了，提示我们不支持在select外部使用UDTF函数，这个时候lateral view就可以配合一起使用了，lateral view我们也称之为侧写。

-- 结合lateral view 查看 select mname,cate from movie lateral view explode(split(category,',')) tmp as cate; +--------------+-------+ | mname | cate | +--------------+-------+ | 《疑犯追踪》 | 悬疑 | | 《疑犯追踪》 | 动作 | | 《疑犯追踪》 | 科幻 | | 《疑犯追踪》 | 剧情 | | 《Lie to me》 | 悬疑 | | 《Lie to me》 | 警匪 | | 《Lie to me》 | 动作 | | 《Lie to me》 | 心理 | | 《Lie to me》 | 剧情 | | 《战狼2》 | 战争 | | 《战狼2》 | 动作 | | 《战狼2》 | 灾难 | +--------------+-------+ 12 rows selected (0.047 seconds)

当我们需要对于category 列和language列一起散列，只需要对每一个需要操作的列进行lateral view即可。需求是想要如下的显示格式：

则其对应的查询sql如下：

从结果可以看出，多个列进行lateral view就是各自的explode做了全连接。

总结

explode函数一般是与lateral view结合使用，因为我们在实际业务中不会单独查看一个列散列之后的情况。同时也从多列explode可以看出，多列的结果是各个列的全连接。

【本文地址】

公司简介

联系我们