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

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

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

1、explode函数-行转列

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

1.1 explode函数语法

返回类型	函数名	描述
T	explode(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类似，不同的是还返回各元素在数组中的位置。

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

具体的posexplode函数使用方法：

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

SQL说明：

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

pos	val
0	A
1	B
2	C

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的执行结果如下所示：

col	explode_col
A	null
B	null

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

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

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

Array col1	Array 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

mycol1	col2
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