什么是Hive SQL执行计划
Hive SQL执行计划描绘了SQL实际执行的整体轮廓，即SQL转化为对应计算引擎的执行逻辑；毫无疑问，这一块对于Hive SQL的优化是非常重要的。
Hive SQL早期是基于规则的方式生成执行计划，在Hive 0.14及之后，集成了Apache Calcite，使得Hive能够基于成本代价来生成执行计划。
Hive目前提供的是预估的执行计划，而非真实的执行计划（SQL实际执行完后才能获得的计划）。
查看Hive SQl执行计划
- explain：查看基本信息
- explain extended：查看扩展信息
- explain dependency：查看SQL数据输入依赖的信息
- explain authorization：查看SQL操作相关权限的信息
- explain vectorization：查看向量化的描述信息
# 看这个前请先理解sql语句
STAGE DEPENDENCIES: # 作业依赖关系
Stage-1 is a root stage # 根stage
Stage-0 depends on stages: Stage-1 # stage-0依赖stage-1
STAGE PLANS:
Stage: Stage-1 # Stage-1详细任务
Map Reduce # 表示当前引擎使用的是 MapReduce
Map Operator Tree: # Map阶段操作信息
TableScan # 对下面alias声明的结果集进行 表扫描操作
alias: student # alias声明student表
Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE # 对当前阶段的统计信息，如当前处理的行和数据量（都是预估值）
Filter Operator # 表示在Tablescan的结果集上进行过滤
predicate: ((s_age < 30) and (s_name like '%red%')) (type: boolean) # 过滤条件
Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
Select Operator # 表示在过滤后的结果集上进行投影
expressions: s_age (type: int) # 需要投影的列
outputColumnNames: s_age # 输出的列名
Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
Group By Operator # 在投影后结果集上进行分组聚合
aggregations: count() # 分组聚合使用的算法
keys: s_age (type: int) # 分组的列
mode: hash # 采用hash
outputColumnNames: _col0, _col1 # 输出的列名
Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
Reduce Output Operator # Map端聚合
key expressions: _col0 (type: int) # Map端输出的key
sort order: + # +表示正序,-表示逆序
Map-reduce partition columns: _col0 (type: int) # 分区字段
Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
value expressions: _col1 (type: bigint) # Map端输出的value
Execution mode: vectorized
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0) # 对Map value的第一个值进行聚合算法
keys: KEY._col0 (type: int) # key是Map Key的第一个值
mode: mergepartial
outputColumnNames: _col0, _col1 # 输出列名
Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
File Output Operator # 对上面的结果集进行文件输出
compressed: false # 不压缩
Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 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 DEPENDENCIES（作业的依赖关系图）、STAGE PLANS（每个作业的详细信息）。以及标注的详细解释。
执行计划归类
只有Map阶段的类型
-
select-from-where型：简单的SQL执行计划，不包含列操作、条件过滤、UDF、聚合、连接等操作的SQL。
由于不需要经过聚合，所以只有Map阶段操作，如果文件大小控制合适的话，可以完全发挥任务本地化执行的优点，也就是不需要跨节点，非常高效！
hive> explain > select s_age,s_name > from student > where s_age=10; OK STAGE DEPENDENCIES: Stage-0 is a root stage STAGE PLANS: Stage: Stage-0 Fetch Operator limit: -1 Processor Tree: TableScan alias: student Statistics: ... Filter Operator predicate: (s_age = 10) (type: boolean) Statistics: ... Select Operator expressions: 10 (type: int), s_name (type: int) outputColumnNames: _col0, _col1 Statistics: ... ListSink -
select-func(col)-from-where-func(col) / select-operation-from-where-operation型：只带普通函数（除UDTF、UDAF、窗口函数）
同样只有Map阶段，非常高效！
hive> explain > select case when s_age>20 then 'over 20' when s_age=20 then 'equal 20' else 'other' end age > from student > where s_name is not null; OK STAGE DEPENDENCIES: Stage-0 is a root stage STAGE PLANS: Stage: Stage-0 Fetch Operator limit: -1 Processor Tree: TableScan alias: student Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE Filter Operator predicate: s_name is not null (type: boolean) Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE Select Operator expressions: CASE WHEN ((s_age > 20)) THEN ('over 20') WHEN ((s_age = 20)) THEN ('equal 20') ELSE ('other') END (type: string) outputColumnNames: _col0 Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE ListSink
Map+Reduce类型
-
select-aggr_func-from-where-groupby类型：带聚合函数的SQL。这类SQL可以分为如下几类：在Reduce阶段聚合的SQL执行计划、在Map和Reduce都有聚合的SQL计划、高级分组聚合的执行计划。
hive中可以通过配置hive.map.aggr来设定是否开启Combine。
高级分组聚合指的是聚合时使用：grouping sets、cube、rollup（使用高级分组聚合需要确保Map端reduce开启）。使用高级分组聚合可以将union需要多次的作业塞到一个作业中，可以减少多作业在磁盘和网络IO中的消耗，是一种优化。但是需要注意，这种聚合会造成数据极速膨胀；如果基表的数据量很大，容易导致Map或者Reduce任务因为硬件资源不足而崩溃，Hive中可以使用hive.new.job.grouping.set.cardinality配置这个问题。
-
带窗口函数的SQL执行计划
hive> explain > select s_name,row_number() over(partition by s_age order by s_score) rk > from student; OK 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: student Statistics: ... Reduce Output Operator key expressions: s_age (type: int), s_score (type: int) sort order: ++ Map-reduce partition columns: s_age (type: int) Statistics: ... value expressions: s_name (type: string) Execution mode: vectorized Reduce Operator Tree: Select Operator expressions: VALUE._col0 (type: string), KEY.reducesinkkey0 (type: int), KEY.reducesinkkey1 (type: int) outputColumnNames: _col0, _col1, _col2 Statistics: ... PTF Operator # 窗口函数分析操作 Function definitions: Input definition input alias: ptf_0 output shape: _col0: string, _col1: int, _col2: int type: WINDOWING Windowing table definition input alias: ptf_1 name: windowingtablefunction order by: _col2 ASC NULLS FIRST # 窗口函数排序列 partition by: _col1 # 窗口函数分区列 raw input shape: window functions: window function definition alias: row_number_window_0 name: row_number # 窗口函数的方法 window function: GenericUDAFRowNumberEvaluator window frame: ROWS PRECEDING(MAX)~FOLLOWING(MAX) # 表示当前窗口函数上下边界 isPivotResult: true Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE Select Operator expressions: _col0 (type: string), row_number_window_0 (type: int) outputColumnNames: _col0, _col1 Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE File Output Operator compressed: false Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL 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
表连接的SQL计划
Hive表连接类型可以分为以下六种：
- inner join
- full outer join
- left outer join
- right outer join
- left semi join：返回左表中与右表的匹配记录
- cross join：返回两表连接字段的笛卡尔积
以下举例inner join
hive> explain
> select a.s_no
> from a a inner join b b on a.s_no=b.s_no;
OK
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 # 扫描表a
alias: a
Statistics: ...
Filter Operator
predicate: s_no is not null (type: boolean) # inner join 内置过滤
Statistics: ...
Select Operator
expressions: s_no (type: int)
outputColumnNames: _col0
Statistics: ...
Reduce Output Operator
key expressions: _col0 (type: int)
sort order: +
Map-reduce partition columns: _col0 (type: int)
Statistics: ...
TableScan # 扫描表b
alias: b
Statistics: ...
Filter Operator
predicate: s_no is not null (type: boolean)
Statistics: ...
Select Operator
expressions: s_no (type: int)
outputColumnNames: _col0
Statistics: ...
Reduce Output Operator
key expressions: _col0 (type: int)
sort order: +
Map-reduce partition columns: _col0 (type: int)
Statistics: ...
Reduce Operator Tree:
Join Operator # 表连接操作
condition map:
Inner Join 0 to 1 # inner join 0 1 ;0 1指代的信息在下面
keys:
0 _col0 (type: int) # 0表示map阶段一个表的数据集 _col0表示连接字段
1 _col0 (type: int) # 1表示map阶段另一个表的数据集 _col0表示连接字段
outputColumnNames: _col0
Statistics: ...
File Output Operator
compressed: false
Statistics: ...
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