黑洞

这里藏着一些独特的想法

0%

Hive之行列转换

explode 函数

简介

explode函数属于UDTF函数(表生成),输入一行,输出多行。从英文直译过来是“爆炸”的意思,因此俗称爆炸函数。

语法和效果

explode函数接收map或者array类型的数据作为参数。在生成的表中,每个元素将单独成为一行数据。

  • explode(array)array列表里的每个元素生成一行;
  • explode(map)map里的每一对元素作为一行,其中key为一列,value为一列;

栗子:

1
select explode(`array`(11,22,33)) as item;

得到结果

item
11
22
33

或者

1
select explode(`map`("id",10086,"name","zhangsan","age",18));

得到结果

key value
id 10086
name zhangsan
age 18

explode函数可以单独使用,但如果需要查看原表的其他字段,请结合lateral view侧视
图使用,否则将报错。

lateral view 侧视图

简介

lateral view是一种特殊的语法,主要用于搭配UDTF类型的函数一起使用,用于解决UDTF函数的一些查询限制问题。

侧视图的原理是将UDTF的结果构建成一个类似于视图的表,然后将原表中的每一行和
UDTF函数输出的每一行进行连接,生成一张新的虚拟表。

使用lateral view时也可以对UDTF生成的列字段设置别名,生成的字段可以用于group byorder bylimit等语句中,不需要再单独嵌套一层子查询。

一般只要使用UDTF,就会固定搭配lateral view使用。

语法和效果

1
2
select ... from tabelA a lateral view UDTF(xxx) b as
col1, col2, col3 ...;

栗子:

1
2
3
4
select a.team_name, b.year
from the_nba_championship a
lateral view explode(champion_year) b as year
order by b.year desc;

行列转换

多行转单列

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

需求:将上表转换为如下所示。

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

实现方法:先进行分组,再用collect_list函数收集每组的多个值,由于col3是数值型,还需转换为字符串类型,最终使用concat_ws拼接。

1
2
3
4
5
6
7
8
select
col1,
col2,
concat_ws(',', collect_list(cast(col3 as string))) as col3
from
table_A
group by
col1, col2;

相关函数

  • concat:用于实现字符串拼接,不可指定分隔符;如果任意一个元素为NULL,结果就为NULL
  • concat_ws:用于实现字符串拼接,可以指定分隔符;全部元素都为NULL,结果才会为NULL
  • collect_list:用于将一列中的多行合并为一行,不进行去重。
  • collect_set:用于将一列中的多行合并为一行,进行去重。

单列转多行

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

需求:将上表转换为如下所示。

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

实现方法:由于col3并非集合,因此需要先使用split进行拆分得到一个集合,再使用爆炸函数和侧视图将结果与原表关联。

1
2
3
4
5
6
select
a.col1,
a.col2,
b.col3
from table_A a
lateral view explode(split(col3, ',')) b as col3
如果觉得文章写得不错或对您有帮助,请我喝杯柠檬茶吧!