HIVE的高频面试UDTF函数

---

1. UDTF是什么

表生成函数

• 特点: 一进多出的函数
• 比如: explode(爆炸函数)
• 此类函数以后学习一个记住一个即可

2. 典型函数explode

explode函数接收map或者array类型的数据作为参数，然后把参数中的每个元素炸开变成一行数据。一个元素一行。这样的效果正好满足于输入一行输出多行。

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

3. 案例实操

3.1 业务需求

我们已有数据如下

id	name
10	CLARK|KING|MILLER
20	SMITH|JONES|SCOTT|ADAMS|FORD
30	ALLEN|WARD|MARTIN|BLAKE|TURNER|JAMES

我们想要的结果

我们该如何实施？

3.2 业务开发

第一步: 在node1的/root/hivedata/下, 创建一个dept.txt文件, 添加以下数据:

10      CLARK|KING|MILLER
20      SMITH|JONES|SCOTT|ADAMS|FORD
30      ALLEN|WARD|MARTIN|BLAKE|TURNER|JAMES

第二步: 在hive中创建表

create database day04_hive;
use day04_hive;
create table day04_hive.dept(dept_id int,dept_name array<string>
)row format 
delimited fields terminated by '\t'
collection items terminated by '|';

第三步: 导入数据到 dept表中

load data local inpath '/root/hivedata/dept.txt' into table day04_hive.dept;

第四步: 测试是否加载成功

select * from day04_hive.dept;

显示结果如下

+---------------+----------------------------------------------------+
| dept.dept_id  |                   dept.dept_name                   |
+---------------+----------------------------------------------------+
| 10            | ["CLARK","KING","MILLER"]                          |
| 20            | ["SMITH","JONES","SCOTT","ADAMS","FORD"]           |
| 30            | ["ALLEN","WARD","MARTIN","BLAKE","TURNER","JAMES"] |
+---------------+----------------------------------------------------+

尝试使用explode:

select  explode(dept_name) from day04_hive.dept;

结果为:

+---------+
|   col   |
+---------+
| CLARK   |
| KING    |
| MILLER  |
| SMITH   |
| JONES   |
| SCOTT   |
| ADAMS   |
| FORD    |
| ALLEN   |
| WARD    |
| MARTIN  |
| BLAKE   |
| TURNER  |
| JAMES   |
+---------+

接着尝试, 将 部门id加上:

select  dept_id,explode(dept_name) as dept_name from day04_hive.dept;

发现, 报错了:

Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)

UDTF函数特殊要求:

1. 如果UDTF函数被使用在select后面, 不允许在出现其他的列或者字段
2. UDTF函数不允许被其他的函数所嵌套, 但是他可以嵌套其他的函数

如何解决呢? 可以将这个结果作为临时表 ,然后和原有表进行关联即可
但是发现, 好像无法关联, 因为临时表和原有表没有关联条件, 此时如何办呢?
答: hive为了解决这种问题, 可以采用侧视图的方案, 而侧视图一般就是和UDTF配合使用, 解决UDTF函数特殊问题
侧视图: LATERAL VIEW

用法：lateral view udtf(expression) tableAlias AS columnAlias
放置位置: 在SQL的最后面

接下来, 使用侧视图解决问题:

select  dept_id, name from day04_hive.dept lateral view  explode(dept_name) t1 as name;+----------+---------+
| dept_id  |  name   |
+----------+---------+
| 10       | CLARK   |
| 10       | KING    |
| 10       | MILLER  |
| 20       | SMITH   |
| 20       | JONES   |
| 20       | SCOTT   |
| 20       | ADAMS   |
| 20       | FORD    |
| 30       | ALLEN   |
| 30       | WARD    |
| 30       | MARTIN  |
| 30       | BLAKE   |
| 30       | TURNER  |
| 30       | JAMES   |
+----------+---------+

思考: 如果刚刚建表的时候, 不使用array类型, 使用string类型, 如何解决呢?

select  dept_id, name from day04_hive.dept lateral view  explode(split(dept_name,'|')) t1 as name;

同样的我们重复前面建表的操作再建立一个表，建表语句如下

create table day04_hive.dept1(dept_id int,dept_name string
)row format delimited
fields terminated by '\t';

加载数据

load data local inpath '/root/hivedata/dept.txt' into table day04_hive.dept1;

使用explode将其炸开

select dept_id,t2.name from day04_hive.dept1 lateral view explode(split(dept_name,'|')) t2 as name;

结果如下，这是怎么回事？

这显然不是我们想要的，这是因为
split(dept_name,‘|’) 中的 | 在正则表达式中表示"或"操作，所以它会按照每个字符进行分割，导致：

“CLARK” 被分割成 [“C”, “L”, “A”, “R”, “K”]
“KING” 被分割成 [“K”, “I”, “N”, “G”]
“MILLER” 被分割成 [“M”, “I”, “L”, “L”, “E”, “R”]
然后 explode() 再对每个字母进行展开，就变成了一行一个字母。

这意味着我们需要转义符，使用双反斜杠转义（推荐）

select dept_id,t2.name from day04_hive.dept1 lateral view explode(split(dept_name,'\\|')) t2 as name;

出现了我们想要的结果，大功告成。

3.3 补充转义

解决方案
您需要对 | 进行转义，有以下几种方法：

方法1：使用双反斜杠转义（推荐）

SELECT explode(split(dept_name, '\\|')) FROM dept1;

方法2：使用方括号转义

SELECT explode(split(dept_name, '[|]')) FROM dept1;

方法3：使用字符类转义

SELECT explode(split(dept_name, '\\|')) FROM dept1;

如果有帮助到你，请点赞收藏