order by

全局排序,因此只有一個reducer,設置了多個reducer也不起作用.

-- 設置reducer的個數為2
set mapred.reduce.tasks=2;
-- 查詢
select * from user_install_status_limit order by uptime limit 10;

order by 在hive.mapred.mode = strict 模式下,必須指定 limit,否則執行會報錯.  

-- 設置嚴格模式
set hive.mapred.mode = strict;
-- 全表查詢
select * from user_install_status_limit order by uptime;
-- 報錯信息
FAILED: SemanticException 1:49 Order by-s without limit are disabled for safety reasons. If you know what you are doing, please make sure that hive.strict.checks.large.query is set to false and that hive.mapred.mode is not set to 'strict' to enable them.. Error encountered near token 'uptime'
-- 加上limit就正常執行.
select * from user_install_status_limit order by uptime limit 10;
-- 取消嚴格模式
set hive.mapred.mode = nonstrict;

sort by

局部排序,在數據進入reducer前完成排序. 
用sort by進行排序,如果設置mapred.reduce.tasks = 1,那作用和order by是一樣的.
如果設置mapred.reduce.tasks > 1,則sort by只保證每個reducer的輸出有序,不保證全局有序.

set mapred.reduce.tasks=2;
insert overwrite local directory '/home/hadoop/sort_data' select * from user_install_status_limit sort by uptime;

總結

? 使用 ASC[ascdend] 或 DESC[descend] 關鍵字來設置查詢結果是按升序或降序排列,默認按升序 ASC 排列.
? order by 后可跟多個字段,字段之間用英文逗號隔開,多個字段時,優先級按先后順序而定.
? 若A、B都升序: order by A,B; 若A、B都用降序,必須用兩個DESC: order by A DESC,B DESC;
? 若A用升序,B用降序,SQL該這樣寫: order by A ASC,B DESC; 也可以這樣寫(默認):order by A, B DESC;