hive sql 拆分多列 hive sql 分组排序

一 ，排序

1 ，order by ： 全排序，转化成 mr ( 耗时 )

1. 原理 ： 单独新开一个 mr ，进行全局排序操作，只开一个 mr 进行排序
2. sql ：

select * from emp order by sal;

3. 观察控制台：正在运行 mr 程序。

2 ，sort by ： map 端排序，本地有序，不能保证全局有序

1. 原理 ： 每个 reduce 输出的结果是有序的，但是不保证全局是有序的。
2. sql ：

select * from emp sort by sal;

3 ，distribute by ： mr 分区

1. 原理 ： mr 的分区操作，他会根据列的 hash 值，将数据分发到不同的 reduce 中去。
   通常做法 ： 先分区，再排序。
2. 用法 ： 先用 distribute by 分区，再用 sort by 排序。
3. 分区数量 ： 需要我们手动设置，如果不设置，我们是看不到效果的。
   因为我们有三个部门，所以设置 3 个分区
   set mapred.reduce.tasks=3;
4. 查询 ： sql

select empno,ename,deptno,sal from emp distribute by deptno sort by sal;

5. 结果 ： 每个部门排序

4 ，cluster by = distribute by +sort by

查询 ： sql

select empno,ename,deptno,sal from emp cluster by deptno;

5 ，cluster by 的缺陷 ：

1. 只能 key 内部排序，不能 key 之间排序 ( 不同组之间是没有关系的，注意看下面的例子 )
2. 只能升序，不能降序
3. 只能拍一个列，不能排别的列
4. 看结果 ：例子

create table clu as select empno,ename,deptno,sal from emp cluster by empno;

5. 结果 ：

+------------+------------+-------------+----------+--+
| clu.empno  | clu.ename  | clu.deptno  | clu.sal  |
+------------+------------+-------------+----------+--+
| 7521       | WARD       | 30          | 1250.0   |
| 7566       | JONES      | 20          | 2975.0   |
| 7698       | BLAKE      | 30          | 2850.0   |
| 7782       | CLARK      | 10          | 2450.0   |
| 7788       | SCOTT      | 20          | 3000.0   |
| 7839       | KING       | 10          | 5000.0   |
| 7902       | FORD       | 20          | 3000.0   |
| 7369       | SMITH      | 20          | 800.0    |
| 7654       | MARTIN     | 30          | 1250.0   |
| 7876       | ADAMS      | 20          | 1100.0   |
| 7900       | JAMES      | 30          | 950.0    |
| 7499       | ALLEN      | 30          | 1600.0   |
| 7844       | TURNER     | 30          | 1500.0   |
| 7934       | MILLER     | 10          | 1300.0   |
+------------+------------+-------------+----------+--+

6. 结果分析 ：
   并不是全部的 empno 都有序，而是每一组的内部才会 empno 有序
7. 那么，谁跟谁是一组的呢，一共三个分区，看看 hdfs 的文件

0 号分区 ： 分区内部的 empno 是有序的

7521WARD301250.0
7566JONES202975.0
7698BLAKE302850.0
7782CLARK102450.0
7788SCOTT203000.0
7839KING105000.0
7902FORD203000.0

1 号分区 ： 分区内部的 empno 是有序的

7369SMITH20800.0
7654MARTIN301250.0
7876ADAMS201100.0
7900JAMES30950.0

2 号分区 ： 分区内部的 empno 是有序的

7499ALLEN301600.0
7844TURNER301500.0
7934MILLER101300.0

8. 查询 sql ：查看结果 ，并不是整体排序，而是组内排序

select * from clu;

二 ，参数优化 ：

1 ，每个reduce任务处理的数据量

hive.exec.reducers.bytes.per.reducer（每个 reduce 任务处理的数据量，默认为 1000^3=1G）

2 ，每个任务最大的 reduce 数

hive.exec.reducers.max（每个任务最大的 reduce 数，默认为 999）

3 ，设置 reduce 数量 ：

set mapred.reduce.tasks = 数量;

4 ，优化原则 ：

（1）尽量尽早地过滤数据，减少每个阶段的数据量,对于分区表要加分区，同时只选择需要使用到的字段

（2）单个SQL所起的JOB个数尽量控制在5个以下

（3）慎重使用map join,一般行数小于2000行，大小小于1M(扩容后可以适当放大)的表才能使用,小表要注意放在join的左边。否则会引起磁盘和内存的大量消耗

（4）写SQL要先了解数据本身的特点，如果有join ,group操作的话，要注意是否会有数据倾斜
set hive.exec.reducers.max=200;
set mapred.reduce.tasks= 200;—增大Reduce个数
set hive.groupby.mapaggr.checkinterval=100000 ;–这个是group的键对应的记录条数超过这个值则会进行分拆,值根据具体数据量设置
（5） 如果 union all 的部分个数大于2，或者每个union部分数据量大，应该拆成多个insert into 语句，这样会提升执行的速度。