Hive 中的四種排序詳解，再也不會(huì)混淆用法了

Hive 中的四種排序

排序操作是一個(gè)比較常見(jiàn)的操作，尤其是在數(shù)據(jù)分析的時(shí)候，我們往往需要對(duì)數(shù)據(jù)進(jìn)行排序，hive 中和排序相關(guān)的有四個(gè)關(guān)鍵字，今天我們就看一下，它們都是什么作用。

數(shù)據(jù)準(zhǔn)備

下面我們有一份溫度數(shù)據(jù),tab 分割

2008    32.0
2008    21.0
2008    31.5
2008    17.0
2013    34.0
2015    32.0
2015    33.0
2015    15.9
2015    31.0
2015    19.9
2015    27.0
2016    23.0
2016    39.9
2016    32.0

建表加載數(shù)據(jù)

create table ods_temperature(
    `year` int,
    temper float
)
row format delimited fields terminated by '\t';
load data local inpath '/Users/liuwenqiang/workspace/hive/temperature.data' overwrite into table ods_temperature;
SQL

1. order by(全局排序)

order by會(huì)對(duì)輸入做全局排序，因此只有一個(gè)Reducer(多個(gè)Reducer無(wú)法保證全局有序)，然而只有一個(gè)reducer，會(huì)導(dǎo)致當(dāng)輸入規(guī)模較大時(shí)，消耗較長(zhǎng)的計(jì)算時(shí)間

  • 降序：desc
  • 升序：asc 不需要指定，默認(rèn)是升序

需要注意的是它受 hive.mapred.mode的影響，在嚴(yán)格模式下，必須使用limit 對(duì)排序的數(shù)據(jù)量進(jìn)行限制，因?yàn)閿?shù)據(jù)量很大只有一個(gè)reducer的話，會(huì)出現(xiàn)OOM 或者運(yùn)行時(shí)間超長(zhǎng)的情況，所以嚴(yán)格模式下，不適用limit 則會(huì)報(bào)錯(cuò)，更多請(qǐng)參考Hive的嚴(yán)格模式和本地模式

Error: Error while compiling statement: FAILED: SemanticException 1:39 Order by-s without limit are disabled for safety reasons. If you know what you are doing, please set hive.strict.checks.orderby.no.limit to false and make sure that hive.mapred.mode is not set to 'strict' to proceed. Note that you may get errors or incorrect results if you make a mistake while using some of the unsafe features.. Error encountered near token 'year' (state=42000,code=40000)

接下來(lái)我們看一下order by的排序結(jié)果select * from ods_temperature order by year;

2. sort by(分區(qū)內(nèi)排序)

不是全局排序，其在數(shù)據(jù)進(jìn)入reducer前完成排序，也就是說(shuō)它會(huì)在數(shù)據(jù)進(jìn)入reduce之前為每個(gè)reducer都產(chǎn)生一個(gè)排序后的文件。因此，如果用sort by進(jìn)行排序，并且設(shè)置mapreduce.job.reduces>1，則sort by只保證每個(gè)reducer的輸出有序，不保證全局有序。

它不受Hive.mapred.mode屬性的影響，sort by的數(shù)據(jù)只能保證在同一個(gè)reduce中的數(shù)據(jù)可以按指定字段排序。使用sort by你可以指定執(zhí)行的reduce個(gè)數(shù)(通過(guò)set mapred.reduce.tasks=n來(lái)指定)，對(duì)輸出的數(shù)據(jù)再執(zhí)行歸并排序，即可得到全部結(jié)果。

set mapred.reduce.tasks=3;
select * from ods_temperature sort by year;
SQL

發(fā)現(xiàn)上面的輸出好像看不出來(lái)啥，只能看到不是有序的，哈哈，那我們換一種方法，將數(shù)據(jù)輸出到文件，因?yàn)槲覀冊(cè)O(shè)置了reduce數(shù)是3，那應(yīng)該會(huì)有三個(gè)文件輸出

set mapred.reduce.tasks=3;
insert overwrite local directory '/Users/liuwenqiang/workspace/hive/sort' row format delimited fields terminated by '\t' select * from ods_temperature sort by year;
SQL

可以看出這下就清楚多了，我們看到一個(gè)分區(qū)內(nèi)的年份并不同意，那個(gè)年份的數(shù)據(jù)都有

sort by 和order by 的執(zhí)行效率

首先我們看一個(gè)現(xiàn)象，一般情況下我們認(rèn)為sort by 應(yīng)該是比 order by 快的，因?yàn)?order by 只能使用一個(gè)reducer,進(jìn)行全部排序，但是當(dāng)數(shù)據(jù)量比較小的時(shí)候就不一定了，因?yàn)閞educer 的啟動(dòng)耗時(shí)可能遠(yuǎn)遠(yuǎn)數(shù)據(jù)處理的時(shí)間長(zhǎng)，就像下面的例子order by 是比sort by快的

sort by 中的limt

可以在sort by 用limit子句減少數(shù)據(jù)量，使用limit n 后，傳輸?shù)絩educe端的數(shù)據(jù)記錄數(shù)就減少到 n *（map個(gè)數(shù)）,也就是說(shuō)我們?cè)趕ort by 中使用limit 限制的實(shí)際上是每個(gè)reducer 中的數(shù)量，然后再根據(jù)sort by的排序字段進(jìn)行order by，最后返回n 條數(shù)據(jù)給客戶端，也就是說(shuō)你在sort by 用limit子句，最后還是會(huì)使用order by 進(jìn)行最后的排序

order by 中使用limit 是對(duì)排序好的結(jié)果文件去limit 然后交給reducer,可以看到sort by 中l(wèi)imit 子句會(huì)減少參與排序的數(shù)據(jù)量，而order by 中的不行，只會(huì)限制返回客戶端數(shù)據(jù)量的多少。

從上面的執(zhí)行效率，我們看到sort by limit 幾乎是 order by limit 的兩倍了 ，大概才出來(lái)應(yīng)該是多了某個(gè)環(huán)節(jié)

接下來(lái)我們分別看一下order by limit 和 sort by limit 的執(zhí)行計(jì)劃

explain select * from ods_temperature order by year limit 2;






explain select * from ods_temperature sort by year limit 2;

從上面截圖我圈出來(lái)的地方可以看到

  1. sort by limit 比 order by limit 多出了一個(gè)stage(order limit)
  2. sort by limit 實(shí)際上執(zhí)行了兩次limit ,減少了參與排序的數(shù)據(jù)量

3. distribute by(數(shù)據(jù)分發(fā))

distribute by是控制在map端如何拆分?jǐn)?shù)據(jù)給reduce端的。類似于MapReduce中分區(qū)partationer對(duì)數(shù)據(jù)進(jìn)行分區(qū)

hive會(huì)根據(jù)distribute by后面列，將數(shù)據(jù)分發(fā)給對(duì)應(yīng)的reducer，默認(rèn)是采用hash算法+取余數(shù)的方式。

sort by為每個(gè)reduce產(chǎn)生一個(gè)排序文件，在有些情況下，你需要控制某寫特定的行應(yīng)該到哪個(gè)reducer，這通常是為了進(jìn)行后續(xù)的聚集操作。distribute by剛好可以做這件事。因此，distribute by經(jīng)常和sort by配合使用。

例如上面的sort by 的例子中，我們發(fā)現(xiàn)不同年份的數(shù)據(jù)并不在一個(gè)文件中，也就說(shuō)不在同一個(gè)reducer 中，接下來(lái)我們看一下如何將相同的年份輸出在一起，然后按照溫度升序排序

首先我們嘗試一下沒(méi)有distribute by 的SQL的實(shí)現(xiàn)

insert overwrite local directory '/Users/liuwenqiang/workspace/hive/sort' row format delimited fields terminated by '\t'  select * from ods_temperature sort by temper ;
SQL

發(fā)現(xiàn)結(jié)果并沒(méi)有把相同年份的數(shù)據(jù)分配在一起,接下來(lái)我們使用一下distribute by 

insert overwrite local directory '/Users/liuwenqiang/workspace/hive/sort' row format delimited fields terminated by '\t' 
select * from ods_temperature distribute by year sort by temper ;
SQL

這下我們看到相同年份的都放在了一下，可以看出2013 和 2016 放在了一起，但是沒(méi)有一定順序，這個(gè)時(shí)候我們可以對(duì) distribute by 字段再進(jìn)行一下排序

insert overwrite local directory '/Users/liuwenqiang/workspace/hive/sort' row format delimited fields terminated by '\t' 
select * from ods_temperature distribute by year sort by year,temper ;
SQL

4. cluster by

cluster by除了具有distribute by的功能外還兼具sort by的功能。但是排序只能是升序排序，不能指定排序規(guī)則為ASC或者DESC。

當(dāng)分區(qū)字段和排序字段相同cluster by可以簡(jiǎn)化distribute by+sort by 的SQL 寫法，也就是說(shuō)當(dāng)distribute by和sort by 字段相同時(shí)，可以使用cluster by 代替distribute by和sort by

insert overwrite local directory '/Users/liuwenqiang/workspace/hive/sort' row format delimited fields terminated by '\t' 
select * from ods_temperature  distribute by year sort by year ;

insert overwrite local directory '/Users/liuwenqiang/workspace/hive/sort' row format delimited fields terminated by '\t' 
select * from ods_temperature cluster by year;
SQL

我們看到上面兩種SQL寫法的輸出結(jié)果是一樣的，這也就證明了我們的說(shuō)法，當(dāng)distribute by和sort by 字段相同時(shí)，可以使用cluster by 代替distribute by和sort by

當(dāng)你嘗試給cluster by 指定排序方向的時(shí)候，你就會(huì)得到如下錯(cuò)誤。

Error: Error while compiling statement: FAILED: ParseException line 2:46 extraneous input 'desc' expecting EOF near '<EOF>' (state=42000,code=40000)

總結(jié)

  1. order by 是全局排序，可能性能會(huì)比較差；
  2. sort by分區(qū)內(nèi)有序，往往配合distribute by來(lái)確定該分區(qū)都有那些數(shù)據(jù)；
  3. distribute by 確定了數(shù)據(jù)分發(fā)的規(guī)則，滿足相同條件的數(shù)據(jù)被分發(fā)到一個(gè)reducer；
  4. cluster by 當(dāng)distribute by和sort by 字段相同時(shí)，可以使用cluster by 代替distribute by和sort by,但是cluster by默認(rèn)是升序，不能指定排序方向；
  5. sort by limit 相當(dāng)于每個(gè)reduce 的數(shù)據(jù)limit 之后，進(jìn)行order by 然后再limit ；


    作者：柯廣的網(wǎng)絡(luò)日志
    微信公眾號(hào)：Java大數(shù)據(jù)與數(shù)據(jù)倉(cāng)庫(kù)