關(guān)于 sql 語(yǔ)句的執(zhí)行順序網(wǎng)上有很多資料,但是大多都沒(méi)進(jìn)行驗(yàn)證,并且很多都有點(diǎn)小錯(cuò)誤,尤其是對(duì)于 select 和 group by 執(zhí)行的先后順序,有說(shuō) select 先執(zhí)行,有說(shuō) group by 先執(zhí)行,到底它倆誰(shuí)先執(zhí)行呢?

今天我們通過(guò) explain 來(lái)驗(yàn)證下 sql 的執(zhí)行順序。

在驗(yàn)證之前,先說(shuō)結(jié)論,Hive 中 sql 語(yǔ)句的執(zhí)行順序如下:

from ．． where ．． join ．． on ．． select ．． group by ．． select ．． having ．． distinct ．． order by ．． limit ．． union/union all

可以看到 group by 是在兩個(gè) select 之間,我們知道 Hive 是默認(rèn)開(kāi)啟 map 端的 group by 分組的,所以在 map 端是 select 先執(zhí)行,在 reduce 端是 group by先執(zhí)行。

下面我們通過(guò)一個(gè) sql 語(yǔ)句分析下:

select
 sum(b．order_amount) sum_amount,
 count(a．userkey) count_user
from user_info a
left join user_order b
 on a．idno=b．idno
where a．idno > '112233'
group by a．idno
 having count_user>1
limit 10;

上面這條 sql 語(yǔ)句是可以成功執(zhí)行的,我們看下它在 MR 中的執(zhí)行順序:

Map 階段:

執(zhí)行 from,進(jìn)行表的查找與加載;

執(zhí)行 where,注意:sql 語(yǔ)句中 left join 寫(xiě)在 where 之前的,但是實(shí)際執(zhí)行先執(zhí)行 where 操作,因?yàn)?Hive 會(huì)對(duì)語(yǔ)句進(jìn)行優(yōu)化,如果符合謂詞下推規(guī)則,將進(jìn)行謂詞下推;

執(zhí)行 left join 操作,按照 key 進(jìn)行表的關(guān)聯(lián);

執(zhí)行輸出列的操作,注意: select 后面只有兩個(gè)字段(order_amount,userkey),此時(shí) Hive 是否只輸出這兩個(gè)字段呢,當(dāng)然不是,因?yàn)?group by 的是 idno,如果只輸出 select 的兩個(gè)字段,后面 group by 將沒(méi)有辦法對(duì) idno 進(jìn)行分組,所以此時(shí)輸出的字段有三個(gè):idno,order_amount,userkey;

執(zhí)行 map 端的 group by,此時(shí)的分組方式采用的是哈希分組,按照 idno 分組,進(jìn)行order_amount 的 sum 操作和 userkey 的 count 操作,最后按照 idno 進(jìn)行排序(group by 默認(rèn)會(huì)附帶排序操作);

Reduce 階段:

執(zhí)行 reduce 端的 group by,此時(shí)的分組方式采用的是合并分組,對(duì) map 端發(fā)來(lái)的數(shù)據(jù)按照 idno 進(jìn)行分組合并,同時(shí)進(jìn)行聚合操作 sum(order_amount)和 count(userkey);

執(zhí)行 select,此時(shí)輸出的就只有 select 的兩個(gè)字段:sum(order_amount) as sum_amount,count(userkey) as count_user;

執(zhí)行 having,此時(shí)才開(kāi)始執(zhí)行 group by 后的 having 操作,對(duì) count_user 進(jìn)行過(guò)濾,注意:因?yàn)樯弦徊捷敵龅闹挥?select 的兩個(gè)字段了,所以 having 的過(guò)濾字段只能是這兩個(gè)字段;

執(zhí)行 limit,限制輸出的行數(shù)為 10。

上面這個(gè)執(zhí)行順序到底對(duì)不對(duì)呢,我們可以通過(guò) explain 執(zhí)行計(jì)劃來(lái)看下,內(nèi)容過(guò)多,我們分階段來(lái)看。

首先看下 sql 語(yǔ)句的執(zhí)行依賴:

我們看到 Stage-5 是根,也就是最先執(zhí)行 Stage-5,Stage-2 依賴 Stage-5,Stage-0 依賴 Stage-2。

首先執(zhí)行 Stage-5:

圖中標(biāo) ① 處是表掃描操作,注意先掃描的 b 表,也就是 left join 后面的表,然后進(jìn)行過(guò)濾操作(圖中標(biāo) ② 處),我們 sql 語(yǔ)句中是對(duì) a 表進(jìn)行的過(guò)濾,但是 Hive 也會(huì)自動(dòng)對(duì) b 表進(jìn)行相同的過(guò)濾操作,這樣可以減少關(guān)聯(lián)的數(shù)據(jù)量。

接下來(lái)執(zhí)行 Stage-2:首先是 Map 端操作:

先掃描 a 表(圖中標(biāo) ① 處);接下來(lái)進(jìn)行過(guò)濾操作 idno > '112233'(圖中標(biāo) ② 處);然后進(jìn)行 left join,關(guān)聯(lián)的 key 是 idno(圖中標(biāo) ③ 處);執(zhí)行完關(guān)聯(lián)操作之后會(huì)進(jìn)行輸出操作,輸出的是三個(gè)字段,包括 select 的兩個(gè)字段加 group by 的一個(gè)字段(圖中標(biāo) ④ 處);然后進(jìn)行 group by 操作,分組方式是 hash(圖中標(biāo) ⑤ 處);然后進(jìn)行排序操作,按照 idno 進(jìn)行正向排序(圖中標(biāo) ⑥ 處)。

然后是 Reduce 端操作:

首先進(jìn)行 group by 操作,注意此時(shí)的分組方式是 mergepartial 合并分組(圖中標(biāo) ① 處);然后進(jìn)行 select 操作,此時(shí)輸出的字段只有兩個(gè)了,輸出的行數(shù)是 30304 行(圖中標(biāo) ② 處);接下來(lái)執(zhí)行 having 的過(guò)濾操作,過(guò)濾出 count_user>1 的字段,輸出的行數(shù)是 10101 行(圖中標(biāo) ③ 處);然后進(jìn)行 limit 限制輸出的行數(shù)(圖中標(biāo) ④ 處);圖中標(biāo) ⑤ 處表示是否對(duì)文件壓縮,false 不壓縮。

執(zhí)行計(jì)劃中的數(shù)據(jù)量只是預(yù)測(cè)的數(shù)據(jù)量,不是真實(shí)運(yùn)行的,所以數(shù)據(jù)可能不準(zhǔn)!

最后是 Stage-0 階段:

限制最終輸出的行數(shù)為 10 行。

總結(jié)

通過(guò)上面對(duì) SQL 執(zhí)行計(jì)劃的分析,總結(jié)以下幾點(diǎn):

每個(gè) stage 都是一個(gè)獨(dú)立的 MR,復(fù)雜的 hive sql 語(yǔ)句可以產(chǎn)生多個(gè) stage,可以通過(guò)執(zhí)行計(jì)劃的描述,看看具體步驟是什么。

對(duì)于 group by 的 key,必須是表中的字段,對(duì)于 having 的 key,必須是 select 的字段。

order by 是在 select 后執(zhí)行的,所以 order by 的 key 必須是 select 的字段。

select 最好指明字段,select * 會(huì)增加很多不必要的消耗(CPU、IO、內(nèi)存、網(wǎng)絡(luò)帶寬)。