HiveQL常用查询语句——排序、分桶、分桶抽样子句记录

由于hivesql中查询语句时，常用查询条件比较多，所以专门写一篇博文对HiveQL的查询语句进行总结，理清联系和区别。

目录

1. 排序子句

（1）单列全局排序order by

（2）多列排序

（3）每个MapReduce内部排序（sort by）

（4）分区排序（distribute by 字段1 sort by 字段2）

（5）分区排序（cluster by）

2. 分桶查询（抽样查询）

（1）创建分桶表

（2）分桶抽样查询

---

1. 排序子句

（1）单列全局排序order by

默认升序asc（ascend），降序是desc（descend）

用于select语句的结尾部分

eg：查询员工信息按工资降序排列

0: jdbc:hive2://hadoop100:10000> select * from emp order by sal desc;

（2）多列排序

0: jdbc:hive2://hadoop100:10000> select ename, deptno, sal from emp order by deptno, sal;

+---------+---------+---------+--+
|  ename  | deptno  |   sal   |
+---------+---------+---------+--+
| MILLER  | 10      | 1300.0  |
| CLARK   | 10      | 2450.0  |
| KING    | 10      | 5000.0  |
| SMITH   | 20      | 800.0   |
| ADAMS   | 20      | 1100.0  |
| JONES   | 20      | 2975.0  |
| SCOTT   | 20      | 3000.0  |
| FORD    | 20      | 3000.0  |
| JAMES   | 30      | 950.0   |
| MARTIN  | 30      | 1250.0  |
| WARD    | 30      | 1250.0  |
| TURNER  | 30      | 1500.0  |
| ALLEN   | 30      | 1600.0  |
| BLAKE   | 30      | 2850.0  |
+---------+---------+---------+--+

（3）每个MapReduce内部排序（sort by）

不需要全局排序的情况下，可用sort by为每个reducer产生一个排序文件。

1. 首先设置并查看reduce个数为3

0: jdbc:hive2://hadoop100:10000> set mapreduce.job.reduces=3;
No rows affected (0.006 seconds)
0: jdbc:hive2://hadoop100:10000> set mapreduce.job.reduces;
+--------------------------+--+
|           set            |
+--------------------------+--+
| mapreduce.job.reduces=3  |
+--------------------------+--+

2. 然后依据部门编号降序查看员工信息

0: jdbc:hive2://hadoop100:10000> select * from emp sort by deptno desc;

3. 将降序排序查询结果导入本地目录
hive (hive_db1)> insert overwrite local directory '/opt/module/datas/sortby_result'
               > select * from emp sort by deptno desc;

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

好处是：执行了局部排序之后可以为接下去的全局排序提高不少的效率（其实就是做一次归并排序就可以做到全局排序了）。

（4）分区排序（distribute by 字段1 sort by 字段2）

先按照字段1进行分区，分配给特定的reducer；然后对分区后的结果再按照字段2进行排序

eg： 先对员工表emp按照部门编号进行分区，再按员工编号进行降序排序

hive (hive_db1)> insert overwrite local directory '/opt/module/datas/distribute_result'
               > select * from emp distribute by deptno sort by empno desc;

ps：distribute by的分区规则是依据（分区字段1的hash码）%（reduce的个数）进行模除后，余数相同的分到同一个区。

所有的col1相同的数据会被送到同一个reducer去处理，这就是因为指定了distribute by col1，这个肯定是全局有序的，因为相同的商户会放到同一个reducer去处理。

（5）分区排序（cluster by）

当distribute by和sort by的字段一致时，可以用cluster by

这与spark中的聚合算子aggregateByKey(初始值)（seqOp，combOp）、foldByKey（初始值）（seqOp，seqOp）有点像，都是两个字段一致时，采取另一种写法。

注意：cluster by只能使用默认升序ASC。

以下两种写法等价

hive (hive_db1)> select * from emp distribute by deptno sort by deptno desc;

hive (hive_db1)> select * from emp cluster by deptno;

2. 分桶查询（抽样查询）

分桶的粒度比分区的粒度更细，用于数据打散（字段hash模以分桶数），抽样查询。

分区partition by，是指将hive表中数据按照分区规则，存放在了HDFS上对应不同分区文件夹中；

分组group by，是将一列中相同的元素分为一组；

按照同一字段分区并排序cluster by 字段；

分桶clustered by into x buckets，指对数据进行区分，分桶将整个数据内容按照某列属性值的hash值进行区分，字段hash值%分桶数，按照取模结果进行数据分桶；适合用来打乱重组数据，

如要按照name属性分为3个桶，就是对name属性值的hash值对3取摸，按照取模结果对数据分桶；结果1、2、3的数据分别单独存放在一个文件中，在HDFS中只能看到分区信息看不到分桶信息。

ps：分区依照的列是伪列，所以partition by（字段，类型）；而分桶依照的列是实际存在的列，不需要指定类型clustered by（字段）

分区表和分桶表的详细区别

（1）创建分桶表

两个要素：第一需要set hive.enforce.bucketing = true，第二需要通过子查询方式导入；否则不能产生分桶表

第一步，设置属性

hive (hive_db1)> set hive.enforce.bucketing=true;

第二步，通过子查询创建分桶表

1. 创建普通表stu
hive (hive_db1)> create table stu(id int, name string)
               > row format delimited fields terminated by '\t';

2. 往stu表中导入数据
hive (hive_db1)> load data local inpath '/opt/module/datas/student.txt' into table stu;

3. 创建分桶表stu_buck
hive (hive_db1)> create table stu_buck(id int, name string)
               > clustered by(id) 
               > into 4 buckets
               > row format delimited fields terminated by '\t';


3. 子查询创建分桶表stu_buck
hive (hive_db1)> insert into table stu_buck select * from stu;

查看HDFS50070端口，发现文件按照模值打乱重组分了4个桶

查询一下分桶数据，和stu表的数据进行对比

1. stu表数据
hive (hive_db1)> select * from stu;
OK
stu.id	stu.name
1001	ss1
1002	ss2
1003	ss3
1004	ss4
1005	ss5
1006	ss6
1007	ss7
1008	ss8
1009	ss9
1010	ss10
1011	ss11
1012	ss12
1013	ss13
1014	ss14
1015	ss15
1016	ss16

2. stu_buck分桶表数据
stu_buck.id	stu_buck.name
1016	ss16
1012	ss12
1008	ss8
1004	ss4
1009	ss9
1005	ss5
1001	ss1
1013	ss13
1010	ss10
1002	ss2
1006	ss6
1014	ss14
1003	ss3
1011	ss11
1007	ss7
1015	ss15

发现数据被打乱重组了。

（2）分桶抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive的分桶表就是用来满足这种需求的。

eg：抽取第2桶数据
hive (hive_db1)> select * from stu_buck tablesample(bucket 2 out of 4 on id);
OK
stu_buck.id	stu_buck.name
1009	ss9
1005	ss5
1001	ss1
1013	ss13

抽样语句tablesample（bucket x out of y on 字段）

x表示从哪个桶开始抽取，

y是分桶数的倍数或因子数，决定抽样比例，分桶数/y = 需抽取桶数

eg：共4桶，y=2，则抽取（4/2）=2个桶；eg共4桶，y=8，则抽取（4/8）=1/2桶；

当抽取2桶数据时，抽取第x和x+y桶数据；

并且x的值必须小于y

eg：抽取2桶数据，抽第1桶和第3桶
分析：抽2桶数据，那么y=2，x=1，

hive (hive_db1)> select * from stu_buck tablesample(bucket 1 out of 2 on id);
OK
stu_buck.id	stu_buck.name
1016	ss16
1012	ss12
1008	ss8
1004	ss4
1010	ss10
1002	ss2
1006	ss6
1014	ss14