Hive Explain 执行计划参数分析

九筒-

于 2022-05-17 21:48:11 发布

阅读量392

点赞数

分类专栏：大数据文章标签： hive big data hadoop

本文链接：https://blog.csdn.net/weixin_44343282/article/details/124830838

版权

大数据专栏收录该内容

18 篇文章 0 订阅

订阅专栏

执行计划code

explain
--your sql code
select
	*
from
	xxx.xxxx as x
left join
	yyy.yyyy as y
on
	x.a=y.b
group by 
	c,d
having 
	e
order by 
	f
limit 
	1000

举例

拿一个实际生产中的复杂sql为例，涉及多表join，开窗函数，子查询，多维分组聚合grouping sets的执行计划为例，可以看出：

stage之间的依赖关系
各个stage的执行计划
sql语句的执行顺序，from(TableScan)→join on(Reduce Output Operator)→where(Filter Operator)→select(Select Operator)→group by(Group By Operator)→having→order by→limit
hive sql 转换为map reduce的过程，及其中间的key-value值

Explain
STAGE DEPENDENCIES: # 各个stage之间的依赖性
  Stage-1 is a root stage
  Stage-2 depends on stages: Stage-1, Stage-3, Stage-5
  Stage-3 is a root stage
  Stage-4 is a root stage
  Stage-5 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-2

STAGE PLANS: # 各个stage的执行计划
  Stage: Stage-1 # 阶段一执行计划
    Map Reduce # Map Reduce中的内容即为抽象语法树AST
      Map Operator Tree: # MAP端的执行计划树
          TableScan # map端第一个操作肯定是加载表，所以就是 TableScan 表扫描操作
            alias: t_ads_indicator_wxvideoaccountevaluate_detailfeature_d_i # 表名称
            Statistics: Num rows: 1486973 Data size: 32713406 Basic stats: COMPLETE Column stats: NONE # 表统计信息，包含表中数据条数，数据大小等
            Filter Operator # 过滤操作
              predicate: ((operator is not null and (operator <> '')) and (operator <> 'sys')) (type: boolean) # 过滤条件
              Statistics: Num rows: 743487 Data size: 16356714 Basic stats: COMPLETE Column stats: NONE
              Select Operator # 选取操作
                # expressions：需要的字段名称及字段类型
                expressions: day (type: bigint), hour (type: bigint), half_hour (type: bigint), operator (type: string), scene_id (type: bigint), scene_layer (type: bigint), action_type (type: bigint), action_result (type: bigint), evaluate_id (type: string)
                outputColumnNames: day, hour, half_hour, operator, scene_id, scene_layer, action_type, action_result, evaluate_id # 输出的列名称
                Statistics: Num rows: 743487 Data size: 16356714 Basic stats: COMPLETE Column stats: NONE
                Group By Operator # 分组聚合操作
                  # aggregations: 显示聚合函数信息
                  aggregations: count(DISTINCT CASE WHEN (((action_type = 3) and ((action_result = 0) or (action_result = 1)))) THEN (evaluate_id) ELSE (null) END), count(DISTINCT CASE WHEN (((action_type = 4) and (action_result = 0))) THEN (evaluate_id) ELSE (null) END), count(DISTINCT CASE WHEN (((action_type = 3) and (action_result = 0))) THEN (evaluate_id) ELSE (null) END), count(DISTINCT CASE WHEN (((action_type = 3) and (action_result = 1))) THEN (evaluate_id) ELSE (null) END)
                  # keys: 分组的字段，如果没有分组，则没有此字段
                  keys: day (type: bigint), hour (type: bigint), half_hour (type: bigint), operator (type: string), scene_id (type: bigint), scene_layer (type: bigint), '0' (type: string), CASE WHEN (((action_type = 3) and ((action_result = 0) or (action_result = 1)))) THEN (evaluate_id) ELSE (null) END (type: string), CASE WHEN (((action_type = 4) and (action_result = 0))) THEN (evaluate_id) ELSE (null) END (type: string), CASE WHEN (((action_type = 3) and (action_result = 0))) THEN (evaluate_id) ELSE (null) END (type: string), CASE WHEN (((action_type = 3) and (action_result = 1))) THEN (evaluate_id) ELSE (null) END (type: string)
                  mode: hash # 聚合模式，值有 hash：随机聚合，就是hash partition；partial：局部聚合；final：最终聚合
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14 # 聚合之后输出列名
                  Statistics: Num rows: 2230461 Data size: 49070142 Basic stats: COMPLETE Column stats: NONE
                  Reduce Output Operator # 输出到reduce操作
                    # key expressions: 依据什么进行排序，即用于排序的列，即map阶段的输出字段（临时字段），也就是reduce的key
                    key expressions: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col3 (type: string), _col4 (type: bigint), _col5 (type: bigint), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string)
                    sort order: +++++++++++  # 表示键表达式中用于排序的列数。每个“ +”代表一列以升序排序，每个“ -”代表一列以降序排序。
                    # Map-reduce partition columns: 保证具有相同此列的值的行被分到同一个reduce中去，即Map阶段输出到Reduce阶段的分区列
                    Map-reduce partition columns: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col3 (type: string), _col4 (type: bigint), _col5 (type: bigint), _col6 (type: string)
                    Statistics: Num rows: 2230461 Data size: 49070142 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree: # Reduce端的执行计划树
        Group By Operator
          aggregations: count(DISTINCT KEY._col7:0._col0), count(DISTINCT KEY._col7:1._col0), count(DISTINCT KEY._col7:2._col0), count(DISTINCT KEY._col7:3._col0)
          keys: KEY._col0 (type: bigint), KEY._col1 (type: bigint), KEY._col2 (type: bigint), KEY._col3 (type: string), KEY._col4 (type: bigint), KEY._col5 (type: bigint), KEY._col6 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col7, _col8, _col9, _col10
          Statistics: Num rows: 1115230 Data size: 24535060 Basic stats: COMPLETE Column stats: NONE
          pruneGroupingSetId: true
          Select Operator
            expressions: _col0 (type: bigint), COALESCE(_col1,-10000) (type: bigint), COALESCE(_col2,-10000) (type: bigint), _col4 (type: bigint), _col5 (type: bigint), _col3 (type: string), _col7 (type: bigint), _col8 (type: bigint), _col9 (type: bigint), _col10 (type: bigint)
            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9
            Statistics: Num rows: 1115230 Data size: 24535060 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-2
    Map Reduce
      Map Operator Tree:
          TableScan
            Reduce Output Operator
              key expressions: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col3 (type: bigint), _col4 (type: bigint), _col5 (type: string)
              sort order: ++++++
              Map-reduce partition columns: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col3 (type: bigint), _col4 (type: bigint), _col5 (type: string)
              Statistics: Num rows: 1115230 Data size: 24535060 Basic stats: COMPLETE Column stats: NONE
              value expressions: _col6 (type: bigint), _col7 (type: bigint), _col8 (type: bigint), _col9 (type: bigint)
          TableScan
            Reduce Output Operator
              key expressions: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col3 (type: bigint), _col4 (type: bigint), _col5 (type: string)
              sort order: ++++++
              Map-reduce partition columns: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col3 (type: bigint), _col4 (type: bigint), _col5 (type: string)
              Statistics: Num rows: 278808 Data size: 6133774 Basic stats: COMPLETE Column stats: NONE
              value expressions: _col6 (type: bigint)
          TableScan
            Reduce Output Operator
              key expressions: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col3 (type: bigint), _col4 (type: bigint), _col5 (type: string)
              sort order: ++++++
              Map-reduce partition columns: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col3 (type: bigint), _col4 (type: bigint), _col5 (type: string)
              Statistics: Num rows: 557614 Data size: 12267508 Basic stats: COMPLETE Column stats: NONE
              value expressions: _col6 (type: bigint)
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Outer Join0 to 1
               Left Outer Join0 to 2
          keys:
            0 _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col3 (type: bigint), _col4 (type: bigint), _col5 (type: string)
            1 _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col3 (type: bigint), _col4 (type: bigint), _col5 (type: string)
            2 _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col3 (type: bigint), _col4 (type: bigint), _col5 (type: string)
          outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col16, _col23
          Statistics: Num rows: 2453506 Data size: 53977133 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col5 (type: string), COALESCE(_col16,0) (type: bigint), _col6 (type: bigint), _col7 (type: bigint), _col8 (type: bigint), _col9 (type: bigint), COALESCE((_col9 / _col6),0) (type: double), COALESCE(_col23,0) (type: bigint), _col3 (type: bigint), _col4 (type: bigint)
            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12
            Statistics: Num rows: 2453506 Data size: 53977133 Basic stats: COMPLETE Column stats: NONE
            Limit
              Number of rows: 1000000
              Statistics: Num rows: 1000000 Data size: 22000000 Basic stats: COMPLETE Column stats: NONE
              File Output Operator
                compressed: true
                Statistics: Num rows: 1000000 Data size: 22000000 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-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: t_ads_indicator_wxvideoaccountevaluate_detailfeature_d_i
            Statistics: Num rows: 1486973 Data size: 32713406 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: ((((action_type = 2) and (action_result = 0)) and receiptor is not null) and (receiptor <> '')) (type: boolean)
              Statistics: Num rows: 185872 Data size: 4089183 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: day (type: bigint), hour (type: bigint), half_hour (type: bigint), receiptor (type: string), scene_id (type: bigint), scene_layer (type: bigint), 2 (type: bigint), 0 (type: bigint), evaluate_id (type: string)
                outputColumnNames: day, hour, half_hour, receiptor, scene_id, scene_layer, action_type, action_result, evaluate_id
                Statistics: Num rows: 185872 Data size: 4089183 Basic stats: COMPLETE Column stats: NONE
                Group By Operator
                  aggregations: count(DISTINCT CASE WHEN (((action_type = 2) and (action_result = 0))) THEN (evaluate_id) ELSE (null) END)
                  keys: day (type: bigint), hour (type: bigint), half_hour (type: bigint), receiptor (type: string), scene_id (type: bigint), scene_layer (type: bigint), '0' (type: string), CASE WHEN (((action_type = 2) and (action_result = 0))) THEN (evaluate_id) ELSE (null) END (type: string)
                  mode: hash
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8
                  Statistics: Num rows: 557616 Data size: 12267549 Basic stats: COMPLETE Column stats: NONE
                  Reduce Output Operator
                    key expressions: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col3 (type: string), _col4 (type: bigint), _col5 (type: bigint), _col6 (type: string), _col7 (type: string)
                    sort order: ++++++++
                    Map-reduce partition columns: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col3 (type: string), _col4 (type: bigint), _col5 (type: bigint), _col6 (type: string)
                    Statistics: Num rows: 557616 Data size: 12267549 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(DISTINCT KEY._col7:0._col0)
          keys: KEY._col0 (type: bigint), KEY._col1 (type: bigint), KEY._col2 (type: bigint), KEY._col3 (type: string), KEY._col4 (type: bigint), KEY._col5 (type: bigint), KEY._col6 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col7
          Statistics: Num rows: 278808 Data size: 6133774 Basic stats: COMPLETE Column stats: NONE
          pruneGroupingSetId: true
          Select Operator
            expressions: _col0 (type: bigint), COALESCE(_col1,-10000) (type: bigint), COALESCE(_col2,-10000) (type: bigint), _col4 (type: bigint), _col5 (type: bigint), _col3 (type: string), _col7 (type: bigint)
            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6
            Statistics: Num rows: 278808 Data size: 6133774 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-4
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: t_ads_indicator_wxvideoaccountevaluate_detailfeature_d_i
            Statistics: Num rows: 1486973 Data size: 32713406 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (((((action_type = 3) and ((action_result = 0) or (action_result = 1))) and operator is not null) and (operator <> '')) and (operator <> 'sys')) (type: boolean)
              Statistics: Num rows: 371743 Data size: 8178346 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: operator (type: string), pull_time (type: bigint), evaluate_time (type: bigint)
                sort order: ++-
                Map-reduce partition columns: operator (type: string), pull_time (type: bigint)
                Statistics: Num rows: 371743 Data size: 8178346 Basic stats: COMPLETE Column stats: NONE
                value expressions: day (type: bigint), hour (type: bigint), half_hour (type: bigint), scene_id (type: bigint), scene_layer (type: bigint)
      Reduce Operator Tree:
        Select Operator
          expressions: KEY.reducesinkkey1 (type: bigint), KEY.reducesinkkey2 (type: bigint), KEY.reducesinkkey0 (type: string), VALUE._col12 (type: bigint), VALUE._col13 (type: bigint), VALUE._col14 (type: bigint), VALUE._col17 (type: bigint), VALUE._col18 (type: bigint)
          outputColumnNames: _col10, _col12, _col14, _col15, _col16, _col17, _col20, _col21
          Statistics: Num rows: 371743 Data size: 8178346 Basic stats: COMPLETE Column stats: NONE
          PTF Operator
            Function definitions:
                Input definition
                  input alias: ptf_0
                  output shape: _col10: bigint, _col12: bigint, _col14: string, _col15: bigint, _col16: bigint, _col17: bigint, _col20: bigint, _col21: bigint
                  type: WINDOWING
                Windowing table definition
                  input alias: ptf_1
                  name: windowingtablefunction
                  order by: _col12(DESC)
                  partition by: _col14, _col10
                  raw input shape:
                  window functions:
                      window function definition
                        alias: row_number_window_0
                        name: row_number
                        window function: GenericUDAFRowNumberEvaluator
                        window frame: PRECEDING(MAX)~FOLLOWING(MAX)
                        isPivotResult: true
            Statistics: Num rows: 371743 Data size: 8178346 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: _col15 (type: bigint), _col16 (type: bigint), _col17 (type: bigint), _col20 (type: bigint), _col21 (type: bigint), _col14 (type: string), _col12 (type: bigint), _col10 (type: bigint), row_number_window_0 (type: int)
              outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8
              Statistics: Num rows: 371743 Data size: 8178346 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                aggregations: sum(CASE WHEN ((((_col8 = 1) and (_col6 <> (- 1))) and (_col7 <> (- 1)))) THEN ((_col6 - _col7)) ELSE (null) END)
                keys: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col5 (type: string), _col3 (type: bigint), _col4 (type: bigint), '0' (type: string)
                mode: hash
                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7
                Statistics: Num rows: 1115229 Data size: 24535038 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-5
    Map Reduce
      Map Operator Tree:
          TableScan
            Reduce Output Operator
              key expressions: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col3 (type: string), _col4 (type: bigint), _col5 (type: bigint), _col6 (type: string)
              sort order: +++++++
              Map-reduce partition columns: _col0 (type: bigint), _col1 (type: bigint), _col2 (type: bigint), _col3 (type: string), _col4 (type: bigint), _col5 (type: bigint), _col6 (type: string)
              Statistics: Num rows: 1115229 Data size: 24535038 Basic stats: COMPLETE Column stats: NONE
              value expressions: _col7 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: sum(VALUE._col0)
          keys: KEY._col0 (type: bigint), KEY._col1 (type: bigint), KEY._col2 (type: bigint), KEY._col3 (type: string), KEY._col4 (type: bigint), KEY._col5 (type: bigint), KEY._col6 (type: string)
          mode: mergepartial
          outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col7
          Statistics: Num rows: 557614 Data size: 12267508 Basic stats: COMPLETE Column stats: NONE
          pruneGroupingSetId: true
          Select Operator
            expressions: _col0 (type: bigint), COALESCE(_col1,-10000) (type: bigint), COALESCE(_col2,-10000) (type: bigint), _col4 (type: bigint), _col5 (type: bigint), _col3 (type: string), _col7 (type: bigint)
            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6
            Statistics: Num rows: 557614 Data size: 12267508 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-0
    Fetch Operator # 客户端获取数据操作
      limit: 1000000 # 值为 -1 表示不限制条数，其他值为限制的条数
      Processor Tree:
        ListSink

参考

九筒-

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Hive Explain 执行计划参数分析

执行计划codeexplain--your sql codeselect *from xxx.xxxx as xleft join yyy.yyyy as yon x.a=y.bgroup by c,dhaving eorder by flimit 1000 举例拿一个实际生产中的复杂sql为例，涉及多表join，开窗函数，子查询，多维分组聚合grouping sets的执行计划为例，可以看出：stage之间的依赖关系各个stage的执行计划sql语
复制链接

扫一扫