hive入门之排序查询（order by,sort by,distribute by,cluster by...）

HQL查询和一般的sql语句的查询基本一样，本文主要总结下hive里面的一些基本排序查询。

数据准备：

SELECT e.ename, d.dname, l.loc_name
FROM   emp e 
JOIN   dept d
ON     d.deptno = e.deptno 
JOIN   location l
ON     d.loc = l.loc;

7369	SMITH	CLERK	7902	1980-12-17	800.00		20
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-2-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-4-2	2975.00		20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-5-1	2850.00		30
7782	CLARK	MANAGER	7839	1981-6-9	2450.00		10
7788	SCOTT	ANALYST	7566	1987-4-19	3000.00		20
7839	KING	PRESIDENT		1981-11-17	5000.00		10
7844	TURNER	SALESMAN	7698	1981-9-8	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-5-23	1100.00		20
7900	JAMES	CLERK	7698	1981-12-3	950.00		30
7902	FORD	ANALYST	7566	1981-12-3	3000.00		20
7934	MILLER	CLERK	7782	1982-1-23	1300.00		10

将这个表格和数据插入hive里面：

1.order by：

使用order by表示的是全局排序，只有一个mapreduce，
ASC（ascend）: 升序（默认）
DESC（descend）: 降序

1. 查询员工信息按工资升序排列：

select * from emp order by sal;

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

 select * from emp order by sal desc;

3. 按照别名排序，按照员工薪水的2倍排序：

select ename, sal*2 twosal from emp order by twosal;

4. 多个列排序，按照部门和工资升序排序：

select ename, deptno, sal from emp order by deptno, sal ;

2.Sort by

Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。
Sort by为每个reducer产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序。
1．设置reduce个数

set mapreduce.job.reduces=3;

2．查看设置reduce个数

set mapreduce.job.reduces;

3．根据部门编号降序查看员工信息

 select * from emp sort by deptno desc;

4．将查询结果导入到文件中（按照部门编号降序排序）

insert overwrite local directory '/home/hive/datas/sortby-result'
 select * from emp sort by deptno desc;

3.Distribute by

Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。
案例实操：
（1）先按照部门编号分区，再按照员工编号降序排序。

 set mapreduce.job.reduces=3;

insert overwrite local directory '/home/hive/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

注意：
1． distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。
2． Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

4.Cluster by

当distribute by和sorts by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。
1）以下两种写法等价

 select * from emp cluster by deptno;

select * from emp distribute by deptno sort by deptno;

注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。