Hive SQL語句的正確執(zhí)行順序

今天我們通過 explain 來驗證下 sql 的執(zhí)行順序。

在驗證之前，先說結(jié)論，Hive 中 sql 語句的執(zhí)行順序如下：

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

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

下面我們通過一個 sql 語句分析下：

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 語句是可以成功執(zhí)行的，我們看下它在 MR 中的執(zhí)行順序：

Map 階段：

1. 執(zhí)行 from，進行表的查找與加載；

2. 執(zhí)行 where，注意：sql 語句中 left join 寫在 where 之前的，但是實際執(zhí)行先執(zhí)行 where 操作，因為 Hive 會對語句進行優(yōu)化，如果符合謂詞下推規(guī)則，將進行謂詞下推；

3. 執(zhí)行 left join 操作，按照 key 進行表的關(guān)聯(lián)；

4. 執(zhí)行輸出列的操作，注意： select 后面只有兩個字段（order_amount，userkey），此時 Hive 是否只輸出這兩個字段呢，當然不是，因為 group by 的是 idno，如果只輸出 select 的兩個字段，后面 group by 將沒有辦法對 idno 進行分組，所以此時輸出的字段有三個：idno，order_amount，userkey;

5. 執(zhí)行 map 端的 group by，此時的分組方式采用的是哈希分組，按照 idno 分組，進行 order_amount 的 sum 操作和 userkey 的 count 操作，最后按照 idno 進行排序（group by 默認會附帶排序操作）；

Reduce 階段：

6. 執(zhí)行 reduce 端的 group by，此時的分組方式采用的是合并分組，對 map 端發(fā)來的數(shù)據(jù)按照 idno 進行分組合并，同時進行聚合操作 sum(order_amount)和 count(userkey)；

7. 執(zhí)行 select，此時輸出的就只有 select 的兩個字段：sum(order_amount) as sum_amount，count(userkey) as count_user;

8. 執(zhí)行 having，此時才開始執(zhí)行 group by 后的 having 操作，對 count_user 進行過濾，注意：因為上一步輸出的只有 select 的兩個字段了，所以 having 的過濾字段只能是這兩個字段；

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

上面這個執(zhí)行順序到底對不對呢，我們可以通過 explain 執(zhí)行計劃來看下，內(nèi)容過多，我們分階段來看。

1. 首先看下 sql 語句的執(zhí)行依賴：

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

2. 首先執(zhí)行 Stage-5：

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

3. 接下來執(zhí)行 Stage-2：

• 首先是 Map 端操作：

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

• 然后是 Reduce 端操作：

首先進行 group by 操作，注意此時的分組方式是 mergepartial 合并分組（圖中標 ① 處）；然后進行 select 操作，此時輸出的字段只有兩個了，輸出的行數(shù)是 30304 行（圖中標 ② 處）；接下來執(zhí)行 having 的過濾操作，過濾出 count_user>1 的字段，輸出的行數(shù)是 10101 行（圖中標 ③ 處）；然后進行 limit 限制輸出的行數(shù)（圖中標 ④ 處）；圖中標 ⑤ 處表示是否對文件壓縮，false 不壓縮。

執(zhí)行計劃中的數(shù)據(jù)量只是預(yù)測的數(shù)據(jù)量，不是真實運行的，所以數(shù)據(jù)可能不準！

4. 最后是 Stage-0 階段：

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

總結(jié)

通過上面對 SQL 執(zhí)行計劃的分析，總結(jié)以下幾點：

1. 每個 stage 都是一個獨立的 MR，復(fù)雜的 hive sql 語句可以產(chǎn)生多個 stage，可以通過執(zhí)行計劃的描述，看看具體步驟是什么。

2. 對于 group by 的 key，必須是表中的字段，對于 having 的 key，必須是 select 的字段。

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

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