【Juicy-Bigdata】Hive SQL执行计划使用

余生杂货铺2023-02-242024-07-16

Hive SQL的执行计划能了解SQL程序在转换成相应计算引擎的执行逻辑，从而有针对性的调优。

explain的用法

explain介绍

explain query

-- 举例
explain select sum(score) from student_score;

一个HIVE查询会被转换为一个或多个stage组成的序列（有向无环图DAG)，可以是MR stage，负责元素存储的stage，负责文件系统的操作的stage

查询结果包含两个大的部分

stage dependencies，各个stage的依赖关系

stage plan：各个stage的执行计划

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: student_score
            Statistics: Num rows: 2 Data size: 960 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: score (type: int)
              outputColumnNames: score
              Statistics: Num rows: 2 Data size: 960 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                aggregations: sum(score)
                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)
      Execution mode: vectorized
      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-1 里有Map Reduce，又分为了

Map Operator Tree：Map端的执行计划树
Reduce Operator Tree：Reduce端的执行计划树

包含一些操作

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

explain的使用场景

案例一：join会过滤null的值吗

1	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: N
ONE
...
HashTable Sink Operator
           keys:
             0 _col0 (type: int)
             1 _col0 (type: int)

group by 分组排序会排序吗

1	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 stat
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 st

Group By Operator里有keys：id，sort order：+，说明是按id正序进行排序的

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;

通过执行explain，会发现它们的执行计划一样

explain dependency用法

explain dependency描述SQL需要的数据来源，输出包含：

input_partitions:描述一段 SQL 依赖的数据来源表分区，里面存储的是分区名的列表，如果整段 SQL 包含的所有表都是非分区表，则显示为空。
input_tables:描述一段 SQL 依赖的数据来源表，里面存储的是 Hive 表名的列表

使用场景

快速排除，排除因为读不到相应分区导致的输出异常
理清表的输入，帮助理解程序的运行

案例一识别看似等价的代码

--代码 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;

会发现，代码1仍然用了分区0的数据，代码2则没有

案例二识别SQL读取数据范围的差别

对左外连接在连接条件中加入非等值过滤的条件，如果过滤条件是作用于右表(b 表)有起到过滤的效果，则右表只要扫描两个分区即可，但是左表(a 表)会进行全表扫描。如果过滤条件是针对左表，则完全没有起到过滤的作用，那么两个表将进行全表扫描。这时的情况就如同全外连接一样都需要对两个数据进行全表扫描。

explain authorization

通过 explain authorization 可以知道当前 SQL 访问的数据来源(INPUTS) 和数据输出(OUTPUTS)，以及当前 Hive 的访问用户 (CURRENT_USER)和操作 (OPERATION)

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}