一文讀懂 Hive Explain 執行計劃
導讀
前文 《一文讀懂 SQL Server 執行計劃》 中介紹過關係型數據庫 SQL Server 的執行計劃執行計劃在數據開發過程中的重要性，以及如何閱讀執行計劃，根據執行計劃分析 SQL 語句的執行效率問題並提出優化方案。Hive 是基於 Hadoop，實現了通過 SQL 操作 MapRedue 任務，簡化了大數據編程的難度，使得普通用戶也可以完成大數據程序開發。SQL 目前是使用最爲廣泛的結構化數據操作語言，未來大數據框架對 SQL 的支持也必將是一種趨勢。Hive 在經過一系列編譯過程後生成執行計劃並提交 MapReduce 等執行引擎端，數據開發人員除了具備 SQL 的編程能力之外， 還必須具備 SQL 執行效率定位能力，而執行計劃就是開發人員快速打開 SQL 優化大門的一把鑰匙。
HQL 編譯過程
本文重點不會介紹 HQL 編譯過程的詳細內容，對於大部分開發者來說該過程還是比較枯燥的，我們這裏大概瞭解 Hive 會經過如下六個階段後將 HQL 編譯爲物理執行計劃後提交到計算引擎 MapReduce。
-
詞法，語法解析
-
遍歷 AST 抽象出 QB（ Query Block)
-
將 QB 轉化成執行操作樹 OperatorTree
-
邏輯層優化器執行 OperatorTree 變換，生成邏輯執行計劃，
-
遍歷 OperatorTree，翻譯爲 MapReduce 任務，生成物理執行計劃
-
物理層優化器進行 MapReduce 任務變化，最終執行計劃生成
Explain 語法
Hive 提供的查看查詢語句執行計劃的語法如下：
EXPLAIN [EXTENDED|CBO|AST|DEPENDENCY|AUTHORIZATION|LOCKS|VECTORIZATION|ANALYZE] queryEXPLAIN ：查看執行計劃的基本信息；
EXPLAIN EXTENDED：加上 EXTENDED 可以輸出有關計劃的額外信息。這通常是物理信息，例如文件名，這些額外信息在特殊場景下可能會使用到；
EXPLAIN CBO：輸出由 Calcite 優化器生成的計劃。CBO 從 Hive 4.0.0 版本開始支持；
EXPLAIN AST：輸出查詢的抽象語法樹。AST 在轉儲 AST 可能會導致 OOM 錯誤，因此從 EXTENDED 中刪除了，將在 4.0.0 版本中作爲單獨的命令使用，主要用於開發或者高級用戶通過查看抽象語法樹發現問題；
EXPLAIN DEPENDENCY：DEPENDENCY 在 EXPLAIN 語句中使用會產生有關計劃中輸入的額外信息。它顯示了輸入的各種屬性；
EXPLAIN AUTHORIZATION：查看 SQL 操作相關權限的信息，從 Hive 0.14.0 開始支持；
EXPLAIN LOCKS：這對於瞭解系統將獲得哪些鎖以運行指定的查詢很有用。LOCKS 從 Hive 3.2.0 開始支持；
EXPLAIN VECTORIZATION：查看 SQL 的向量化描述信息，顯示爲什麼未對 Map 和 Reduce 進行矢量化。從 Hive 2.3.0 開始支持；
EXPLAIN ANALYZE：用實際的行數註釋計劃。從 Hive 2.2.0 開始支持；
Explain 輸出
一個 HIVE 查詢被轉換爲一個由一個或多個 stage 組成的序列（有向無環圖 DAG）。這些 stage 可以是 Map/Reduce stage，也可以是負責元數據存儲的 stage，也可以是負責文件系統的操作（比如移動和重命名）的 stage。
EXPLAIN 輸出主要包括以下三部分，其中第一部分根據前文介紹在新版本中已經移除，只有其餘兩部分。
1）， 抽象語法樹 （ 該部分已經移除，使用單獨的命令查看 ）
2）， Stage Dependencies: 各個 stage 之間的依賴性
3）， Stage Plan: 各個 stage 的執行計劃
按照上一部分的介紹，我們通過例子來看一下 EXPLAIN 的詳細使用，查詢 HQL 如下，該 SQL 是數據倉庫中常見的數據倉庫需求 —— 獲取銷售區域銷售額並按照銷售額按照降序排列。
SQL 腳本如下：
select ds.salesterritoryregion , sum(sales.salesamount) total_amt
from ods.dws_fact_internetsales sales
left join ods.dim_salesterritory ds on sales.salesterritorykey = ds.salesterritorykey
group by ds.salesterritoryregion
order by 2 desc;執行計劃輸入結果如下，其中 第一部分 —— Stage Dependencies 共有 6 個 Stage，Stage-6 是根 stage，說明這是開始的 stage, Stag-2 依賴 Stage-6， Stage-3 依賴 Stage-2，Stage-0 依賴 Stage-2，Stage 依賴關係說明必須等待被依賴的 stage 執行結束纔可以開始執行當前 stage。一個查詢任務中會有一個或者多個 Stage，每個 Stage 之間可能存在依賴關係，沒有依賴關係的 stage 可以並行執行。
第二部分 —— Stage Plan ，各個 Stage 的執行計劃。Stage 是 Hive 執行任務中的某一個階段，這個階段可能是一個 MR 任務，也可能是一個抽取任務（ Fetch Operator），也可能是一個 Map Reduce Local，也可能是一個 Limit。Stage-6 是 Map Reduce Local Work，本地化的 MapReduce，意味着該表數據量比較小，Hive 選擇將數據拉取到本地直接操作，沒有執行分佈式 MapReduce 任務。看到這裏介紹一下 Fetch Operator 和 Table Scan
Fetch Operator: 客戶端獲取數據操作，常見屬性：
1），limit，當前值是 -1，表示不限制條數，其他值爲限制的條數；
TableScan: 表掃描操作， Map 端的第一個操作肯定是加載表，所以就是表掃描操作。常見屬性：
1），alias: 表名稱
2），Statistics: 表統計信息，包含表中數據條數，數據大小等
Select Operator: 選取操作，常見屬性：
1），expressions：需要的字段名稱及字段類型
2），outputColumnNames：輸出的列名稱
3），Statistics：表統計信息，包含表中數據條數，數據大小等
HashTable Sink Operator
標誌着 Hive 生成的 MapReduce 程序中 Map 階段的結束，同時將 Map 端的字段組合序列化爲 Reduce Key/value, Partition Key，只可能出現在 Map 階段。常見屬性爲：
1），key：Reduce Key & Partition Key，這裏是以 salesterritorykey 作爲 key
Map Join Operator: Join 操作，常見屬性
1），condition map：join 方式，這裏是 Left Outer Join 0 to 1 2），keys：join 的條件字段，這裏是 salesterritorykey 3），outputColumnNames：join 完成之後輸出的字段 4），Statistics：join 完成之後生成的數據條數，大小等
Group By Operator: 分組聚合操作，常見屬性：
1），aggregations：顯示聚合函數信息 2），mode：聚合模式，有 hash：隨機聚合，就是 hash partition；partial：局部聚合；final：最終聚合 3），keys：分組的字段，如果沒有分組，則沒有此字段 4），outputColumnNames：聚合之後輸出列名 5），Statistics：表統計信息，包含分組聚合之後的數據條數，數據大小等
Reduce Output Operator: 輸出到 Reduce 操作，常見屬性：
1），sort order：值爲空 不排序；值爲 + 正序排序，值爲 - 倒序排序；值爲 +- 排序的列爲兩列，第一列爲正序，第二列爲倒序；
File Output Operator: 文件輸出操作，常見的屬性：
1），compressed：是否壓縮 2），table：表的信息，包含輸入輸出文件格式化方式，序列化方式等
Filter Operator: 過濾操作，常見屬性：
1），predicate：過濾條件，SQL 語句中的過濾條件，本實例中沒有使用過濾條件，所以沒有出現 Filter Operator，大家可以自己嘗試；
hive> explain select ds.salesterritoryregion , sum(sales.salesamount) total_amt
> from ods.dws_fact_internetsales sales
> left join ods.dim_salesterritory ds on sales.salesterritorykey = ds.salesterritorykey
> group by ds.salesterritoryregion
> order by 2 desc;
OK
STAGE DEPENDENCIES:
Stage-6 is a root stage
Stage-2 depends on stages: Stage-6
Stage-3 depends on stages: Stage-2
Stage-0 depends on stages: Stage-3
STAGE PLANS:
Stage: Stage-6
Map Reduce Local Work
Alias -> Map Local Tables:
$hdt$_1:ds
Fetch Operator
limit: -1
Alias -> Map Local Operator Tree:
$hdt$_1:ds
TableScan
alias: ds
Statistics: Num rows: 1 Data size: 4407460 Basic stats: COMPLETE Column stats: NONE
Select Operator
expressions: salesterritorykey (type: int), salesterritoryregion (type: string)
outputColumnNames: _col0, _col1
Statistics: Num rows: 1 Data size: 4407460 Basic stats: COMPLETE Column stats: NONE
HashTable Sink Operator
keys:
0 _col0 (type: int)
1 _col0 (type: int)
Stage: Stage-2
Map Reduce
Map Operator Tree:
TableScan
alias: sales
Statistics: Num rows: 1 Data size: 177482752 Basic stats: COMPLETE Column stats: NONE
Select Operator
expressions: salesterritorykey (type: int), salesamount (type: float)
outputColumnNames: _col0, _col1
Statistics: Num rows: 1 Data size: 177482752 Basic stats: COMPLETE Column stats: NONE
Map Join Operator
condition map:
Left Outer Join 0 to 1
keys:
0 _col0 (type: int)
1 _col0 (type: int)
outputColumnNames: _col1, _col3
Statistics: Num rows: 1 Data size: 195231031 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: sum(_col1)
keys: _col3 (type: string)
mode: hash
outputColumnNames: _col0, _col1
Statistics: Num rows: 1 Data size: 195231031 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
key expressions: _col0 (type: string)
sort order: +
Map-reduce partition columns: _col0 (type: string)
Statistics: Num rows: 1 Data size: 195231031 Basic stats: COMPLETE Column stats: NONE
value expressions: _col1 (type: double)
Execution mode: vectorized
Local Work:
Map Reduce Local Work
Reduce Operator Tree:
Group By Operator
aggregations: sum(VALUE._col0)
keys: KEY._col0 (type: string)
mode: mergepartial
outputColumnNames: _col0, _col1
Statistics: Num rows: 1 Data size: 195231031 Basic stats: COMPLETE Column stats: NONE
File Output Operator
compressed: false
table:
input format: org.apache.hadoop.mapred.SequenceFileInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
Stage: Stage-3
Map Reduce
Map Operator Tree:
TableScan
Reduce Output Operator
key expressions: _col1 (type: double)
sort order: -
Statistics: Num rows: 1 Data size: 195231031 Basic stats: COMPLETE Column stats: NONE
value expressions: _col0 (type: string)
Execution mode: vectorized
Reduce Operator Tree:
Select Operator
expressions: VALUE._col0 (type: string), KEY.reducesinkkey0 (type: double)
outputColumnNames: _col0, _col1
Statistics: Num rows: 1 Data size: 195231031 Basic stats: COMPLETE Column stats: NONE
File Output Operator
compressed: false
Statistics: Num rows: 1 Data size: 195231031 Basic stats: COMPLETE Column stats: NONE
table:
input format: org.apache.hadoop.mapred.SequenceFileInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
Stage: Stage-0
Fetch Operator
limit: -1
Processor Tree:
ListSink
Time taken: 0.208 seconds, Fetched: 103 row(s)Hive 優化
到目前， Hive 執行計劃中使用到的所有操作符基本都有介紹到，通過對以上執行計劃的解析，我們大概可以理解 Hive 是如何轉換爲 Map Reduce 任務的，以及每種 SQL 語句對應哪一種 Operator。因此 Hive 的調優最後還是需要對 MapReduce 的工作原理了解， Hive SQL 的調優本質還是 MapReduce 作業的優化。 Hive 中沒有關係型數據庫 (如 SQL Server) 執行計劃緩存等等機制，Hive 作業中主要是對 HDFS 磁盤文件的讀取與計算。所以對 Hive 作業的調優基本有以下幾個原則：
1），儘量使用表分區 2），儘量在讀取數據的時候過濾不必要的數據 3），關聯表的時候不管關聯多少表，儘量使用同列關聯，這樣可以在一個 Map 任務搞定，如果不是同一列，則會新開一個 MapReduce 任務；4），避免空值的影響，如果關聯業務主鍵上存在大量的 null 值，則會有 shuffle 產生，進而引起數據傾斜。5），儘可能在開發階段避免數據傾斜的發生，當數據傾斜發生時可以考慮通過 MapJoin 提前在 Map 階段完成 join 操作，避免不必要的 shuffle 階段，從而提高資源利用率。MapJoin 一般適用於小表關聯大表的場景，不適用於大表和大表的關聯。6），對於大數據量業務關聯鍵值確實分佈不均的情況，可以通過對關聯主鍵首先進行膨脹，藉助隨機數的方式，將引發數據傾斜的數據進行分散到不同的 Reduce 端提高處理效率。
Explain Dependency
explain denpendency 用於列舉查詢 SQL 需要的數據來源，輸出是以 JSON 格式的數據，詳細案例參照下面截圖，主要包含如下兩個部分內容：
1），input_tables：輸入表信息，描述查詢 SQL 依賴的數據來源表，其中 tablename （ 表名 ）以及 tabletype（ 表類型 ）；
hive> explain dependency select ds.salesterritoryregion , sum(sales.salesamount) total_amt
from ods.dws_fact_internetsales sales
left join ods.dim_salesterritory ds on sales.salesterritorykey = ds.salesterritorykey
group by ds.salesterritoryregion
order by 2 desc;
OK
{"input_tables":[{"tablename":"ods@dws_fact_internetsales","tabletype":"MANAGED_TABLE"},{"tablename":"ods@dim_salesterritory","tabletype":"MANAGED_TABLE"}],"input_partitions":[]}
Time taken: 0.223 seconds, Fetched: 1 row(s)2），input_partitions：依賴分區信息，描述查詢 SQL 依賴的數據來源表，其中 tablename （ 表名 ）, tabletype（ 表類型 ）以及依賴的表分區信息，詳細內容看如下 SQL 示例。
hive> explain dependency select shipdate , sum(sales.salesamount) total_amt
from dwh.dws_fact_internetsales sales
where sales.shipdate >= '2014/02/04 00:00:00.000000000'
group by shipdate;
OK
{"input_tables":[{"tablename":"dwh@dws_fact_internetsales","tabletype":"MANAGED_TABLE"}],"input_partitions":[{"partitionName":"dwh@dws_fact_internetsales@shipdate=2014%2F02%2F04 00%3A00%3A00.000000000"}]}
Time taken: 0.174 seconds, Fetched: 1 row(s)結尾
除了以上開發過程中最常使用的 Explain 命令外， Explain 還提供了 Authorization，CBO，Locks 等等命令，Explain 是大數據 Hive 開發過程中比不可少的工具，與 SQL Server 中的執行計劃功能相當，還需要我們仔細研究學習，提高數據開發的開發效率，程序的健壯性。我們只是對 Hive 中 Explain 做了一個簡單的學習與瞭解以及對 Hive 優化的初步瞭解，不足與錯誤之處，還請見諒，還需要在開發過程中進一步實踐，加深對 Hive 以及 MapReduce 的理解。
本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/__1g7ksd6_iOA5JRqQaIHw