萬字長文詳解HiveSQL執行計劃

五分鐘學大資料發表於2022-03-22
HiveSQL

本文目錄：
一、前言
二、SQL的執行計劃

  1. explain 的用法
  2. explain 的使用場景
    案例一：join 語句會過濾 null 的值嗎？
    案例二：group by 分組語句會進行排序嗎？
    案例三：哪條sql執行效率高呢？
    案例四：定位產生資料傾斜的程式碼段
  3. explain dependency的用法
    案例一：識別看似等價的程式碼
    案例二：識別SQL讀取資料範圍的差別
  4. explain authorization 的用法

一、前言

Hive SQL的執行計劃描述SQL實際執行的整體輪廓，通過執行計劃能瞭解SQL程式在轉換成相應計算引擎的執行邏輯，掌握了執行邏輯也就能更好地把握程式出現的瓶頸點，從而能夠實現更有針對性的優化。此外還能幫助開發者識別看似等價的SQL其實是不等價的，看似不等價的SQL其實是等價的SQL。可以說執行計劃是開啟SQL優化大門的一把鑰匙。

要想學SQL執行計劃，就需要學習檢視執行計劃的命令：explain，在查詢語句的SQL前面加上關鍵字explain是檢視執行計劃的基本方法。

學會explain，能夠給我們工作中使用hive帶來極大的便利！

二、SQL的執行計劃

Hive提供的執行計劃目前可以檢視的資訊有以下幾種：

  • explain：檢視執行計劃的基本資訊；

  • explain dependency：dependency在explain語句中使用會產生有關計劃中輸入的額外資訊。它顯示了輸入的各種屬性；

  • explain authorization：檢視SQL操作相關許可權的資訊；

  • explain vectorization：檢視SQL的向量化描述資訊，顯示為什麼未對Map和Reduce進行向量化。從 Hive 2.3.0 開始支援；

  • explain analyze：用實際的行數註釋計劃。從 Hive 2.2.0 開始支援；

  • explain cbo：輸出由Calcite優化器生成的計劃。CBO 從 Hive 4.0.0 版本開始支援；

  • explain locks：這對於瞭解系統將獲得哪些鎖以執行指定的查詢很有用。LOCKS 從 Hive 3.2.0 開始支援；

  • explain ast：輸出查詢的抽象語法樹。AST 在 Hive 2.1.0 版本刪除了，存在bug，轉儲AST可能會導致OOM錯誤，將在4.0.0版本修復；

  • explain extended：加上 extended 可以輸出有關計劃的額外資訊。這通常是物理資訊，例如檔名，這些額外資訊對我們用處不大；

1. explain 的用法

Hive提供了explain命令來展示一個查詢的執行計劃，這個執行計劃對於我們瞭解底層原理，Hive 調優，排查資料傾斜等很有幫助。

使用語法如下：

explain query;

在 hive cli 中輸入以下命令(hive 2.3.7)：

explain select sum(id) from test1;

得到結果：

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: test1
            Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: id (type: int)
              outputColumnNames: id
              Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                aggregations: sum(id)
                mode: hash
                outputColumnNames: _col0
                Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
                Reduce Output Operator
                  sort order:
                  Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col0 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: sum(VALUE._col0)
          mode: mergepartial
          outputColumnNames: _col0
          Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
          File Output Operator
            compressed: false
            Statistics: Num rows: 1 Data size: 8 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

看完以上內容有什麼感受，是不是感覺都看不懂，不要著急，下面將會詳細講解每個引數，相信你學完下面的內容之後再看 explain 的查詢結果將遊刃有餘。

一個HIVE查詢被轉換為一個由一個或多個stage組成的序列（有向無環圖DAG）。這些stage可以是MapReduce stage，也可以是負責後設資料儲存的stage，也可以是負責檔案系統的操作（比如移動和重新命名）的stage。

我們將上述結果拆分看，先從最外層開始，包含兩個大的部分：

  1. stage dependencies： 各個stage之間的依賴性
  2. stage plan： 各個stage的執行計劃

先看第一部分 stage dependencies ，包含兩個 stage，Stage-1 是根stage，說明這是開始的stage，Stage-0 依賴 Stage-1，Stage-1執行完成後執行Stage-0。

再看第二部分 stage plan，裡面有一個 Map Reduce，一個MR的執行計劃分為兩個部分：

  1. Map Operator Tree： MAP端的執行計劃樹
  2. Reduce Operator Tree： Reduce端的執行計劃樹

這兩個執行計劃樹裡面包含這條sql語句的 operator：

  1. TableScan：表掃描操作，map端第一個操作肯定是載入表，所以就是表掃描操作，常見的屬性：
    • alias： 表名稱
    • Statistics： 表統計資訊，包含表中資料條數，資料大小等
  2. Select Operator： 選取操作，常見的屬性 ：
    • expressions：需要的欄位名稱及欄位型別
    • outputColumnNames：輸出的列名稱
    • Statistics：表統計資訊，包含表中資料條數，資料大小等
  3. Group By Operator：分組聚合操作，常見的屬性：
    • aggregations：顯示聚合函式資訊
    • mode：聚合模式，值有 hash：隨機聚合，就是hash partition；partial：區域性聚合；final：最終聚合
    • keys：分組的欄位，如果沒有分組，則沒有此欄位
    • outputColumnNames：聚合之後輸出列名
    • Statistics： 表統計資訊，包含分組聚合之後的資料條數，資料大小等
  4. Reduce Output Operator：輸出到reduce操作，常見屬性：
    • sort order：值為空 不排序；值為 + 正序排序，值為 - 倒序排序；值為 +- 排序的列為兩列，第一列為正序，第二列為倒序
  5. Filter Operator：過濾操作，常見的屬性：
    • predicate：過濾條件，如sql語句中的where id>=1，則此處顯示(id >= 1)
  6. Map Join Operator：join 操作，常見的屬性：
    • condition map：join方式 ，如Inner Join 0 to 1 Left Outer Join0 to 2
    • keys: join 的條件欄位
    • outputColumnNames： join 完成之後輸出的欄位
    • Statistics： join 完成之後生成的資料條數，大小等
  7. File Output Operator：檔案輸出操作，常見的屬性
    • compressed：是否壓縮
    • table：表的資訊，包含輸入輸出檔案格式化方式，序列化方式等
  8. Fetch Operator 客戶端獲取資料操作，常見的屬性：
    • limit，值為 -1 表示不限制條數，其他值為限制的條數

2. explain 的使用場景

本節介紹 explain 能夠為我們在生產實踐中帶來哪些便利及解決我們哪些迷惑

案例一：join 語句會過濾 null 的值嗎？

現在，我們在hive cli 輸入以下查詢計劃語句

select 
  a.id,
  b.user_name 
from test1 a 
join test2 b 
on a.id=b.id;

問：上面這條 join 語句會過濾 id 為 null 的值嗎

執行下面語句：

explain 
select 
  a.id,
  b.user_name 
from test1 a 
join test2 b 
on a.id=b.id;

我們來看結果 (為了適應頁面展示，僅擷取了部分輸出資訊)：

TableScan
 alias: a
 Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
 Filter Operator
    predicate: id is not null (type: boolean)
    Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
    Select Operator
        expressions: id (type: int)
        outputColumnNames: _col0
        Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
        HashTable Sink Operator
           keys:
             0 _col0 (type: int)
             1 _col0 (type: int)
 ...

從上述結果可以看到 predicate: id is not null 這樣一行，說明 join 時會自動過濾掉關聯欄位為 null 值的情況，但 left join 或 full join 是不會自動過濾null值的，大家可以自行嘗試下。

案例二：group by 分組語句會進行排序嗎？

看下面這條sql

select 
  id,
  max(user_name) 
from test1 
group by id;

問：group by 分組語句會進行排序嗎

直接來看 explain 之後結果 (為了適應頁面展示，僅擷取了部分輸出資訊)

 TableScan
    alias: test1
    Statistics: Num rows: 9 Data size: 108 Basic stats: COMPLETE Column stats: NONE
    Select Operator
        expressions: id (type: int), user_name (type: string)
        outputColumnNames: id, user_name
        Statistics: Num rows: 9 Data size: 108 Basic stats: COMPLETE Column stats: NONE
        Group By Operator
           aggregations: max(user_name)
           keys: id (type: int)
           mode: hash
           outputColumnNames: _col0, _col1
           Statistics: Num rows: 9 Data size: 108 Basic stats: COMPLETE Column stats: NONE
           Reduce Output Operator
             key expressions: _col0 (type: int)
             sort order: +
             Map-reduce partition columns: _col0 (type: int)
             Statistics: Num rows: 9 Data size: 108 Basic stats: COMPLETE Column stats: NONE
             value expressions: _col1 (type: string)
 ...

我們看 Group By Operator，裡面有 keys: id (type: int) 說明按照 id 進行分組的，再往下看還有 sort order: + ，說明是按照 id 欄位進行正序排序的。

案例三：哪條sql執行效率高呢？

觀察兩條sql語句

SELECT
 a.id,
 b.user_name
FROM
 test1 a
JOIN test2 b ON a.id = b.id
WHERE
 a.id > 2;
SELECT
 a.id,
 b.user_name
FROM
 (SELECT * FROM test1 WHERE id > 2) a
JOIN test2 b ON a.id = b.id;

這兩條sql語句輸出的結果是一樣的，但是哪條sql執行效率高呢？

有人說第一條sql執行效率高，因為第二條sql有子查詢，子查詢會影響效能；

有人說第二條sql執行效率高，因為先過濾之後，在進行join時的條數減少了，所以執行效率就高了。

到底哪條sql效率高呢，我們直接在sql語句前面加上 explain，看下執行計劃不就知道了嘛！

在第一條sql語句前加上 explain，得到如下結果

hive (default)> explain select a.id,b.user_name from test1 a join test2 b on a.id=b.id where a.id >2;
OK
Explain
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        $hdt$_0:a
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        $hdt$_0:a
          TableScan
            alias: a
            Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (id > 2) (type: boolean)
              Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: id (type: int)
                outputColumnNames: _col0
                Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE
                HashTable Sink Operator
                  keys:
                    0 _col0 (type: int)
                    1 _col0 (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (id > 2) (type: boolean)
              Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: id (type: int), user_name (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE
                Map Join Operator
                  condition map:
                       Inner Join 0 to 1
                  keys:
                    0 _col0 (type: int)
                    1 _col0 (type: int)
                  outputColumnNames: _col0, _col2
                  Statistics: Num rows: 2 Data size: 27 Basic stats: COMPLETE Column stats: NONE
                  Select Operator
                    expressions: _col0 (type: int), _col2 (type: string)
                    outputColumnNames: _col0, _col1
                    Statistics: Num rows: 2 Data size: 27 Basic stats: COMPLETE Column stats: NONE
                    File Output Operator
                      compressed: false
                      Statistics: Num rows: 2 Data size: 27 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
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

在第二條sql語句前加上 explain，得到如下結果

hive (default)> explain select a.id,b.user_name from(select * from  test1 where id>2 ) a join test2 b on a.id=b.id;
OK
Explain
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3

STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        $hdt$_0:test1
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        $hdt$_0:test1
          TableScan
            alias: test1
            Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (id > 2) (type: boolean)
              Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: id (type: int)
                outputColumnNames: _col0
                Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE
                HashTable Sink Operator
                  keys:
                    0 _col0 (type: int)
                    1 _col0 (type: int)

  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (id > 2) (type: boolean)
              Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: id (type: int), user_name (type: string)
                outputColumnNames: _col0, _col1
                Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE
                Map Join Operator
                  condition map:
                       Inner Join 0 to 1
                  keys:
                    0 _col0 (type: int)
                    1 _col0 (type: int)
                  outputColumnNames: _col0, _col2
                  Statistics: Num rows: 2 Data size: 27 Basic stats: COMPLETE Column stats: NONE
                  Select Operator
                    expressions: _col0 (type: int), _col2 (type: string)
                    outputColumnNames: _col0, _col1
                    Statistics: Num rows: 2 Data size: 27 Basic stats: COMPLETE Column stats: NONE
                    File Output Operator
                      compressed: false
                      Statistics: Num rows: 2 Data size: 27 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
      Local Work:
        Map Reduce Local Work

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

大家有什麼發現，除了表別名不一樣，其他的執行計劃完全一樣，都是先進行 where 條件過濾，在進行 join 條件關聯。說明 hive 底層會自動幫我們進行優化，所以這兩條sql語句執行效率是一樣的。

案例四：定位產生資料傾斜的程式碼段

資料傾斜大多數都是大 key 問題導致的。

如何判斷是大 key 導致的問題，可以通過下面方法：

1. 通過時間判斷

如果某個 reduce 的時間比其他 reduce 時間長的多，如下圖，大部分 task 在 1 分鐘之內完成，只有 r_000000 這個 task 執行 20 多分鐘了還沒完成。

注意：要排除兩種情況：

  1. 如果每個 reduce 執行時間差不多，都特別長，不一定是資料傾斜導致的，可能是 reduce 設定過少導致的。

  2. 有時候，某個 task 執行的節點可能有問題，導致任務跑的特別慢。這個時候，mapreduce 的推測執行，會重啟一個任務。如果新的任務在很短時間內能完成，通常則是由於 task 執行節點問題導致的個別 task 慢。但是如果推測執行後的 task 執行任務也特別慢，那更說明該 task 可能會有傾斜問題。

2. 通過任務 Counter 判斷

Counter 會記錄整個 job 以及每個 task 的統計資訊。counter 的 url 一般類似：

http://bd001:8088/proxy/application_1624419433039_1569885/mapreduce/singletaskcounter/task_1624419433039_1569885_r_000000/org.apache.hadoop.mapreduce.FileSystemCounter

通過輸入記錄數，普通的 task counter 如下，輸入的記錄數是 13 億多:

而 task=000000 的 counter 如下，其輸入記錄數是 230 多億。是其他任務的 100 多倍：

定位 SQL 程式碼

1. 確定任務卡住的 stage

  • 通過 jobname 確定 stage：

    一般 Hive 預設的 jobname 名稱會帶上 stage 階段，如下通過 jobname 看到任務卡住的為 Stage-4：

  • 如果 jobname 是自定義的，那可能沒法通過 jobname 判斷 stage。需要藉助於任務日誌：

    找到執行特別慢的那個 task，然後 Ctrl+F 搜尋 “CommonJoinOperator: JOIN struct” 。Hive 在 join 的時候，會把 join 的 key 列印到日誌中。如下：

上圖中的關鍵資訊是：struct<_col0:string, _col1:string, _col3:string>

這時候，需要參考該 SQL 的執行計劃。通過參考執行計劃，可以斷定該階段為 Stage-4 階段：

2. 確定 SQL 執行程式碼

確定了執行階段，即 Stage-4 階段。通過執行計劃，則可以判斷出是執行哪段程式碼時出現了傾斜。還是從此圖，這個 Stage-4 階段中進行連線操作的表別名是 d：

就可以推測出是在執行下面紅框中程式碼時出現了資料傾斜，因為這行的表的別名是 d：

以上僅列舉了4個我們生產中既熟悉又有點迷糊的例子，explain 還有很多其他的用途，如檢視stage的依賴情況、hive 調優等，小夥伴們可以自行嘗試。

3. explain dependency的用法

explain dependency用於描述一段SQL需要的資料來源，輸出是一個json格式的資料，裡面包含以下兩個部分的內容：

  • input_partitions：描述一段SQL依賴的資料來源表分割槽，裡面儲存的是分割槽名的列表，如果整段SQL包含的所有表都是非分割槽表，則顯示為空。

  • input_tables：描述一段SQL依賴的資料來源表，裡面儲存的是Hive表名的列表。

使用explain dependency檢視SQL查詢非分割槽普通表，在 hive cli 中輸入以下命令：

explain dependency select s_age,count(1) num from student_orc;

得到結果：

{"input_partitions":[],"input_tables":[{"tablename":"default@student_tb _orc","tabletype":"MANAGED_TABLE"}]}

使用explain dependency檢視SQL查詢分割槽表，在 hive cli 中輸入以下命令：

explain dependency select s_age,count(1) num from student_orc_partition;

得到結果：

{"input_partitions":[{"partitionName":"default@student_orc_partition@ part=0"}, 
{"partitionName":"default@student_orc_partition@part=1"}, 
{"partitionName":"default@student_orc_partition@part=2"}, 
{"partitionName":"default@student_orc_partition@part=3"},
{"partitionName":"default@student_orc_partition@part=4"}, 
{"partitionName":"default@student_orc_partition@part=5"},
{"partitionName":"default@student_orc_partition@part=6"},
{"partitionName":"default@student_orc_partition@part=7"},
{"partitionName":"default@student_orc_partition@part=8"},
{"partitionName":"default@student_orc_partition@part=9"}], 
"input_tables":[{"tablename":"default@student_orc_partition", "tabletype":"MANAGED_TABLE"}]

explain dependency的使用場景有兩個：

  • 場景一：快速排除。快速排除因為讀取不到相應分割槽的資料而導致任務資料輸出異常。例如，在一個以天分割槽的任務中，上游任務因為生產過程不可控因素出現異常或者空跑，導致下游任務引發異常。通過這種方式，可以快速檢視SQL讀取的分割槽是否出現異常。

  • 場景二：理清表的輸入，幫助理解程式的執行，特別是有助於理解有多重子查詢，多表連線的依賴輸入。

下面通過兩個案例來看explain dependency的實際運用：

案例一：識別看似等價的程式碼

對於剛接觸SQL的程式設計師，很容易將

select * from a inner join b on a.no=b.no and a.f>1 and a.f<3;

等價於

select * from a inner join b on a.no=b.no where a.f>1 and a.f<3;

我們可以通過案例來檢視下它們的區別：

程式碼1：

select 
a.s_no 
from student_orc_partition a 
inner join 
student_orc_partition_only b 
on a.s_no=b.s_no and a.part=b.part and a.part>=1 and a.part<=2;

程式碼2：

select 
a.s_no 
from student_orc_partition a 
inner join 
student_orc_partition_only b 
on a.s_no=b.s_no and a.part=b.part 
where a.part>=1 and a.part<=2;

我們看下上述兩段程式碼explain dependency的輸出結果：

程式碼1的explain dependency結果：

{"input_partitions": 
[{"partitionName":"default@student_orc_partition@part=1"}, 
{"partitionName":"default@student_orc_partition@part=2"},
{"partitionName":"default@student_orc_partition_only@part=0"},
{"partitionName":"default@student_orc_partition_only@part=1"}, 
{"partitionName":"default@student_orc_partition_only@part=2"}], 
"input_tables": [{"tablename":"default@student_orc_partition","tabletype":"MANAGED_TABLE"}, {"tablename":"default@student_orc_partition_only","tabletype":"MANAGED_TABLE"}]}

程式碼2的explain dependency結果：

{"input_partitions": 
[{"partitionName":"default@student_orc_partition@part=1"}, 
{"partitionName" : "default@student_orc_partition@part=2"},
{"partitionName" :"default@student_orc_partition_only@part=1"},
{"partitionName":"default@student_orc_partition_only@part=2"}], 
"input_tables": [{"tablename":"default@student_orc_partition","tabletype":"MANAGED_TABLE"}, {"tablename":"default@student_orc_partition_only","tabletype":"MANAGED_TABLE"}]}

通過上面的輸出結果可以看到，其實上述的兩個SQL並不等價，程式碼1在內連線（inner join）中的連線條件（on）中加入非等值的過濾條件後，並沒有將內連線的右表按照過濾條件進行過濾，內連線在執行時會多讀取part=0的分割槽資料。而在程式碼2中，會過濾掉不符合條件的分割槽。

案例二：識別SQL讀取資料範圍的差別

程式碼1：

explain dependency
select
a.s_no 
from student_orc_partition a 
left join 
student_orc_partition_only b 
on a.s_no=b.s_no and a.part=b.part and b.part>=1 and b.part<=2;

程式碼2：

explain dependency 
select 
a.s_no 
from student_orc_partition a 
left join 
student_orc_partition_only b 
on a.s_no=b.s_no and a.part=b.part and a.part>=1 and a.part<=2;

以上兩個程式碼的資料讀取範圍是一樣的嗎？答案是不一樣，我們通過explain dependency來看下：

程式碼1的explain dependency結果：

{"input_partitions": 
[{"partitionName": "default@student_orc_partition@part=0"}, 
{"partitionName":"default@student_orc_partition@part=1"}, …中間省略7個分割槽
{"partitionName":"default@student_orc_partition@part=9"}, 
{"partitionName":"default@student_orc_partition_only@part=1"}, 
{"partitionName":"default@student_orc_partition_only@part=2"}], 
"input_tables": [{"tablename":"default@student_orc_partition","tabletype":"MANAGED_TABLE"}, {"tablename":"default@student_orc_partition_only","tabletype":"MANAGED_TABLE"}]}

程式碼2的explain dependency結果：

{"input_partitions": 
[{"partitionName":"default@student_orc_partition@part=0"}, 
{"partitionName":"default@student_orc_partition@part=1"}, …中間省略7個分割槽 
{"partitionName":"default@student_orc_partition@part=9"}, 
{"partitionName":"default@student_orc_partition_only@part=0"}, 
{"partitionName":"default@student_orc_partition_only@part=1"}, …中間省略7個分割槽 
{"partitionName":"default@student_orc_partition_only@part=9"}],
"input_tables": [{"tablename":"default@student_orc_partition","tabletype":"MANAGED_TABLE"}, {"tablename":"default@student_orc_partition_only","tabletype":"MANAGED_TABLE"}]}

可以看到，對左外連線在連線條件中加入非等值過濾的條件，如果過濾條件是作用於右表（b表）有起到過濾的效果，則右表只要掃描兩個分割槽即可，但是左表（a表）會進行全表掃描。如果過濾條件是針對左表，則完全沒有起到過濾的作用，那麼兩個表將進行全表掃描。這時的情況就如同全外連線一樣都需要對兩個資料進行全表掃描。

在使用過程中，容易認為程式碼片段2可以像程式碼片段1一樣進行資料過濾，通過檢視explain dependency的輸出結果，可以知道不是如此。

4. explain authorization 的用法

通過explain authorization可以知道當前SQL訪問的資料來源（INPUTS） 和資料輸出（OUTPUTS），以及當前Hive的訪問使用者 （CURRENT_USER）和操作（OPERATION）。

在 hive cli 中輸入以下命令：

explain authorization 
select variance(s_score) from student_tb_orc;

結果如下：

INPUTS: 
  default@student_tb_orc 
OUTPUTS: 
  hdfs://node01:8020/tmp/hive/hdfs/cbf182a5-8258-4157-9194- 90f1475a3ed5/-mr-10000 
CURRENT_USER: 
  hdfs 
OPERATION: 
  QUERY 
AUTHORIZATION_FAILURES: 
  No privilege 'Select' found for inputs { database:default, table:student_ tb_orc, columnName:s_score}

從上面的資訊可知：

上面案例的資料來源是defalut資料庫中的 student_tb_orc表；

資料的輸出路徑是hdfs://node01:8020/tmp/hive/hdfs/cbf182a5-8258-4157-9194-90f1475a3ed5/-mr-10000;

當前的操作使用者是hdfs，操作是查詢；

觀察上面的資訊我們還會看到AUTHORIZATION_FAILURES資訊，提示對當前的輸入沒有查詢許可權，但如果執行上面的SQL的話也能夠正常執行。為什麼會出現這種情況？Hive在預設不配置許可權管理的情況下不進行許可權驗證，所有的使用者在Hive裡面都是超級管理員，即使不對特定的使用者進行賦權，也能夠正常查詢。

最後

通過上面對explain的介紹，可以發現explain中有很多值得我們去研究的內容，讀懂 explain 的執行計劃有利於我們優化Hive SQL，同時也能提升我們對SQL的掌控力。

參考文件

  1. 最強最全面的數倉建設規範指南
  2. 美團資料平臺及數倉建設實踐，超十萬字總結
  3. 五萬字 | Hive知識體系保姆級教程

相關文章