[Hive系列09] 表生成函数

数据分析师的FIRE人生 2021-06-24

1719

点击上方「数据分析师的FIRE人生」→

点击右上角「...」→设为星标⭐

大家好，我是风影楼，一名互联网公司的数据分析师。之前我曾在CSDN编写了Hive系列的付费博客专栏，目前收获了25W次的访问，不过由于CSDN的付费设置无法取消，所以我决定在公众号重新免费分享一遍。这一篇主要介绍Hive中的表生成函数。在Hive中，所有的表生成函数，包括用户自定义的和内置的，都统称为用户自定义表生成函数（user defined table generating functions），简称udtf。本文只介绍Hive自带的内置表生成函数。

1、explode(array)

功能：返回n行，每行对应数组中的一个元素。

举例：

select explode(array(1,2,3,4));


运行结果如下：
col
1
2
3
4

2、explode(map)

功能：返回n行两列，每行对应每个map键-值，第一列是map的键，第二列是map的值。（不常用）

举例：

select explode(map(1,2,3,4));


运行结果如下：
key  value
1  2
3  4

3、posexplode(array)

功能：与explode类似，但除了数组元素本身（第二列），还返回各元素在数组中的位置（从0开始，第一列）。（不常用）

举例：

select posexplode(array(2,4,6,8));


运行结果如下：
pos  val
0  2
1  4
2  6
3  8

4、stack(int n, v_1, v_2, ..., v_k)

功能：把k列数据转换成n行，k/n列，其中n必须是正整数，后面的v_1到v_k必须是元素，不能是列名。（不常用）

举例：

n设为3，将后面6个元素按顺序分为3行2列
select stack(3,1,2,3,4,5,6);


运行结果如下：
col0  col1
1  2
3  4
5  6


n设为2，将后面6个元素按顺序分为2行3列
select stack(2,1,2,3,4,5,6);


运行结果如下：
col0  col1  col2
1  2  3
4  5  6


n设为6，将后面6个元素转为为6行1列
select stack(6,1,2,3,4,5,6);


运行结果如下：
col0
1
2
3
4
5
6

5、json_tuple(jsonstr, k1, k2, ...)

功能：从一个json字符串中获取多个key对应的value并作为一个元组返回。

举例：具体案例见[Hive系列03] 如何解析JSON格式数据

6、parse_url_tuple(url, p1, p2, ...)

功能：返回从url中抽取指定n部分的内容并作为一个元组返回，参数url是url字符串，而参数p1,p2,....是要抽取的部分。

举例：具体案例见[Hive系列04] URL解析函数

7、表生成函数必备：lateral view

udtf有一个很大的限制，在使用udtf时，select后面只能跟udtf，不能跟其他任何字段，否则会报错，如下：

select 1 as flag,explode(array(1,2,3,4));


failed: semanticexception [error 10081]: udtf's are not supported outside the select clause, nor nested in expressions

lateral view就是为了解决在select使用udtf做查询过程中，查询只能包含单个udtf，不能包含其他字段、以及多个udtf的问题。

1）语法:

select 
列别名1[ ,列别名2,列别名3……] 
from 表名 lateral view udtf(expression) 虚拟表别名 as 列别名1[ ,列别名2,列别名3……]

lateral view跟在from后面，然后跟要使用的udtf，为生成的虚拟表起一个表别名，不写会报错。然后跟as 列别名，有些udtf会产生多个列，所以有时要跟多个列别名。

2）应用1：行转列

举例：

原始数据如下表：
select * from temp_test6;


运行结果如下：
temp_test6.shop  temp_test6.uid_array
a  1,2,3
b  4,5,6

首先使用split函数对uid_array进行切割，返回一个数组，然后使用lateral view explode进行行转列

select shop
      ,uid   --这里是下面生成的列别名
from temp_test6 lateral view explode(split(uid_array, ',')) a as uid;


运行结果如下：
shop  uid
a  1
a  2
a  3
b  4
b  5
b  6

3）应用2：求总聚合结果

举例：

原始数据如下表：
select * from temp_test7;


运行结果如下：
temp_test7.shop  temp_test7.uid
a  1
a  2
a  4
b  4
b  5
b  6

此时想一句hql求出每个商店的来客数，以及两个商店去重后的来客数。由于uid为4的用户同时出现在两家商店，所以统计total维度时用户数为5。最终要得到如下结果：

a 3

b 3

总 5

explode实现，关键点在于构造一个array数组，将原本的聚合维度字段放入，然后任意自定义一个词例如‘total’作为总聚合的维度名称，再自定义一个别名（这里使用total_shop），代表包含total的字段名，聚合时使用新自定义的别名进行聚合，如下：

select total_shop
      ,count(distinct uid) as uid_num
from temp_test7 lateral view explode(array(shop, '总')) a as total_shop
group by total_shop;


运行结果如下：
total_shop  uid_num
a             3
b             3
总            5

这样的写法等价于：

select shop
      ,count(distinct uid) as uid_num
from temp_test7
group by shop


union all


select '总' as shop
      ,count(distinct uid) as uid_num
from temp_test7;

点个在看你最好看

数据库

文章转载自数据分析师的FIRE人生，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

[Hive系列09] 表生成函数

评论