大数据开发工程师-第八周 第5章 Hive高级函数实战

发表于 | 分类于 ， | 浏览 次
字数统计: 3.1k | 阅读时长 ≈ 13

第八周 第5章 Hive高级函数实战

函数的基本操作

1
2
和mysql一样的，hive也是一个主要做统计的工具，所以为了满足各种各样的统计需要，他也内置了相当多的函数，我们可以通过 show functions; 来查看hive中的内置函数
hive (default)> show functions;
1
2
3
查看指定函数的描述信息我们可以使用： desc function functionName;

hive (default)> desc function year;
1
2
显示函数的扩展内容
hive (default)> desc function extended year;

Hive高级函数应用

1
普通的就不说了，mysql中支持的函数这里面大部分都支持，并且hive支持的函数比mysql还要多，在这里我们主要挑几个典型的说一下

分组排序取TopN

1
2
3
4
5
6
一个典型的应用场景，分组排序取TopN操作
主要需要使用到ROW_NUMBER()和OVER()函数
row_number和over函数通常搭配在一起使用

row_number会对数据编号，编号从1开始
over可以理解为把数据划分到一个窗口内，里面可以加上partition by，表示按照字段对数据进行分组，还可以加上order by 表示对每个分组内的数据按照某个字段进行排序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
我们的需求是这样，有一份学生的考试分数信息，语文、数学、英语这三门，需要计算出班级中单科排名前三名学生的姓名
基础数据是这样的
[root@bigdata04 hivedata]# more student_score.data
1 zs1 chinese 80
2 zs1 math 90
3 zs1 english 89
4 zs2 chinese 60
5 zs2 math 75
6 zs2 english 80
7 zs3 chinese 79
8 zs3 math 83
9 zs3 english 72
10 zs4 chinese 90
11 zs4 math 76
12 zs4 english 80
13 zs5 chinese 98
14 zs5 math 80
15 zs5 english 70
1
2
3
4
5
6
7
8
9
建表
create external table student_score(
id int,
name string,
sub string,
score int
)row format delimited
fields terminated by '\t'
location '/data/student_score';
1
2
加载数据
[root@bigdata04 hivedata]# hdfs dfs -put /data/soft/hivedata/student_score.dat

row_number()

1
2
3
我们先使用row_number对数据编号，看一下是什么样子，row_number不能单独使用，在这里需要加上over

select *,row_number() over() from student_score;

image-20230320233700641

1
2
3
4
结果如下：在这里相当于给表里面的所有数据编了一个号，从1开始

但是我们是希望对这些数据，先分组，再对组内数据进行排序，再编号
所以就需要在over函数内部添加partiton by进行分组，添加order by进行排序，最终给生成的编号起了换一个别名num
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
执行sql
select *, row_number() over (partition by sub order by score desc) as num from student_score

13 zs5 chinese 98 1
10 zs4 chinese 90 2
1 zs1 chinese 80 3
7 zs3 chinese 79 4
4 zs2 chinese 60 5
3 zs1 english 89 1
6 zs2 english 80 2
12 zs4 english 80 3
9 zs3 english 72 4
15 zs5 english 70 5
2 zs1 math 90 1
8 zs3 math 83 2
14 zs5 math 80 3
11 zs4 math 76 4
5 zs2 math 75 5
1
2
3
4
5
6
7
接着就可以获取前三名了，
执行sql

select * from (
select *, row_number() over (partition by sub order by score desc) as num
from student_score
) s where s.num<=3

image-20230320233933200

rank()

1
2
3
4
5
前面SQL中的 row_number() 可以替换为 rank() 或者 dense_rank()
其中： rank() 表示上下两条记录的score相等时，记录的行号是一样的，但下一个score值的行号递增N（N是重复的次数），比如：有两条并列第一，下一个是第三，没有第二

执行sql
select *, rank() over (partition by sub order by score desc) as num from student_score

dense_rank()

1
2
3
dense_rank() 表示上下两条记录的score相等时，下一个score值的行号递增1，比如：有两条并列第一，下一个是第二
执行sql
select *, dense_rank() over (partition by sub order by score desc) as num from student_score;

总结

1
2
3
row_number() over() 是正常排序
rank() over() 是跳跃排序，有两个第一名时接下来就是第三名（在各个分组内）
dense_rank() over() 是连续排序，有两个第一名时仍然跟着第二名（在各个分组内）

行转列

1
2
3
4
行转列就是把多行数据转为一列数据
针对行转列这种需求主要需要使用到 CONCAT_WS()、COLLECT_SET() 、COLLECT_LIST()函数

先看一下这几个函数的描述信息，注意，有的函数在帮助文档里面没有描述信息

CONCAT_WS()

1
2
3
4
5
hive (default)> desc function CONCAT_WS;
OK
tab_name
CONCAT_WS(separator, [string | array(string)]+) - returns the concatenation of
Time taken: 0.019 seconds, Fetched: 1 row(s)
1
2
3
CONCAT_WS() 函数可以实现根据指定的分隔符拼接多个字段的值，最终转化为一个带有分隔符的字符串

它可以接收多个参数，第一个参数是分隔符，后面的参数可以是字符串或者字符串数组，最终就是使用分隔符把后面的所有字符串拼接到一块

COLLECT_LIST()

1
2
3
4
5
6
7
hive (default)> desc function COLLECT_LIST;
OK
tab_name
COLLECT_LIST(x) - Returns a list of objects with duplicates
Time taken: 0.014 seconds, Fetched: 1 row(s)

这个函数可以返回一个list集合，集合中的元素会重复，一般和group by 结合在一起使用，一会再演示

COLLECT_SET()

1
2
3
4
5
6
7
8
9
hive (default)> desc function COLLECT_SET;
OK
tab_name
COLLECT_SET(x) - Returns a set of objects with duplicate elements eliminated
Time taken: 0.014 seconds, Fetched: 1 row(s)

这个函数可以返回一个set集合，集合汇中的元素不重复，一般和group by 结合在一起使用，一会再演示

根据前面的分析，使用这几个函数我们就可以实现行转列这个需求了

实现

1
2
3
4
5
6
7
8
9
10
11
12
13
[root@bigdata04 hivedata]# more student_favors.data
zs swing
zs footbal
zs sing
zs codeing
zs swing

期望的结果是这样的
zs swing,footbal,sing,codeing,swing


分析一下：
在这其实就是对数据进行了分组，分组之后可以把相同人的爱好保存到一个数组中，再把数组中的数据转成使用逗号分割的字符串
1
2
3
4
5
6
7
8
9
10
开始对原始数据建表
create external table student_favors(
name string,
favor string
)row format delimited
fields terminated by '\t'
location '/data/student_favors';

上传数据
[root@bigdata04 hivedata]# hdfs dfs -put /data/soft/hivedata/student_favors.data /data/student_favors
1
2
3
4
5
6
7
8
9
10
查看数据
hive (default)> select * from student_favors;
OK
student_favors.name student_favors.favor
zs swing
zs footbal
zs sing
zs codeing
zs swing
Time taken: 0.175 seconds, Fetched: 5 row(s)
1
2
先对name字段进行分组，把favor转成一个数组
select name,collect_list(favor) as favor_list from student_favors group by name;
1
2
结果如下
 zs ["swing","footbal","sing","codeing","swing"]
1
2
3
4
5
然后再使用 concat_ws 把数组中的元素按照指定分隔符转成字符串
这样就实现了多行数据转为一列数据了

执行行转列操作
select name,concat_ws(',',collect_list(favor)) as favor_list from student_favors group by name;
1
2
结果如下
zs swing,footbal,sing,codeing,swing
1
2
3
我们发现这里面有一些爱好是重复的，如果不希望出现重复的话可以使用COLLECT_SET()
执行sql
select name,concat_ws(',',collect_set(favor)) as favor_list from student_favors group by name;

列转行

1
2
3
列转行是和刚才的行转列反着来的，列转行可以把一列数据转成多行
主要使用到 SPLIT()、EXPLODE()和LATERAL VIEW
看一下这几个函数

SPLIT()

1
2
3
4
5
6
7
hive (default)> desc function SPLIT;
OK
tab_name
SPLIT(str, regex) - Splits str around occurances that match regex
Time taken: 0.011 seconds, Fetched: 1 row(s)

split函数，接受一个字串符和切割规则，就类似于java中的split函数，使用切割规则对字符串中的数据进行切割，最终返回一个array数组

EXPLODE()

1
2
3
4
5
6
7
8
9
hive (default)> desc function EXPLODE;
OK
tab_name
EXPLODE(a) - separates the elements of array a into multiple rows, or the ele
Time taken: 0.013 seconds, Fetched: 1 row(s)

explode函数可以接受array或者map
explode(ARRAY)：表示把数组中的每个元素转成一行
explode(MAP) ：表示把map中每个key-value对，转成一行，key为一列，value为一列

LATERAL VIEW

1
2
3
4
Lateral view通常和split，explode等函数一起使用。
split可以对表中的某一列进行切割，返回一个数组类型的字段，explode可以对这个数组中的每一个元素转为一行，lateral view可以对这份数据产生一个支持别名的虚拟表

原始数据如下

实现

1
2
3
[root@bigdata04 hivedata]# more student_favors_2.data
zs swing,footbal,sing
ls codeing,swing
1
2
3
4
5
6
希望的结果是这样的
zs swing
zs footbal
zs sing
ls codeing
ls swing
1
2
3
4
5
6
7
接着建表
create external table student_favors_2(
name string,
favorlist string
)row format delimited
fields terminated by '\t'
location '/data/student_favors_2';
1
2
3
4
5
6
7
8
9
10
上传数据
[root@bigdata04 hivedata]# hdfs dfs -put /data/soft/hivedata/student_favors_2

查看数据
hive (default)> select * from student_favors_2;
OK
student_favors_2.name student_favors_2.favorlist
zs swing,footbal,sing
ls codeing,swing
Time taken: 0.131 seconds, Fetched: 2 row(s)
1
2
3
4
5
6
7
8
先使用split对favorlist字段进行切割
hive (default)> select split(favorlist,',') from student_favors_2;

OK
_c1
["swing","footbal","sing"]
["codeing","swing"]
Time taken: 0.224 seconds, Fetched: 2 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
再使用explode对数据进行操作

hive (default)> select explode(split(favorlist,',')) from student_favors_2;
OK

col
swing
footbal
sing
codeing
swing
Time taken: 0.185 seconds, Fetched: 5 row(s)
1
2
3
其实到这里已经实现了列转行了，但是还需要把name字段拼接上，这时候就需要使用later view了，否则直接查询name字段会报错

laterview相当于把explode返回的数据作为一个虚拟表来使用了，起名字为table1，然后给这个表里面的那一列数据起一个名字叫favor_new，如果有多个字段，可以再后面指定多个。这样在select后面就可以使用这个名字了，有点类似join操作了
1
select name,favor_new from student_favors_2 lateral view explode(split(favorlist,',')) table1 as favor_new;
1
2
3
4
5
6
结果如下：
zs swing
zs footbal
zs sing
ls codeing
ls swing

Hive排序相关函数

ORDER BY

1
Hive中的order by跟传统的sql语言中的order by作用是一样的，会对查询的结果做一次全局排序，使用这个语句的时候生成的reduce任务只有一个

SORT BY

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Hive中指定了sort by，如果有多个reduce，那么在每个reducer端都会做排序，也就是说保证了局部有序（每个reducer出来的数据是有序的，但是不能保证所有的数据是全局有序的，除非只有一个reducer）
使用前面的t2_bak表，表中的数据如下：

hive (default)> select * from t2_bak;
OK
t2_bak.id t2_bak.name
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
执行排序SQL
hive (default)> select id from t2_bak sort by id;
......
Hadoop job information for Stage-1: number of mappers: 1; number of reducers:
......
OK
id
1 
1 
2 
2 
3
3 
4 
4 
5 
5
Time taken: 24.562 seconds, Fetched: 10 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
刚才我们说sort by是局部有序，为什么最终的结果还是全局有序呢？
看里面的日志，现在只有一个reduce任务，所以最终结果还是有序的

动态设置reduce任务数量为2，然后再执行排序的SQL

hive (default)> set mapreduce.job.reduces = 2;
hive (default)> select id from t2_bak sort by id;
.......
Hadoop job information for Stage-1: number of mappers: 1; number of reducers:
......
OK
id
1 3 3 4 5 5 1 2 2 4
Time taken: 27.943 seconds, Fetched: 10 row(s)
1
2
3
此时会发现数据就没有全局排序了，因为有多个reduce了。

不过针对ORDER BY来说，你动态设置再多的reduce数量都没有用，最后还是只产生1个reduce。

DISTRIBUTE BY

1
2
3
4
5
6
7
ditribute by是控制map的输出到reducer是如何划分的

ditribute by：只会根据指定的key对数据进行分区，但是不会排序。
一般情况下可以和sort by结合使用，先对数据分区，再进行排序

两者结合使用的时候distribute by必须要写在sort by之前
先来看一下单独ditribute by的使用
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
hive (default)> set mapreduce.job.reduces = 2;
hive (default)> select id from t2_bak distribute by id;
.......
Number of reduce tasks not specified. Defaulting to jobconf value of: 2
.......
OK
id
4 
2 
4 
2 
5 
3 
1 
5 
3 
1
Time taken: 25.395 seconds, Fetched: 10 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
可以结合sort by实现分区内的排序，默认是升序，可以通过desc来设置倒序

hive (default)> set mapreduce.job.reduces = 2;
hive (default)> select id from t2_bak distribute by id sort by id;
.......
Number of reduce tasks not specified. Defaulting to jobconf value of: 2
.......
id
2 
2 
4 
4 
1 
1 
3 
3 
5 
5
Time taken: 24.468 seconds, Fetched: 10 row(s)
1
hive (default)> select id from t2_bak distribute by id sort by id desc;

CLUSTER BY

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
cluster by的功能就是distribute by和sort by的简写形式
也就是 cluster by id 等于 distribute by id sort by id
注意被cluster by指定的列只能是升序，不能指定asc和desc

hive (default)> set mapreduce.job.reduces = 2;
hive (default)> select id from t2_bak cluster by id;
.....
Number of reduce tasks not specified. Defaulting to jobconf value of: 2

.....
OK
id
2 
2 
4 
4 
1 
1 
3 
3 
5 
5
Time taken: 25.495 seconds, Fetched: 10 row(s)

Hive 的分组和去重函数

1
2
3
4
5
GROUP BY ：对数据按照指定字段进行分组
DISTINCT：对数据中指定字段的重复值进行去重

第一种：select count(distinct name) from order
第二种：select count(tmp.name) from (select name from order group by name) tmp
1
2
第一种：使用distinct会将所有的name都shuffle到一个reducer里面，性能较低
第二种：先对name分组，因为分组的同时其实就是去重，此时是可以并行计算的，然后再计算count

本文标题:大数据开发工程师-第八周 第5章 Hive高级函数实战

文章作者:TTYONG

发布时间:2022年02月20日 - 12:02

最后更新:2023年06月17日 - 21:06

原始链接:http://tianyong.fun/%E5%A4%A7%E6%95%B0%E6%8D%AE%E5%BC%80%E5%8F%91%E5%B7%A5%E7%A8%8B%E5%B8%88-%E7%AC%AC%E5%85%AB%E5%91%A8-%E7%AC%AC5%E7%AB%A0-Hive%E9%AB%98%E7%BA%A7%E5%87%BD%E6%95%B0%E5%AE%9E%E6%88%98%201.html

许可协议: 转载请保留原文链接及作者。

多少都是爱
0%