hive中order by ,sort by ,distribute by, cluster by 的区别（**很详细**）

hive 查询语法

?

1 2 3 4 5 6 7

select [all | distinct] select_ condition, select_ condition from table_name a [join table_other b on a.id=b.id] [where wehre_condition] [group by col_list [having condition]] [cluster by col_list | [distribute by col_list] [sort by col_list | order by col_list]] [limit number]

准备数据：

?

1 2 3

create table if not exists stu_test(id int,name string,sex string,age int) row format delimited fields terminated by ',' ;

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

insert into stu_test values (1,'zs','m',18) ,(2,'ls','m',19) ,(3,'ww','m',20) ,(4,'zq','f',18) ,(5,'ll','f',21) ,(6,'hl','f',19) ,(7,'xh','f',20) ,(8,'cl','f',22) ,(9,'fj','m',19) ,(10,'wb','m',23) ,(11,'wf','f',24) ,(12,'jj','m',21) ,(13,'yy','m',20) ,(14,'ld','f',18) ,(15,'ch','f',22) ;

1.order by col_list:

排序 全局排序 默认为升序asc ,因此只有一个reducer,只有一个reduce task的结果，
比如文件名是000000_0,会导致当输入规模较大时，需要较长的计算时间。

如果指定了hive.mapred.mode=strict（默认值是nonstrict）,这时就必须指定limit来限制输出条数，原因是：所有的数据都会在同一个reducer端进行，数据量大的情况下可能不能出结果，那么在这样的严格模式下，必须指定输出的条数。

 

例如：stu:按照年龄排序

?

1	select * from stu_test order by age desc;

结果：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

id name sex age 11 wf f 24 10 wb m 23 15 ch f 22 8  cl f 22 5  ll f 21 12 jj m 21 13 yy m 20 7  xh f 20 3  ww m 20 9  fj m 19 6  hl f 19 2  ls m 19 14 ld f 18 1  zs m 18 4  zq f 18

2.sort by col_list :

局部排序，其在数据进入reducer前完成排序。因此，如果用sort by 进行排序，并且设置mapred.reduce.tasks>1,
则sort by 只保证每个reducer的输出有序，不保证全局排序。
在每一个reducetask中，每一个小的输出结果排序，但是当reducetask的个数为1的话和order by 的排序结果一致
注意：sort by 指定的字段仅仅是用于排序的字段，不用于分reducetask输出结果，最终的输出文件中的结果是随机生成的

?

1	select * from stu_test sort by age desc;

结果：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

id name sex age 11 wf f 24 10 wb m 23 15 ch f 22 8  cl f 22 5  ll f 21 12 jj m 21 13 yy m 20 7  xh f 20 3  ww m 20 9  fj m 19 6  hl f 19 2  ls m 19 14 ld f 18 1  zs m 18 4  zq f 18

正常（set mapreduce.job.reduces=1）结果和sort by 的结果一致
如果设置reduce task 个数为3的话： set mapreduce.job.reduces=3，此时不一致

?

1 2	set mapreduce.job.reduces=3 select * from stu_test sort by age desc;

结果：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

id name sex age 10 wb m 23 15 ch f 22 8 cl f 22 5 ll f 21 7 xh f 20 9 fj m 19 6 hl f 19   11 wf f 24 12 jj m 21 3 ww m 20 2 ls m 19 14 ld f 18 4 zq f 18   13 yy m 20 1 zs m 18

随机生成3个文件，然后在每个文件中进行排序。

3.distribute by col_list：

根据指定的字段将数据分到不同的reducer,且分发算法是hash散列
类似于分桶的概念 按照指定的distribute by 字段和 设置的reducetask的个数进行取余分组，但是并没有排序，只是分，没有排序
select * from stu_test distribute by age desc; 错误，不能使用desc,因为它不是排序的意思

?

1 2	set mapreduce.job.reduces=3 select * from stu_test distribute by age；

结果：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

id name sex age 000000_0 age%3=0 14 ld f 18 1  zs m 18 4  zq f 18 12 jj m 21 11 wf f 24 5  ll f 21 000000_1 age%3=1 15 ch f 22 9  fj m 19 6  hl f 19 2  ls m 19 8  cl f 22 000000_1 age%3=2 13 yy m 20 7  xh f 20 3  ww m 20 10 wb m 23

 

?

1 2 3

set mapreduce.job.reduces=2   select * from stu_test distribute by age；

 

结果：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

000000_0 age%2=0 15 ch f 22 14 ld f 18 13 yy m 20 11 wf f 24 8 cl f 22 7 xh f 20 4 zq f 18 3 ww m 20 1 zs m 18 000000_0 age%2=1 12 jj m 21 6 hl f 19 10 wb m 23 9 fj m 19 5 ll f 21 2 ls m 19

4.cluster by col_list ：

除了具有distribute by 的功能外，还会对该字段进行排序
cluster by = distribute by+sort by
cluster by id = distribute by id +sort by id
注意：1）cluster by 和sort by 不可以同时使用
2）当分组字段和排序字段是同一个字段的时候 cluster by id = distribute by id +sort by id
不是同一个字段的时候 请不要使用 cluster by id

?

1	select * from stu_test cluster by age；

结果：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

id name sex age 000000_0 age%3=0 14 ld f 18 4  zq f 18 1  zs m 18 12 jj m 21 5  ll f 21 11 wf f 24 000000_1 age%3=1 6  hl f 19 2  ls m 19 9  fj m 19 15 ch f 22 8  cl f 22 000000_1 age%3=2 3  ww m 20 13 yy m 20 7  xh f 20 10 wb m 23