用数据看hive的order distribute sort cluster by的区别

1.distribute by hash分发到不同文件中

分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个文件中。

1.1实验

hive (default)>  select * from empt;
OK
empt.empno	empt.ename	empt.job	empt.mgr	empt.hiredate	empt.sal	empt.comm	empt.deptno
7566	JONES	MANAGER	7863	1980-12-17	2975.0	0	20
7499	NULL	NULL	NULL	NULL	NULL	NULL	NULL
7369	SMITH	CLERK	7902	1980-12-17	800.0	0	20
7499	ALLEN	SALESMAN	7968	1980-12-17	1600.0	300	20


set mapreduce.job.reduces=3;
set hive.merge.mapredfiles=false;
insert overwrite table empt select * from empt distribute by empno;

[root@hadoop logs]# hdfs dfs -cat /hive/db/empt/000000_0
7566,JONES,MANAGER,7863,1980-12-17,2975.0,0,20
[root@hadoop logs]# hdfs dfs -cat /hive/db/empt/000001_0
7369,SMITH,CLERK,7902,1980-12-17,800.0,0,20
[root@hadoop logs]# hdfs dfs -cat /hive/db/empt/000002_0
7451,\N,\N,\N,\N,\N,\N,\N
7499,\N,\N,\N,\N,\N,\N,\N
7499,ALLEN,SALESMAN,7968,1980-12-17,1600.0,300,20


7566%3=0  在文件000000_0中
7396%3=1  在文件000001_0中
7499%3=2  在文件000002_0中

1.2使用场景

a.distribute by 和 order by 不能一起使用，因为前者是分桶，后者是全局排序，有冲突

hive (default)> select * from empt distribute by empno order by empno;
FAILED: ParseException line 1:39 missing EOF at 'order' near 'empno'

b.kylin构建cube的第二步中使用到distributed by

Redistribute Flat Hive Table
	  重新分发数据
	  第一步产出在HDFS上面的数据，文件大小可能会存在分布不均匀的情况：
	  	1.map task ==> 大的文件  ==> 慢
	  	2.map task ==> 小的文件  ==> 快
	  INSERT OVERWRITE TABLE \`kylin_intermediate_ruozedata_user_click_cube_56d719e1_e45a_ec49_1538_d5c5313b82ee\` SELECT * FROM \`kylin_intermediate_ruozedata_user_click_cube_56d719e1_e45a_ec49_1538_d5c5313b82ee\` DISTRIBUTE BY DW_USER_CLICK_D_DAY,DW_USER_CLICK_D_PROVINCE_ID,DW_USER_CLICK_D_CITY_ID;
	  通过distributed by语法对数据进行重新分发 +  mapreduce.job.reduces
           最终达成的效果：使得最终的数据分布非常均匀，且毫无规律

2. sort by 局部排序

2.1 设置reduce job数对sort by有效果

 set mapreduce.job.reduces=1;
 set hive.merge.mapredfiles=false;
 insert overwrite table empt select * from empt sort by empno;

[root@hadoop logs]# hdfs dfs -cat /hive/db/empt/000000_0
7369,SMITH,CLERK,7902,1980-12-17,800.0,0,20
7499,ALLEN,SALESMAN,7968,1980-12-17,1600.0,300,20
7499,\N,\N,\N,\N,\N,\N,\N
7566,JONES,MANAGER,7863,1980-12-17,2975.0,0,20

2.2 直接sort by会出现数据分布不均匀的分区内排序

 set mapreduce.job.reduces=3;
 set hive.merge.mapredfiles=false;
 insert overwrite table empt select * from empt sort by empno;

[root@hadoop logs]# hdfs dfs -cat /hive/db/empt/000000_0
empty
[root@hadoop logs]# hdfs dfs -cat /hive/db/empt/000001_0
7499,\N,\N,\N,\N,\N,\N,\N
7499,ALLEN,SALESMAN,7968,1980-12-17,1600.0,300,20
7566,JONES,MANAGER,7863,1980-12-17,2975.0,0,20
[root@hadoop logs]# hdfs dfs -cat /hive/db/empt/000002_0
7369,SMITH,CLERK,7902,1980-12-17,800.0,0,20

2.3 sort by与distribute by 联合使用实现分区内有序

Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
 set mapreduce.job.reduces=3;
 set hive.merge.mapredfiles=false;
# 默认分区内升序,此处使用降序
 insert overwrite table empt select * from empt distribute by empno sort by empno desc;

[root@hadoop logs]# hdfs dfs -cat /hive/db/empt/000000_0
7566,JONES,MANAGER,7863,1980-12-17,2975.0,0,20
[root@hadoop logs]# hdfs dfs -cat /hive/db/empt/000001_0
7369,SMITH,CLERK,7902,1980-12-17,800.0,0,20
# 分区内降序
[root@hadoop logs]# hdfs dfs -cat /hive/db/empt/000002_0
7499,\N,\N,\N,\N,\N,\N,\N
7499,ALLEN,SALESMAN,7968,1980-12-17,1600.0,300,20
7451,\N,\N,\N,\N,\N,\N,\N

3.cluster by

3.1 cluster by 等价于 distribute by+ sort by(distribute和sort字段相同)

 distribute by empno sort by empno  等价于 cluster by empno
 distribute by empno sort by empno  asc 等价于 cluster by empno
 set mapreduce.job.reduces=3;
 set hive.merge.mapredfiles=false;
# cluster by 后面可以加cluster by empno desc.默认是升序 asc,如果要降序 与 distribute by empno sort by empno desc 模式相同效果
 insert overwrite table empt select * from empt cluster by empno

3.2 distribute by+sort by 比 cluster by 更加灵活

例如:distribute by 字段和sort by 字段可以不一样 ,这里按照empno进行分区,按照ename进行分区内排序
 set mapreduce.job.reduces=3;
 set hive.merge.mapredfiles=false;
 insert overwrite table empt select * from empt distribute by empno sort by ename desc;

[root@hadoop logs]# hdfs dfs -cat /hive/db/empt/000000_0
7566,JONES,MANAGER,7863,1980-12-17,2975.0,0,20
[root@hadoop logs]# hdfs dfs -cat /hive/db/empt/000001_0
7369,SMITH,CLERK,7902,1980-12-17,800.0,0,20

# 通过ename降序
[root@hadoop logs]# hdfs dfs -cat /hive/db/empt/000002_0
7499,ALLEN,SALESMAN,7968,1980-12-17,1600.0,300,20
7499,\N,\N,\N,\N,\N,\N,\N
7451,\N,\N,\N,\N,\N,\N,\N

4.order by

4.1 全局排序的特点

全局排序,默认升序,即使想开3个reduce,最终还是一个reduce,因为是全局排序,如果有多个输出文件实现不了全局排序.

hive (default)> set mapreduce.job.reduces=3;
hive (default)> set hive.merge.mapredfiles=false;
hive (default)>  insert overwrite table empt select * from empt order by empno;

# 一个hdfs输出文件,默认升序
[root@hadoop logs]# hdfs dfs -cat /hive/db/empt/000000_0
7369,SMITH,CLERK,7902,1980-12-17,800.0,0,20
7499,ALLEN,SALESMAN,7968,1980-12-17,1600.0,300,20
7499,\N,\N,\N,\N,\N,\N,\N
7566,JONES,MANAGER,7863,1980-12-17,2975.0,0,20

4.2 使用场景

(1) 数据量不太大（如10G） 或 对处理后的结果数据(数据量不大) 进行全局排序,可直接使用order by 
(2) 在实际生产中，数据量很大（比如100G）,如果直接使用order by，会导致只有一个reduce处理，这么大的数据，会出现很慢的情况。
比如对全世界60多亿人的年消费金额进行降序排序找出Top10，可以采用先sort by 在局部排序中找到前10 ，再进行order by 全局排序中找到前10.简单来说，全局排序中的Top10一定出自于局部排序中的Top10.
select * from
(select * from consumer sort by consumer_amount  desc limit 10) a 
order by consumer_amount desc limit 10;

注意：生产中不要使用 * 进行模糊匹配，特别是hive中使用列式存储parquent等格式，会对性能造成极大影响。

5.总结

1. order by 全局排序 不能和distribute by 一起使用
2. sort by 局部排序，一般要和distribute by 一起使用，如果没有分桶那么就是全局排序
3. distribute by 对查询的结果进行分布，但不是排序
4. cluseter by 对查询的结果进行分布，然后每个桶内只能是按照自然序排序

使用建议

order by : 全局排序，会把数据全部放到一个reduce 排序
sort by : 不是全局排序，其在数据进入reducer前完成排序，只能保证一个reduce 内的数据是按照指定字段排序。
distribute by ：控制map结果的分发，它会将具有相同字段的map输出分发到一个reduce节点上做处理
cluster by : 可以理解为distribute by + sort by 某个特定字段，而且只能升序排列。

因为order by 是全局排序，对计算性能的开销较大，而且集中在单个reduce 上排序并不能起到分布式处理的效果， 所以我们可以通过 distribute by xxx sort by xxx 来进行分区排序。