Hive Explain 语句类似Mysql 的Explain 语句，提供了对应查询的执行计划，对于我们在理解Hive底层逻辑、Hive调优、Hive SQL书写等方面提供了一个参照，在我们的生产工作了是一个很有意义的工具。

1.Hive Explain语法

EXPLAIN [EXTENDED|CBO|AST|DEPENDENCY|AUTHORIZATION|LOCKS|VECTORIZATION|ANALYZE] query

hive explain 的语法规则如上，后面将按照对应的子句进行探讨。

1.1 基本语法: Explain HiveSQL

EXTENDED 语句会在执行计划中产生关于算子（Operator）的额外信息，这些信息都是典型的物理信息，如文件名称等。

在执行Explain QUERY 之后，一个查询会被转化为包含多个Stage的语句（看起来更像一个DAG）。这些Stages要么是map/reduce Stage，要么是做些元数据或文件系统操作的Stage （如 move 、rename等）。Explain的输出包含2个部分：

  • 执行计划不同Stage之间的以来关系（Dependency）
  • 每个Stage的执行描述信息（Description）

以下将通过一个简单的例子进行解释。

执行Explain 语句：

EXPLAIN 
SELECT SUM(id) FROM test1;

Explain输出结果解析：

  • 依赖图
STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

这里显示有3个Stage， stage-1是根阶段，stage-2依赖stage-1，stage-0依赖stage-2

  • 各Stage的计划
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

STAGE PLAN 中包含2个MapReduce Stage （stage-1和stage-0）。

每个Mapreduce Stage本身包含两个部分：

  1. Map Operator Tree：map端的执行计划树
  2. Reduce Operator Tree的： reduce端的执行计划树

这两个执行计划树里边，包含这条SQL的operator：

  1. map端第一个操作肯定是加载表，所以就是 TableScan 表扫描操作，常见的属性：
  • alias： 表名称
  • Statistics： 表统计信息，包含表中数据条数，数据大小等
  1. Select Operator： 选取操作，常见的属性 ：
  • expressions：需要的字段名称及字段类型
  • outputColumnNames：输出的列名称
  • Statistics：表统计信息，包含表中数据条数，数据大小等
  1. Group By Operator：分组聚合操作，常见的属性：
  • aggregations：显示聚合函数信息
  • mode：聚合模式，值有 hash：随机聚合，就是hash partition；partial：局部聚合；final：最终聚合
  • keys：分组的字段，如果没有分组，则没有此字段
  • outputColumnNames：聚合之后输出列名
  • Statistics： 表统计信息，包含分组聚合之后的数据条数，数据大小等
  1. Reduce Output Operator：输出到reduce操作，常见属性：
  • sort order：值为空 不排序；值为 + 正序排序，值为 - 倒序排序；值为 ± 排序的列为两列，第一列为正序，第二列为倒序
  1. Filter Operator：过滤操作，常见的属性：
  • predicate：过滤条件，如sql语句中的where id>=1，则此处显示(id >= 1)
  1. Map Join Operator：join 操作，常见的属性：
  • condition map：join方式 ，如Inner Join 0 to 1 Left Outer Join0 to 2
  • keys: join 的条件字段
  • outputColumnNames： join 完成之后输出的字段
  • Statistics： join 完成之后生成的数据条数，大小等
  1. File Output Operator：文件输出操作，常见的属性
  • compressed：是否压缩
  • table：表的信息，包含输入输出文件格式化方式，序列化方式等
  1. Fetch Operator 客户端获取数据操作，常见的属性：
  • limit，值为 -1 表示不限制条数，其他值为限制的条数

根据上面的Stage执行计划:

在Stage-1阶段执行了Map Container，其中的操作顺序：TableScan (表文件扫描)→ Select(字段筛选) → Groupby (map端预聚合) → ReduceOutput (预聚合输出到临时文件)

在Stage-0阶段执行了Reduce Container，其中的操作顺序：Groupby (对Map端输出进行全局聚合) → FileOutput (文件输出到HDFS目录)

1.2 语句：EXPLAIN DEPENDENCY HiveSQL

该子句会展示执行计划中输入源相关的额外信息。

执行以下语句：

EXPLAIN DEPENDENCY
SELECT 
COUNT(*) cnt
FROM testdb.test3 a 
LEFT JOIN 
testdb.test4 b 
ON a.id=b.id
WHERE a.name LIKE 'jack%'
;

输出结果：

{"input_partitions":[{"partitionName":"testdb@test3@date_id=20200101"},{"partitionName":"testdb@test3@date_id=20200102"},{"partitionName":"testdb@test3@date_id=20200103"},{"partitionName":"testdb@test3@date_id=20200104"},{"partitionName":"testdb@test3@date_id=20200105"},{"partitionName":"testdb@test3@date_id=20200106"},{"partitionName":"testdb@test3@date_id=20200107"},{"partitionName":"testdb@test4@date_id=20200101"},{"partitionName":"testdb@test4@date_id=20200102"},{"partitionName":"testdb@test4@date_id=20200103"},{"partitionName":"testdb@test4@date_id=20200105"},{"partitionName":"testdb@test4@date_id=20200107"}],"input_tables":[{"tablename":"testdb@test3","tabletype":"MANAGED_TABLE"},{"tablename":"testdb@test4","tabletype":"MANAGED_TABLE"}]}

上面的输出结果，显示了执行的SQL依赖的底层数据，以一个大JSON的方式展示处理，其中包含了input_tables 和 input_partitions两个字段，分别底层扫描的表和对应扫描的分区（如果表是分区表）

1.3 语句：EXPLAIN AUTHORIZATION HiveSQL

该子句会展示执行查询的所有需要被授权的实体，若存在授权失败的情况也会展示。

执行下边的语句：

EXPLAIN AUTHORIZATION
SELECT * FROM src JOIN srcpart;

输出结果：

INPUTS:
  default@srcpart
  default@src
  default@srcpart@ds=2008-04-08/hr=11
  default@srcpart@ds=2008-04-08/hr=12
  default@srcpart@ds=2008-04-09/hr=11
  default@srcpart@ds=2008-04-09/hr=12
OUTPUTS:
  hdfs://localhost:9000/tmp/.../-mr-10000
CURRENT_USER:
  navis
OPERATION:
  QUERY
AUTHORIZATION_FAILURES:
  Permission denied: Principal [name=navis, type=USER] does not have following privileges for operation QUERY [[SELECT] on Object [type=TABLE_OR_VIEW, name=default.src], [SELECT] on Object [type=TABLE_OR_VIEW, name=default.srcpart]]

结果中展示了输入、输出的对应文件目录，以及用户、操作类型的情况，特别注意对应授权失败的情况，如果没有对应表的相关权限，会被体现出来。

1.4 其他语句

EXPLAIN CBO/AST/LOCKS/VECTORIZATION/ANALYZE 要在Hive 2.0以后才可用，本次暂不详解

2.Hive Explain 总结

通过对Hive Explain 的学习，我们能在日常的问题排查、性能调优、底层原理等方面更加深入。那么，可以根据其他网友以及自我总结，在以下几个点学到一些东西。

2.1.join 会过滤null值？

//执行下面语句：
 explain select [a.id](http://a.id/),b.user_name from test1 a join test2 b on [a.id=b.id](http://a.id%3Db.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)  --判断：id is not null,进行了过滤
     Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE
     Select Operator
         expressions: id (type: int)
 ......

2.2 group by会进行排序吗？

//执行语句：
 	explain select id,max(user_name) from test1 group by id;
 //输出结果：
 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: +     --排序：根据上面的key 排序，默认正序
              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)
 ......

2.3 子查询过滤和join后过滤性能有差别吗？

//执行SQL1
 explain
 SELECT
 	a.id,
 	b.user_name
 FROM
 	test1 a
 JOIN test2 b ON a.id = b.id
 WHERE
 	a.id > 2;
 
 //执行SQL2
 explain
 SELECT
 	a.id,
 	b.user_name
 FROM
 	(SELECT * FROM test1 WHERE id > 2) a
 JOIN test2 b ON a.id = b.id;
 
 //会发现两个语句的执行计划几乎一样，都是TableScan 然后where过滤，后进行join

2.4 Hive Explain 展示的HiveSQL 各个子句执行顺序？

//执行如下查询的Explain(查询涉及了where、join、on等场景)
 EXPLAIN 
 select 
 a.f_time,a.f_merchant_id,b.create_time,b.agent_class
 from dw.dwd_t_agent_profit_statement_card_di a 
 left join dw.dim_agent_f b 
 on (a.f_agent_id=b.agent_id and b.agent_level=1 )
 where a.date_id>='20210101' 
 ;

具体执行计划如下：

STAGE DEPENDENCIES:
   Stage-4 is a root stage , consists of Stage-1
   Stage-1
   Stage-0 depends on stages: Stage-1
 
 STAGE PLANS:
   Stage: Stage-4
     Conditional Operator
 
   Stage: Stage-1
     Map Reduce
       Map Operator Tree:
           TableScan
             alias: a
             filterExpr: (date_id >= '20210101') (type: boolean)
             Statistics: Num rows: 200284367 Data size: 32253640262 Basic stats: PARTIAL Column stats: NONE
             Reduce Output Operator
               key expressions: f_agent_id (type: string)
               sort order: +
               Map-reduce partition columns: f_agent_id (type: string)
               Statistics: Num rows: 200284367 Data size: 32253640262 Basic stats: PARTIAL Column stats: NONE
               value expressions: f_merchant_id (type: string), f_time (type: timestamp)
           TableScan
             alias: b
             filterExpr: (agent_level = 1) (type: boolean)
             Statistics: Num rows: 990700 Data size: 346454976 Basic stats: COMPLETE Column stats: COMPLETE
             Filter Operator
               predicate: (agent_level = 1) (type: boolean)
               Statistics: Num rows: 61918 Data size: 8606602 Basic stats: COMPLETE Column stats: COMPLETE
               Reduce Output Operator
                 key expressions: agent_id (type: string)
                 sort order: +
                 Map-reduce partition columns: agent_id (type: string)
                 Statistics: Num rows: 61918 Data size: 8606602 Basic stats: COMPLETE Column stats: COMPLETE
                 value expressions: create_time (type: timestamp), agent_class (type: tinyint)
       Reduce Operator Tree:
         Join Operator
           condition map:
                Left Outer Join0 to 1
           keys:
             0 f_agent_id (type: string)
             1 agent_id (type: string)
           outputColumnNames: _col4, _col40, _col90, _col128
           Statistics: Num rows: 220312808 Data size: 35479005057 Basic stats: COMPLETE Column stats: NONE
           Select Operator
             expressions: _col40 (type: timestamp), _col4 (type: string), _col90 (type: timestamp), _col128 (type: tinyint)
             outputColumnNames: _col0, _col1, _col2, _col3
             Statistics: Num rows: 220312808 Data size: 35479005057 Basic stats: COMPLETE Column stats: NONE
             File Output Operator
               compressed: false
               Statistics: Num rows: 220312808 Data size: 35479005057 Basic stats: COMPLETE Column stats: NONE
               table:
                   input format: org.apache.hadoop.mapred.TextInputFormat
                   output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                   serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
 
   Stage: Stage-0
     Fetch Operator
       limit: -1
       Processor Tree:
         ListSink

具体分析如下：

依赖关系：该语句被划分为stage-4、stage-1、stage-0三个阶段，stage-4与stage-1被优化为一个stage执行。
 Stage-4: 一个条件操作（被优化放入stage-1中）
 Stage-1:
 		Map操作树：
 			对应两张表的扫描：
 				表a为分区表，表b为非分区表。
 				SQL中在where条件对a进行了条件筛选，在on关联中对b进行了筛选；
 				对a表选取了f_merchant_id和f_time字段，对b表读取了。
 				（因此：1.在sql中分区表进行分区筛选很有必要 2.子查询中尽可能早的筛选需要的字段，减少IO 3.On中进行条件筛选时要注意join类型，inner join 时On条件与where条件效果一样，left/right join时对主表On条件与where条件效果一样，left/right join时对副表On条件与where条件效果不一样）
 
 		Reduce操作树：
 			先进行join，关联条件 0 f_agent_id 与 1 agent_id，输出读取的字段
 			再进行select操作，按select排列顺序输出字段
 			最后进行文件输出操作，数据数据到对应的表（分区）目录或临时文件目录
 
 stage-0:
 	进行Fetch操作，由于没有写入操作，所以直接进行fetch并展示

那么，可以归纳下hive SQL的执行顺序：

1.先执行from tbl_name 和where 子句，进行表扫描，筛选对应数据
 2.执行join 和 on 子句，join过程中进行on 匹配
 3.执行select操作，获取需要的字段
 4.进行group by 操作（如果有groupby），进行分组
 5.进行聚合操作（如果有，如sum/count/max/min等），进行聚合运算
 6.进行聚合赛选操作（如果有，having 筛选语句），进行聚合后的聚合结果筛选
 7.进行order by操作（如果有），进行排序
 8.进行limit操作（如果有，limit），进行限制输出