hive sql执行计划

Hive provides an EXPLAIN command that shows the execution plan for a query. The syntax for this statement is as follows:

EXPLAIN [EXTENDED] query

 

hive> explain SELECT a.bar, count(*) FROM invites a WHERE a.foo > 0 GROUP BY a.bar;

OK
ABSTRACT SYNTAX TREE:
  (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME invites) a)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL a) bar)) (TOK_SELEXPR (TOK_FUNCTIONSTAR count))) (TOK_WHERE (> (. (TOK_TABLE_OR_COL a) foo) 0)) (TOK_GROUPBY (. (TOK_TABLE_OR_COL a) bar))))

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Alias -> Map Operator Tree:
        a
          TableScan
            alias: a
            Filter Operator
              predicate:
                  expr: (foo > 0)
                  type: boolean
              Filter Operator
                predicate:
                    expr: (foo > 0)
                    type: boolean
                Select Operator
                  expressions:
                        expr: bar
                        type: string
                  outputColumnNames: bar
                  Group By Operator
                    aggregations:
                          expr: count()
                    bucketGroup: false
                    keys:
                          expr: bar
                          type: string
                    mode: hash
                    outputColumnNames: _col0, _col1
                    Reduce Output Operator
                      key expressions:
                            expr: _col0
                            type: string
                      sort order: +
                      Map-reduce partition columns:
                            expr: _col0
                            type: string
                      tag: -1
                      value expressions:
                            expr: _col1
                            type: bigint
      Reduce Operator Tree:
        Group By Operator
          aggregations:
                expr: count(VALUE._col0)
          bucketGroup: false
          keys:
                expr: KEY._col0
                type: string
          mode: mergepartial
          outputColumnNames: _col0, _col1
          Select Operator
            expressions:
                  expr: _col0
                  type: string
                  expr: _col1
                  type: bigint
            outputColumnNames: _col0, _col1
            File Output Operator
              compressed: false
              GlobalTableId: 0
              table:
                  input format: org.apache.hadoop.mapred.TextInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

  Stage: Stage-0
    Fetch Operator
      limit: -1


Time taken: 0.133 seconds

hive> explain insert overwrite TABLE lpx SELECT t1.bar, t1.foo, t2.foo FROM pokes t1 JOIN invites t2 ON (t1.bar = t2.bar) ;
OK
ABSTRACT SYNTAX TREE:
  (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_TABREF (TOK_TABNAME pokes) t1) (TOK_TABREF (TOK_TABNAME invites) t2) (= (. (TOK_TABLE_OR_COL t1) bar) (. (TOK_TABLE_OR_COL t2) bar)))) (TOK_INSERT (TOK_DESTINATION (TOK_TAB (TOK_TABNAME lpx))) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL t1) bar)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL t1) foo)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL t2) foo)))))

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

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Alias -> Map Operator Tree:
        t1
          TableScan
            alias: t1
            Reduce Output Operator
              key expressions:
                    expr: bar
                    type: string
              sort order: +
              Map-reduce partition columns:
                    expr: bar
                    type: string
              tag: 0
              value expressions:
                    expr: foo
                    type: int
                    expr: bar
                    type: string
        t2
          TableScan
            alias: t2
            Reduce Output Operator
              key expressions:
                    expr: bar
                    type: string
              sort order: +
              Map-reduce partition columns:
                    expr: bar
                    type: string
              tag: 1
              value expressions:
                    expr: foo
                    type: int
      Reduce Operator Tree:
        Join Operator
          condition map:
               Inner Join 0 to 1
          condition expressions:
            0 {VALUE._col0} {VALUE._col1}
            1 {VALUE._col0}
          handleSkewJoin: false
          outputColumnNames: _col0, _col1, _col5
          Select Operator
            expressions:
                  expr: _col1
                  type: string
                  expr: _col0
                  type: int
                  expr: _col5
                  type: int
            outputColumnNames: _col0, _col1, _col2
            File Output Operator
              compressed: false
              GlobalTableId: 1
              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
                  name: default.lpx

  Stage: Stage-0
    Move Operator
      tables:
          replace: true
          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
              name: default.lpx

  Stage: Stage-2
    Stats-Aggr Operator

注：
ABSTRACT SYNTAX TREE为抽象语法树

从信息头：
STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1
  Stage-2 depends on stages: Stage-0
从这里可以看出Plan计划的Job任务结构，整个任务会分为3个Job 执行，
第一个Job 将由Stage-1 构成;
第二个Job处理由Stage-0 构成，Stage-0 的处理必须依赖Stage-1 阶段的结果;
第三个Job处理由Stage-2 构成，Stage-2 的处理必须依赖Stage-0 阶段的结果。

下面分别解释 Stage-1 和 Stage-0，执行SQL可以分成两步：
(1)SELECT t1.bar, t1.foo, t2.foo FROM pokes t1 JOIN invites t2 ON (t1.bar = t2.bar);
(2)insert overwrite TABLE lpx;
    Stage: Stage-1对应一次完整的 Map Reduce任务，包括：Map Operator Tree和Reduce Operator Tree两步操作,Map Operator Tree对应Map任务，Reduce Operator Tree对应Reduce任务。
        从Map Operator Tree阶段可以看出进行了两个并列的操作t1和t2，分别SELECT t1.bar, t1.foo FROM t1;和 SELECT t2.foo FROM t2;而且两个Map任务分别产生了Reduce阶段的输入[Reduce Output Operator]。
     从Reduce Operator Tree分析可以看到如下信息，条件连接Map 的输出以及通过预定义的输出格式生成符合default.lpx的存储格式的数据存储到HDFS 中。在我们创建lpx表
的时候，没有指定该表的存储格式，默认会以Text 为存储格式，输入输出会以TextInputFormat 与TextOutputFormat 进行读写：
              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
                  name: default.lpx
input format 的值对应org.apache.hadoop.mapred.TextInputFormat，
这是因为在开始的Map 阶段产生的临时输出文件是以TextOutputFormat 格式保存的，自然Reduce 的读取是由TextInputFormat 格式处理读入数据。这些是由Hadoop 的MapReduce 处
理细节来控制，而Hive 只需要指定处理格式即可。
    Serde 值为org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe 类，这时这个对象的保存的值为_col0, _col1, _col2，也就是我们预期要查询的t1.bar, t1.foo, t2.foo，这个值具体的应该为_col0+表lpx 设置的列分割符+_col1+表lpx 设置的列分割符+_col2。outputformat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat 可以知道output 的处理是使用该类来处理的。
    Stage-0 对应上面提到的第二步操作。这时stage-1 产生的临时处理文件举例如tmp,需要经过stage-0 阶段操作处理到lpx 表中。Move Operator 代表了这并不是一个
MapReduce 任务，只需要调用MoveTask 的处理就行，在处理之前会去检查输入文件是否符合lpx表的存储格式。

ref: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Explain