Vol08-Hive中的行列互转-与UDTF的应用

UDTF(User-DefinedTable-Generating Functions) 用来解决输入一行输出多行(On-to-manymaping) 的需求。

hive 官方提供的所有UDTF请参照官方文档，

地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-BuiltinTableGeneratingFunctions%28UDTF%29

本文主要以我们日常用到较多的explode 为例说明。

UDTF有两种使用方法，一种直接放到select后面（不常用，基本不用），一种和lateral view一起使用（常用）。

1. 1.      直接select中使用

SELECT explode(myCol) AS myNewColFROM myTable;

说明：

其中myCol 为数组类型，myTable 表数据如下：

[100,200]

[300,400]

经过udtf处理，查询结果为：

100

200

300

400

单独使用此函数有一定的局限性，不太适用我们日常需求的场景，如下：

1. a)      不可以与其他字段一起使用：如 SELECT ,type,explode(myCol) ASmyNewCol FROM myTable ; 这样是不支持的。

2. b)      不可以嵌套调用：如 select explode(explode(myCol)) ASmyNewCol from src ; 这样是不支持的。

3. c)      不可以和group by/cluster by/distribute by/sort by一起使用：如 select explode(myCol) AS myNewCol from src group by myNewCol 这样是不支持的。

4. 2.      和lateral view一起使用

select col1,col2,newColfrom myTable LATERAL VIEW explode(myCol) adTable AS newCol

说明：

此方法更为常用，满足我们日常需求。

执行过程相当于单独执行了两次抽取，然后union到一个表里，但JOB数只有一个。

同样myCol 也需要为数组类型，但日常中我们多数情况下是string 类型经过split 函数拆分后获取数组类型 。

myTable 表数据如下：

id user name all_citys

'id1', 'user1', 'name1', '北京,天津'

'id2', 'user2', 'name2', '上海,河北'

执和语句：select id,user,name,city from myTable LATERAL VIEW explode(split(all_citys,',')) adTableAS city

经过udtf处理，查询结果为：

id user name city

'id1', 'user1', 'name1', '北京'

'id1', 'user1', 'name1', '天津'

'id2', 'user2', 'name2', '上海'

'id2', 'user2', 'name2', '河北'

避免了使用 union all 多个JOB进行多次读取占用较多资源的情况。

注意:新生的新列别名一定要与select 后读取的列名保持一致。

explode 生成的新列一定要放在所有列之后。

实际应用中可灵活使用，包括与其它函数嵌套使用，以及自定义数组数据格式。

另：自 Hive 0.13.0 版本支持 posexplode 函数，与 explode 的区别是新增支持位置序列可自动生成为单独一列

myTable 表数据如下：

[100,200]

[300,400]

经过udtf处理，查询结果为：

1,100

2,200

1,300

2,400