[Hive]表生成函数(UDTF)使用指南

CSDN博客 · · 234 次点击 · · 开始浏览    

UDTF是User-Defined Table-Generating Functions 的缩写,即用户定义的表生成函数。UDTF 用于从原始表中的一行生成多行数据。典型的 UDTF有EXPLODE、posexplode等函数,它能将array或者map展开。

表生成函数和聚合函数是相反的,表生成函数可以把单列扩展到多列。表生成函数:可以理解为一个函数可以生成一个表。

1、explode函数-行转列

explode函数以array类型数据输入,然后对数组中的数据进行迭代,返回多行结果,一行一个数组元素值。
ARRAY函数是将一列输入转换成一个数组输出。

1.1 explode函数语法

返回类型函数名描述
Texplode(ARRAY a)可以返回0到多行的结果,每行对应的是array数组中的一个元素。

现在使用explode函数:

hive (default)> SELECT explode(array(1,2,3)) AS num;
OK
num
1
2
3
Time taken: 0.327 seconds, Fetched: 3 row(s)

SQL说明:

  1. array 函数是将一列输入转换成一个数组输出;
  2. num 是给转换成的列命名一个新的名字,用于代表转换之后的列名。

1.2 explode(用于map类型数据)

由于map是key-value结构的,所以它在转换的时候会转换成两列,一列是key转换而成的,一列是value转换而成的。

select explode(mapcol) as (key,value) from tablename;

SQL说明:

  1. explode():函数中的参数传入的是map数据类型的列名。
  2. 由于map是kay-value结构的,所以它在转换的时候会转换成两列,一列是key转换而成的,一列是value转换而成的。
  3. key:表示key转换成的列名称,用于代表key转换之后的列名。
  4. value:表示value转换成的列名称,用于代表value转换之后的列名称。

注意:这两个值需要在as之后用括号括起来然后以逗号分隔。

2、posexplode函数

posexplode与explode类似,不同的是还返回各元素在数组中的位置。

返回类型函数名描述
Tposexplode (array a)可以返回0到多行的结果,每行对应的是array数组中的一个元素。

具体的posexplode函数使用方法:


select posexplode(array('A','B','C')) as (pos,val);

SQL说明:

  1. posexplode():函数中的参数传入的是array数据类型的列名。
  2. pos:表示数组中的位置转换成的列名。
  3. val:表示元素转换后对应的列名。
    执行结果如下所示:
posval
0A
1B
2C

3、表生成函数的局限性

UDTF 有一个限制,它出现在 SELECT 子句中的时候,不能与其它列共同出现。例如,下面这段SQL是会报错的。

SELECT
  pageid, EXPLODE(adid_list) AS adid
FROM
  pageAds;

此外,使用表生成函数的局限性还表现如下方面:
1)不能关联原有的表中的其他字段。
2)不能与group by、cluster by、distribute by、sort by联用。
3)不能进行UDTF嵌套。
4)不允许选择其他表达式。

为了解决这个UDTF问题,我们就需要引入 LATERAL VIEW

4、Lateral View

Lateral View一般与用户自定义表生成函数(如explode())结合使用。 如表生成函数中所述,UDTF为每个输入行生成零个或多个输出行。 Lateral View 首先将UDTF应用于基表的每一行,然后将结果输出行连接到输入行,以形成具有提供的表别名的虚拟表。

lateral view是Hive中提供给UDTF的结合,它可以解决UDTF不能添加额外的select列的问题。LATERAL VIEW将explode生成的结果当做一个视图来处理。

Lateral View其实就是用来和像类似explode这种UDTF函数联用的,Lateral View会将UDTF生成的结果放到一个虚拟表中,然后这个虚拟表会和输入行进行inner join来达到连接UDTF外的select字段的目的。而如果要保留输出为零的行,则需使用 LATERAL VIEW OUTER 执行 OUTER JOIN。

4.1 Lateral View语法格式

select 
	col1 [,col2,col3……] 
from 表名 
lateral view udtf(expression) 虚拟表别名 as col1 [,col2,col3……] 

SQL语句说明:

  1. 在from子句中使用,一般和格式一搭配使用,这个格式只是说明了lateral view的使用位置。
  2. from子句后面也可以跟多个lateral view语句,使用空格间隔就可以了。

lateral view跟在from后面,其后面是要使用的UDTF,为生成的虚拟表起一个表别名,不写会报错。然后跟as 列别名,有些UDTF会产生多个列,所以有时要跟多个列别名,不过列的别名可以省略,在实际使用中最好指定别名。

4.2 outer关键字的作用

用户可以指定outer关键字来生成行,即使LATERAL VIEW通常不会生成行。当所使用的UDTF不产生任何行时(使用explode()函数时,explode的列为空时,很容易发生上述这种情况)。 在这种情况下,源数据行不会出现在结果中。

select 
	col1 [,col2,col3……] 
from
(
	select explode(array('A', 'B')) as col
) 
lateral view explode(array()) tf as explode_col

SQL执行的结果是空,如果想让源数据行继续出现在结果中,可以使用 OUTER可以用来阻止关键字,并且来自UDTF的列使用NULL值代替。

select 
	col1 [,col2,col3……] 
from
(
	select explode(array('A', 'B')) as col
) 
lateral view outer explode(array()) tf as explode_col

添加outer关键字之后,SQL的执行结果如下所示:

colexplode_col
Anull
Bnull

这个outer的作用是在UDTF转换列的时候将其中的空也给展示出来,UDTF默认是忽略输出空的,加上outer之后,会将空也输出,显示为NULL。这个功能是在Hive0.12是开始支持的。

4.3 多个Lateral View outer 语句联合使用

FROM子句可以有多个LATERAL VIEW子句。 后面的LATERAL VIEWS子句可以引用出现在LATERAL VIEWS左侧表的任何列。

Array col1Array col2
[1, 2][a", “b”, “c”]
[3, 4][d", “e”, “f”]

例如,如下查询:

SELECT * FROM exampleTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(col2) myTable2 AS myCol2;

LATERAL VIEW子句按照它们出现的顺序应用。 下面我们对上述SQL语句进行拆解:

(1)应用第一个LATERAL VIEW子句的结果

SELECT * FROM exampleTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
mycol1col2
1[a", “b”, “c”]
1[a", “b”, “c”]
3[d", “e”, “f”]
4[d", “e”, “f”]

(2)应用第二个LATERAL VIEW子句的结果

SELECT * FROM exampleTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(col2) myTable2 AS myCol2;

SQL的执行结果:

在这里插入图片描述

4.4 Lateral View总结

使用lateral view explode的结合使用,关键点在于构造一个array数组。传入explode函数的可以是自定义的数组,也可以是from表中的数组字段。

LATERAL VIEW可以让我们像使用普通的表一样使用表生成函数,也就是说我们可以基于表生成函数的结果使用select、joins以及更多的操作。
例如,

select tf1.*, tf2.*
from (select 0) t
lateral view explode(map('A',10,'B',20,'C',30)) tf1
lateral view explode(map('A',10,'B',20,'C',30)) tf2 
where tf1.key = tf2.key;

SQL的执行结果如下所示:
在这里插入图片描述
或者对返回的结果集数据条数进行限制:

SELECT * FROM src LATERAL VIEW OUTER explode(array()) C AS a limit 10;

以及其他的一些表操作,在Lateral View中都可以使用。

参考链接:
Built-in Table-Generating Functions (UDTF)
LanguageManual LateralView
Hive Explode / Lateral View clarification

本文来自:CSDN博客

感谢作者:CSDN博客

查看原文:[Hive]表生成函数(UDTF)使用指南

234 次点击  
加入收藏 微博
暂无回复
添加一条新回复 (您需要 登录 后才能回复 没有账号 ?)
  • 请尽量让自己的回复能够对别人有帮助
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
  • 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
  • 图片支持拖拽、截图粘贴等方式上传