Hive SQL語(yǔ)句的正確執(zhí)行順序
本文轉(zhuǎn)載自微信公眾號(hào)「五分鐘學(xué)大數(shù)據(jù)」，作者園陌 。轉(zhuǎn)載本文請(qǐng)聯(lián)系五分鐘學(xué)大數(shù)據(jù)公眾號(hào)。
關(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 寫在 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ò)帶寬)。
