Hive SQL語句的正確執行順序

五分鐘學大資料發表於2021-08-02
HiveSQL

關於 sql 語句的執行順序網上有很多資料，但是大多都沒進行驗證，並且很多都有點小錯誤，尤其是對於 select 和 group by 執行的先後順序，有說 select 先執行，有說 group by 先執行，到底它倆誰先執行呢？

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

在驗證之前，先說結論，Hive 中 sql 語句的執行順序如下：

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 先執行，在 reduce 端是 group by
先執行。

下面我們通過一個 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 語句是可以成功執行的，我們看下它在 MR 中的執行順序：

Map 階段：

  1. 執行 from，進行表的查詢與載入；

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

  3. 執行 left join 操作，按照 key 進行表的關聯；

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

  5. 執行 map 端的 group by，此時的分組方式採用的是雜湊分組，按照 idno 分組，進行
    order_amount 的 sum 操作和 userkey 的 count 操作，最後按照 idno 進行排序（group by 預設會附帶排序操作）；

Reduce 階段：

  1. 執行 reduce 端的 group by，此時的分組方式採用的是合併分組，對 map 端發來的資料按照 idno 進行分組合並，同時進行聚合操作 sum(order_amount)和 count(userkey)；

  2. 執行 select，此時輸出的就只有 select 的兩個欄位：sum(order_amount) as sum_amount，count(userkey) as count_user;

  3. 執行 having，此時才開始執行 group by 後的 having 操作，對 count_user 進行過濾，注意：因為上一步輸出的只有 select 的兩個欄位了，所以 having 的過濾欄位只能是這兩個欄位；

  4. 執行 limit，限制輸出的行數為 10。

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

  1. 首先看下 sql 語句的執行依賴：

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

  1. 首先執行 Stage-5：

圖中標 ① 處是表掃描操作，注意先掃描的 b 表，也就是 left join 後面的表，然後進行過濾操作（圖中標 ② 處），我們 sql 語句中是對 a 表進行的過濾，但是 Hive 也會自動對 b 表進行相同的過濾操作，這樣可以減少關聯的資料量。

  1. 接下來執行 Stage-2：
  • 首先是 Map 端操作：

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

  • 然後是 Reduce 端操作：

首先進行 group by 操作，注意此時的分組方式是 mergepartial 合併分組（圖中標 ① 處）；然後進行 select 操作，此時輸出的欄位只有兩個了，輸出的行數是 30304 行（圖中標 ② 處）；接下來執行 having 的過濾操作，過濾出 count_user>1 的欄位，輸出的行數是 10101 行（圖中標 ③ 處）；然後進行 limit 限制輸出的行數（圖中標 ④ 處）；圖中標 ⑤ 處表示是否對檔案壓縮，false 不壓縮。

執行計劃中的資料量只是預測的資料量，不是真實執行的，所以資料可能不準！

  1. 最後是 Stage-0 階段：

限制最終輸出的行數為 10 行。

總結

通過上面對 SQL 執行計劃的分析，總結以下幾點：

  1. 每個 stage 都是一個獨立的 MR，複雜的 hive sql 語句可以產生多個 stage，可以通過執行計劃的描述，看看具體步驟是什麼。

  2. 對於 group by 的 key，必須是表中的欄位，對於 having 的 key，必須是 select 的欄位。

  3. order by 是在 select 後執行的，所以 order by 的 key 必須是 select 的欄位。

  4. select 最好指明欄位，select * 會增加很多不必要的消耗（CPU、IO、記憶體、網路頻寬）。

相關文章