Hive SQL中的 lateral view 与 explode（列转行）以及行转列

您所在的位置：网站首页 › HIVE的本质是 › Hive SQL中的 lateral view 与 explode（列转行）以及行转列

Hive SQL中的 lateral view 与 explode（列转行）以及行转列

2024-07-04 08:01| 来源: 网络整理| 查看: 265

文章目录一、列转行Explode炸裂函数posexplode()函数 Lateral View例子1例子2例子3例子4——多列炸裂 Posexplode例子5例子6 二、行转列例子1例子2

一、列转行

行转列：将多个列中的数据在一列中输出列转行：将某列一行中的数据拆分成多行

Explode炸裂函数

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

语法

explode(col) select explode(arraycol) as newcol from tablename;

explode()：函数中的参数传入的是arrary数据类型的列名。

newcol：是给转换成的列命名一个新的名字，用于代表转换之后的列名。

tablename：原表名

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

// 输入是array时，array中的每个元素都单独输出为一行 select(array('1','2','3'))

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

select explode(map('A','1','B','2','C','3'))

在这里插入图片描述

通常，explode函数会与lateral view一起结合使用

posexplode()函数

对一列进行炸裂可以使用 explode()函数，但是如果想实现对两列都进行多行转换，那么用explode()函数就不能实现了，可以用posexplode()函数，因为该函数可以将index和数据都取出来，使用两次posexplode并令两次取到的index相等就行了。

详见例子4和例子5

Lateral View

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

语法

lateral view udtf(expression) tableAlias as columnAlias (,columnAlias)*

lateral view在UDTF前使用，表示连接UDTF所分裂的字段。

UDTF(expression)：使用的UDTF函数，例如explode()。

tableAlias：表示UDTF函数转换的虚拟表的名称。

columnAlias：表示虚拟表的虚拟字段名称，如果分裂之后有一个列，则写一个即可；如果分裂之后有多个列，按照列的顺序在括号中声明所有虚拟列名，以逗号隔开。

Lateral View主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF，不能包含其它字段以及多个UDTF的情况（不能添加额外的select列的问题）。

Lateral View其实就是用来和想类似explode这种UDTF函数联用的，lateral view会将UDTF生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行进行join来达到连接UDTF外的select字段的目的。

引用：hive中的lateral view 与 explode函数的使用在这里插入图片描述

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

例子1

假设有如下movies表，字段名分别为movie(string)和category(array) 在这里插入图片描述转换为

在这里插入图片描述

select movie,category_name from movies lateral view explode(category) table_tmp as category_name; -- 结果: --《疑犯追踪》悬疑 --《疑犯追踪》动作 --《疑犯追踪》科幻 --《疑犯追踪》剧情 --《海豹突击队》动作 -- ...

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

例子2 a b 1,2,3 c d 4,5,6

转换为

a b 1 a b 2 a b 3 c d 4 c d 5 c d 6 select col1, col2, col5 from test lateral view explode(split(col3,',')) b AS col5 // split(col3,",")相对字符串切割，得到数组

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

例子3

应用文章的一个思考题掌握这个SQL技巧超越80%的人——行转列/列转行

在这里插入图片描述

select uid_split, game from ( select uid,game from user_game lateral view explode(split(game_list,",")) tmpTable as game ) a lateral view explode(split(uid, ",")) m as uid_split 例子4——多列炸裂 Posexplode

前面提到：

引用例子来自：Hive中的explode使用全解

在这里插入图片描述单列Posexplode

select class,student_index + 1 as student_index,student_name from default.classinfo lateral view posexplode(split(student,',')) t as student_index,student_name;

在这里插入图片描述

双列炸裂，转换成如下的形式

在这里插入图片描述

注：不能这样用！

select class,student_name,student_score from default.classinfo lateral view explode(split(student,',')) sn as student_name lateral view explode(split(score,',')) sc as student_score

这样写的结果如下，不符合需求

在这里插入图片描述

可以进行两次posexplode，姓名和成绩都保留对应的序号，即使变成了9行，也通过where条件只保留序号相同的行即可。 select class,student_name,student_score from default.classinfo lateral view posexplode(split(student,',')) sn as student_index_sn,student_name lateral view posexplode(split(score,',')) sc as student_index_sc,student_score where student_index_sn = student_index_sc; 例子5

引用例子来自：https://blog.csdn.net/dzysunshine/article/details/101110467

在这里插入图片描述

转换成如下形式

single_id single_tim a 2:00 b 3:00 c 4:00 d 5:00 f 1:10 b 2:20 c 3:30 d 4:40

一次posexplode

select id,tim,single_id_index,single_id from test.a lateral view posexplode(split(id,',')) t as single_id_index, single_id;d;

结果

single_id_index single_id 0 a 1 b 2 c 3 d 0 f 1 b 2 c 3 d

两次posexplode+where筛选

select id,tim,single_id,single_tim from test.a lateral view posexplode(split(id,',')) t as single_id_index, single_id lateral view posexplode(split(tim,',')) t as single_yim_index, single_tim where single_id_index = single_yim_index; 例子6

在这里插入图片描述

select column1, column2, column3, m_key, m_val from (select column1, column2, column3, map("X1", X1, "X2", X2, "X3", X3, "X4", X4) as map1 from table1) as t1 lateral view explode(map1) xyz as m_key, m_val 二、行转列

行转列：将多个列中的数据在一列中输出列转行：将某列一行中的数据拆分成多行

Concat

concat(string1/col, string2/col, …)

输入任意个字符串(或字段,可以为int类型等)，返回拼接后的结果

select concat(id,'-',name,'-',age) from student;

Concat_ws

concat_ws(separator, str1, str2, …)

特殊形式的 concat()，参数只能为字符串，第一个参数为后面参数的分隔符

分隔符可以是与后面参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

select concat_ws('-', name, gender) from student;

Collect_set（聚合，返回数组类型）

collect_set(col)

将某字段进行去重处理，返回array类型；该函数只接受基本数据类型

select collect_set(age) from student;

collect_set 与 collect_list 的区别就是set去重，list不去重

例子1

把星座和血型一样的人归类到一起在这里插入图片描述结果

射手座,A 大海|凤姐白羊座,A 孙悟空|猪八戒白羊座,B 宋宋 select t1.base, concat_ws('|', collect_set(t1.name)) as name from (select name,concat(constellation, ",", blood_type) as base from person_info) as t1 group by t1.base; 例子2

在这里插入图片描述结果：

张三语文,数学,英语 98,95,89 李四语文,数学,英语 97,88,90 select stu_name, concat_ws(',',collect_set(course)) as course, concat_ws(',',collect_set(score)) as score from student group by stu_name

另外，行转列常用函数还有

case when then …else end if(expr, true_result, false_result)

case when 语句是SQL中的一个非常重要的功能，可以完成很多复杂的计算，相当于一个表达式，可以放在任何可放表达式的地方。

语法 case when 条件 then 结果 when 条件 then 结果 else end。else可不加，是缺省条件下的值，如果不加，有缺省情况则为NULL。CASE WHEN还可以和GROUP BY 语句搭配使用，用在sum,count,max等聚合函数内部。

在这里插入图片描述

详情参见文章掌握这个SQL技巧超越80%的人——行转列/列转行

参考： Hive SQL中的lateral view explode https://zhuanlan.zhihu.com/p/142666980

【本文地址】

公司简介

联系我们