Hive高级函数实战

函数的基本操作

和mysql一样的，hive也是一个主要做统计的工具，所以为了满足各种各样的统计需要，它也内置了相当多的函数

show functions; # 查看所有内置函数
desc function functionName; # 查看指定函数的描述信息
desc function extended functionName; # 显示函数的扩展内容

Hive高级函数应用

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

分组排序取TopN

一个典型的应用场景，分组排序取TopN操作
主要需要使用到ROW_NUMBER() 和 OVER()函数
row_number和over函数通常搭配在一起使用
row_number会对数据编号，编号从1开始
over可以理解为把数据划分到一个窗口内，里面可以加上partition by，表示按照字段对数据进行分组，还可以加上order by 表示对每个分组内的数据按照某个字段进行排序

我们的需求是这样，有一份学生的考试分数信息，语文、数学、英语这三门，需要计算出班级中单科排名前三名学生的姓名

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

create external table student_score(
id int,
name string,
sub string,
score int
) row format delimited
fields terminated by ' '
location '/user/hive/student_score';

load data local inpath '/root/test_hive/hivedata/student_score.data' into table student_score;

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

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

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

15 zs5 english 70 1
14 zs5 math    80 2
13 zs5 chinese 98 3
12 zs4 english 80 4
11 zs4 math    76 5
10 zs4 chinese 90 6
9  zs3 english 72 7
8  zs3 math    83 8
7  zs3 chinese 79 9
6  zs2 english 80 10
5  zs2 math    75 11
4  zs2 chinese 60 12
3  zs1 english 89 13
2  zs1 math    90 14
1  zs1 chinese 80 15

但是我们是希望对这些数据，先分组，再对组内数据进行排序，再编号
所以就需要在over函数内部添加partiton by进行分组，添加order by 进行排序，最终给生成的编号起一个别名num

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

接着就可以获取前三名了

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

结果如下：

13 zs5 chinese 98 1
10 zs4 chinese 90 2
1  zs1 chinese 80 3
3  zs1 english 89 1
6  zs2 english 80 2
12 zs4 english 80 3
2  zs1 math    90 1
8  zs3 math    83 2
14 zs5 math    80 3

前面SQL中的 row_number() 可以替换为 rank() 或者 dense_rank()

其中：

• rank() 表示上下两条记录的score相等时，记录的行号是一样的，但下一个score值的行号递增N（N是重复的次数），比如：有两条并列第一，下一个是第三，没有第二
• dense_rank() 表示上下两条记录的score相等时，下一个score值的行号递增1，比如：有两条并列第一，下一个是第二

总结一下：

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

行转列

行转列就是把多行数据转为一列数据

针对行转列这种需求主要需要使用到 CONCAT_WS()、COLLECT_SET() 、COLLECT_LIST() 函数

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

• CONCAT_WS() 函数可以实现根据指定的分隔符拼接多个字段的值，最终转化为一个带有分隔符的字符串，它可以接收多个参数，第一个参数是分隔符，后面的参数可以是字符串或者字符串数组，最终就是使用分隔符把后面的所有字符串拼接到一块
• COLLECT_LIST()函数可以返回一个list集合，集合中的元素会重复，一般和group by 结合在一起使用
• COLLECT_SET()个函数可以返回一个set集合，集合汇中的元素不重复，一般和group by 结合在一起使用

zs swing
zs footbal
zs sing
zs codeing
zs swing

期望的结果是这样的

zs swing,footbal,sing,codeing,swing

分析一下：
在这其实就是对数据进行了分组，分组之后可以把相同人的爱好保存到一个数组中，再把数组中的数据转成使用逗号分割的字符串

create external table student_favors(
name string,
favor string
) row format delimited
fields terminated by ' '
location '/user/hive/student_favors';

创建表之后才会创建/user/hive/student_favors目录

hdfs dfs -put /root/test_hive/hivedata/student_favors.data /user/hive/student_favors/;
load data local inpath '/root/test_hive/hivedata/student_favors.data' into table student_favors;

上面两种方式都可以

select name,collect_list(favor) as favor_list from student_favors group by name;

然后再使用 concat_ws 把数组中的元素按照指定分隔符转成字符串

select name,concat_ws(',',collect_list(favor)) as favor_list from student_favors group by name;

列转行

列转行是和刚才的行转列反着来的，列转行可以把一列数据转成多行
主要使用到 SPLIT()、EXPLODE()和LATERAL VIEW

• SPLIT()函数，接受一个字串符和切割规则，就类似于java中的split函数，使用切割规则对字符串中的数据进行切割，最终返回一个array数组
• EXPLODE()函数可以接受array或者map，explode(ARRAY)：表示把数组中的每个元素转成一行，explode(MAP) ：表示把map中每个key-value对，转成一行，key为一列，value为一列
• LATERAL VIEW，通常和split， explode等函数一起使用。

split可以对表中的某一列进行切割，返回一个数组类型的字段，explode可以对这个数组中的每一个元素转为一行，lateral view可以对这份数据产生一个支持别名的虚拟表

zs swing,footbal,sing
ls codeing,swing

希望的结果是这样的

zs swing
zs footbal
zs sing
ls codeing
ls swing

create external table student_favors_2(
name string,
favorlist string
) row format delimited
fields terminated by ' '
location '/user/hive/student_favors_2';

load data local inpath '/root/test_hive/hivedata/student_favors_2.data' into table student_favors_2;

select explode(split(favorlist,',')) from student_favors_2;

其实到这里已经实现了列转行了，但是还需要把name字段拼接上，这时候就需要使用lateral view了，否则直接查询name字段会报错

lateral view相当于把explode返回的数据作为一个虚拟表来使用了，起名字为table1，然后给这个表里面的那一列数据起一个名字叫favor_new，如果有多个字段，可以再后面指定多个。这样在select后面就可以使用这个名字了，有点类似join操作了

select name,favor_new from student_favors_2 lateral view explode(split(favorlist,',')) table1 as favor_new;

结果如下：

zs swing
zs footbal
zs sing
ls codeing
ls swing

Hive排序相关函数

ORDER BY

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

SORT BY

Hive中指定了sort by，如果有多个reduce，那么在每个reducer端都会做排序，也就是说保证了局部有序（每个reducer出来的数据是有序的，但是不能保证所有的数据是全局有序的，除非只有一个reducer）

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

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

DISTRIBUTE BY

只会根据指定的key对数据进行分区，所有的id相同的数据会被送到同一个reducer去处理，但是不会排序。一般情况下可以和sort by 结合使用，先对数据分区，再进行排序

两者结合使用的时候distribute by必须要写在sort by之前

可以结合sort by实现分区内的排序，默认是升序，可以通过desc来设置倒序

select id from t2_bak distribute by id sort by id;

CLUSTER BY

cluster by的功能就是distribute by和sort by的简写形式
也就是 cluster by id 等于 distribute by id sort by id

注意被cluster by指定的列只能是升序，不能指定asc和desc

Hive 的分组和去重函数

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

select count(distinct name) from order;
select count(tmp.name) from (select name from order group by name) tmp;

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