Hive是一个主要做统计的工具,内置了大量函数以支持各种统计需求。通过show functions;可以查看Hive中的内置函数;查看指定函数的描述信息我们可以使用desc function functionName;

# 分组排序取TopN

一个典型的应用场景是分组排序取TopN操作,主要使用row_number()over()函数。

  • row_number()会对数据进行编号,编号从1开始;
  • over()可以理解为将数据划分到一个窗口内,可以指定按字段对数据进行分组,并对每个分组内的数据按照某个字段进行排序。
  • 需求是根据学生的考试分数信息(语文、数学、英语),计算班级中每门科目排名前三名学生的姓名。
[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

建表语句:

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

加载数据:

hdfs dfs -put /data/soft/hivedata/student_score /data/student_score

创建查询SQL语句:

select *, row_number() over(partition by sub order by score desc) from student_score;
# 执行结果如下,先按照sub字段分组,再根据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

接下来找出前3的数据

select * from (select *, row_number() over(partition by sub order by score desc) as num from student_score) AS s where s.num<=3;
# 执行结果如下
s.id    s.name  s.sub   s.score s.num
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相同,记录的行号是一样的
    • 3 zs1 english 89 1 12 zs4 english 80 2 6 zs2 english 80 2
  • dense_rank():上下两个score相同,记录的行号是一样的,但是会区前几
    • 3 zs1 english 89 1 12 zs4 english 80 2 6 zs2 english 80 2 9 zs3 english 72 3

# 行专列

多行数据转成一列数据,用到的函数:concat_ws()collect_list()collect_set()

  1. concat_ws():将多个字符串连接为一个字符串,并使用指定的分隔符进行分隔。
  2. collect_list():将指定列的值收集到一个数组中。
  3. collect_set():将指定列的值收集到一个集合中,去除重复值。
[root@flume hivedata]# cat /data/soft/hivedata/student_favors.data 
zs	swing
zs	footbal
zs	sing
zs	codeing
zs	swing
# 希望的效果:zs	swing,footbal,sing,codeing

# 建表及准备操作

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

上传数据:

hdfs dfs -put /data/soft/hivedata/student_favors.data /data/student_favors

# 编写查询SQL

# 1.将favor转为数组
select name, collect_list(favor) from student_favors group by name;
# 2.将数组转为字符串,使用逗号分割
select name, concat_ws(',', collect_list(favor)) from student_favors group by name;
# 3.如果需要去重,使用collect_set
select name, concat_ws(',', collect_set(favor)) from student_favors group by name;

# 列转行

把一列数据转成多行,主要使用到split()explode()lateral view

  1. split():将字符串按指定的分隔符拆分为数组。
  2. explode():将数组或Map类型的列拆分成多行,每行包含一个元素。
  3. lateral view():将一个表的列展开成多行,用于处理复杂的数据结构,如数组或Map。
[root@flume hivedata]# cat student_favors_2.data 
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 '\t'
location '/data/student_favors_2';

上传数据:

hdfs dfs -put /data/soft/hivedata/student_favors_2.data /data/student_favors_2

# 编写查询SQL

# 先使用split将字符串转为数组
select name, split(favorlist,',') from student_favors_2;
# 使用explode拆分成多行
select explode(split(favorlist,',')) from student_favors_2;
# 注意,直接使用name会报错
select name, favor_new from student_favors_2 lateral view explode(split(favorlist, ',')) table1 as favor_new;

# Hive排序相关函数

# order by

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

# sort by

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

# distribute by

只会根据指定的key对数据进行分区,但是不会排序。一般情况下可以和sort by结合使用,先对数据分区,再进行排序两者结合使用的时候distribute by必须要写在sort by。

 select id from t2_bak distribute by id sort by id; # 先根据id分区,再根据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:对数据中指定字段的重复值进行去重

需求:统计order表中name去重后的数据量

第一种: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