hive中的Order By

原文地址：http://www.oratea.net/?p=622

hive中的order by也是对一个结果集合进行排序，但是和关系型数据库又所有不同。
这不同的地方也是两者在底层架构区别的体现。

hive的参数hive.mapred.mode是控制hive执行mapred的方式的，有两个选项：strict和nonstrict，默认值是nonstrict。
这个两个值对order by的执行有着很大的影响。

测试用例
hive> select * from test09;
OK
100 tom
200 mary
300 kate
400 tim
Time taken: 0.061 seconds

我们先来看看nonstrict的情况。

hive> set hive.mapred.mode=nonstrict;
hive> select * from test09 order by id;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=
In order to set a constant number of reducers:
set mapred.reduce.tasks=
Starting Job = job_201105020924_0065, Tracking URL = http://hadoop00:50030/jobdetails.jsp?jobid=job_201105020924_0065
Kill Command = /home/hjl/hadoop/bin/../bin/hadoop job -Dmapred.job.tracker=hadoop00:9001 -kill job_201105020924_0065
2011-05-03 03:37:41,270 Stage-1 map = 0%, reduce = 0%
2011-05-03 03:37:43,292 Stage-1 map = 50%, reduce = 0%
2011-05-03 03:37:45,314 Stage-1 map = 100%, reduce = 0%
2011-05-03 03:37:50,360 Stage-1 map = 100%, reduce = 100%
Ended Job = job_201105020924_0065
OK
100 tom
200 mary
300 kate
400 tim
Time taken: 15.049 seconds

这个时候order by可以正常的执行，hive启动了一个reduce进行处理，事实上也只能启动一个reduce。

在来看看strict的情况
hive> set hive.mapred.mode=strict;
hive> select * from test09 order by id;
FAILED: Error in semantic analysis: line 1:30 In strict mode, limit must be specified if ORDER BY is present id

这个时候提示你，在strict模式下如果执行order by的操作必须要指定limit。
因为执行order by的时候只能启动单个reduce执行，如果排序的结果集过大，那么执行时间会非常漫长。

hive> select * from test09 order by id limit 4;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=
In order to set a constant number of reducers:
set mapred.reduce.tasks=
Starting Job = job_201105020924_0067, Tracking URL = http://hadoop00:50030/jobdetails.jsp?jobid=job_201105020924_0067
Kill Command = /home/hjl/hadoop/bin/../bin/hadoop job -Dmapred.job.tracker=hadoop00:9001 -kill job_201105020924_0067
2011-05-03 04:18:26,828 Stage-1 map = 0%, reduce = 0%
2011-05-03 04:18:27,842 Stage-1 map = 50%, reduce = 0%
2011-05-03 04:18:29,864 Stage-1 map = 100%, reduce = 0%
2011-05-03 04:18:35,916 Stage-1 map = 100%, reduce = 100%
Ended Job = job_201105020924_0067
OK
100 tom
200 mary
300 kate
400 tim
Time taken: 15.706 seconds

加上limit后，SQL成功执行。