今天我们继续HiveSQL执行计划的学习。在学习之前我们先复习之前：

1. 大数据Hive-SQL执行计划解读（explain）

回顾完之前的文章，我们继续解读SQL执行计划的扩展和依赖信息，即：

• explain extended
• explain dependency

查看执行计划的扩展信息

explain extended顾名思义就是对explain的扩展，打印的信息会比explain更加丰富，包含三个部分：

第一部分：抽象语法树（ABSTRACT SYNTAX TREE，简称AST）：是SQL转换成MapReduce或其它计算引擎的任务中的一个过程。在Hive 3.0版本，AST会从explain extended的移除，要查看AST，需要使用explain ast的命令。

第二部分：作业的依赖关系图，即STAGE DEPENDENCIES，其内容和explain所展现的一样，不做重复介绍。

第三部分：每个作业的详细信息，即STAGE PLANS，在打印每个作业详细信息时，explain extend会打印出更多的信息，除了explain打印出的内容，还包括每个表的HDFS读取路径，每个Hive表的表配置信息等。

查看SQL数据输入依赖的信息

explain dependency用于描述一段SQL需要的数据来源，输出是一个json格式的数据，里面包含两个部分：

第一部分：input_partitions：描述一段SQL依赖的数据来源表分区，里面存储的是分区名的列表，格式如下：

{"partitionName":"库名@表名 @分区列=分区列的值"}

如果整段SQL包含的所有表都是非分区表，则显示为空。

第二部分：input_tables：描述一段SQL依赖的数据来源表，里面存储的是Hive表名的列表，格式如下：

{"tablename":"库名@表名 ","tabletype":表的类型（外部表/内部表）"}

下面看两个案例，案例6.3是查询非分区普通表SQL的explain dependency，案例6.4时查询分区表SQL的explain dependency

案例6.3 使用explain dependency查看SQL查询非分区普通

--业务逻辑同案例6.1
explain dependency
select s_age,count(1) num from student_tb_orc
where s_age<30 and s_name like '%红%'
group by s_age;
输出结果如下：
{"input_partitions":[],"input_tables":[{"tablename":"default@student_tb_orc","tabletype":"MANAGED_TABLE"}]}

案例6.4 使用explain denpendency查看SQL查询分区表

--业务逻辑同案例6.1
explain dependency
select s_age,count(1) num from student_orc_partition
where s_age<30 and s_name like '%红%'
group by s_age
输出结果如下：
{"input_partitions":[{"partitionName":"default@student_orc_partition@part=0"},
                     {"partitionName":"default@student_orc_partition@part=1"},
                     {"partitionName":"default@student_orc_partition@part=2"},
                     {"partitionName":"default@student_orc_partition@part=3"},
                     {"partitionName":"default@student_orc_partition@part=4"},
                     {"partitionName":"default@student_orc_partition@part=5"},
                     {"partitionName":"default@student_orc_partition@part=6"},
                     {"partitionName":"default@student_orc_partition@part=7"},
                     {"partitionName":"default@student_orc_partition@part=8"},
                     {"partitionName":"default@student_orc_partition@part=9"}],
  "input_tables":[{"tablename":"default@student_orc_partition","tabletype":"MANAGED_TABLE"}]

explain dependency使用场景：

场景一：快速排错。快速排除因为读取不到相应分区的数据导致任务数据输出异常的情况。例如，在一个以天分区任务中，上游任务因为生产过程不可控因素导致出现异常或者出现空跑，导致下游任务引发异常。通过这种方式，可以快速查看SQL读取的分区，是否出现异常。

场景二：帮助理清表的输入，帮助理解程序的运行，特别是有助于理解有多重数据查询，多表连接的依赖输入。

下面通过两个案例来看explain dependency的实际运用。在案例6.5中，我们会通过explain dependency识别看似等价的代码实际不等价。对于刚接触SQL的人，很容易将“select * from a inner join b on a.no=b.no and a.f>1 and a.f<3”这种写法等价于“select * from a inner join b on a.no=b.no where a.f>1 and a.f<3”这种写法，我们可以通过案例6.5，来查看下它们的区别。

案例6.5 通过explain dependency识别看似等价的代码

--代码片段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

下面分别是上面两代码片段explain dependency输出结果：

--代码片段1的explain dependency打印结果：
{"input_partitions":
[{"partitionName":"default@student_orc_partition@part=0"},
{"partitionName":"default@student_orc_partition@part=1"},
{"partitionName":"default@student_orc_partition@part=2"},
{"partitionName":"default@student_orc_partition_only@part=1"},
{"partitionName":"default@student_orc_partition_only@part=2"}],
"input_tables":
[{"tablename":"default@student_orc_partition","tabletype":"MANAGED_TABLE"},
{"tablename":"default@student_orc_partition_only","tabletype":"MANAGED_TABLE"}]}
--代码片段2的explain dependency打印结果：
{"input_partitions":
[{"partitionName":"default@student_orc_partition@part=1"},
{"partitionName" : "default@student_orc_partition@part=2"},
{"partitionName" :"default@student_orc_partition_only@part=1"},
{"partitionName":"default@student_orc_partition_only@part=2"}],
"input_tables":
[{"tablename":"default@student_orc_partition","tabletype":"MANAGED_TABLE"},
{"tablename":"default@student_orc_partition_only","tabletype":"MANAGED_TABLE"}]}

通过上面我们可以其实上述的两SQL并不等价，在内连接（inner join）中的连接条件中加入非等值的过滤条件，并没法将内连接的左右两表按照过滤条件进行过滤，内连接在执行时候会多读取part=0的分区数据。

explain dependency可以帮助纠正错误的认知。大部分SQL的学习者，在学习外连接，包括左外连接、右外连接、全外连接，如果不细抠概念，很容易将下面案例6.6中两种情况进行搞混。

案例6.6 使用explain dependency识别SQL读取数据范围差别

---代码片段1
explain dependency
select a.s_no
from student_orc_partition  a
left outer join student_orc_partition_only b
on a.s_no=b.s_no and a.part=b.part and b.part>=1 and b.part<=2;
---代码片段2
explain dependency
select a.s_no
from student_orc_partition  a
left outer 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可以像代码片段1一样进行数据过滤，通过查看explain dependency输出结果，可以知道不是如此。

下面是代码片段1和2的explain dependency输出结果：

--代码片段1的打印结果
{"input_partitions":
[{"partitionName": "default@student_orc_partition@part=0"},
{"partitionName":"default@student_orc_partition@part=1"},
…中间省略7个分区
{"partitionName":"default@student_orc_partition@part=9"},
{"partitionName":"default@student_orc_partition_only@part=1"},
{"partitionName":"default@student_orc_partition_only@part=2"}],
"input_tables":
[{"tablename":"default@student_orc_partition","tabletype":"MANAGED_TABLE"},
{"tablename":"default@student_orc_partition_only","tabletype":"MANAGED_TABLE"}]}
--代码片段2的打印结果
{"input_partitions":
[{"partitionName":"default@student_orc_partition@part=0"},
{"partitionName":"default@student_orc_partition@part=1"},
…中间省略7个分区
{"partitionName":"default@student_orc_partition@part=9"},
{"partitionName":"default@student_orc_partition_only@part=0"},
{"partitionName":"default@student_orc_partition_only@part=1"},
..中间省略7个分区
{"partitionName":"default@student_orc_partition_only@part=9"}],
"input_tables":
[{"tablename":"default@student_orc_partition","tabletype":"MANAGED_TABLE"},
{"tablename":"default@student_orc_partition_only","tabletype":"MANAGED_TABLE"}]}

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

扩展：如果要使用外连接并需要对左右两表进行条件过滤，最好的方式就是将过滤条件放到表的就近处，即如果已经知道表数据过滤筛选条件，那么在使用该表前，就用该过滤条件进行过滤，一些SQL内置优化器的也会做上述的优化，但是我们还是建议按上面介绍的方式写出来。例如将代码片段2改写成如下案例6.7形式，即在使用表数据之前尽可能过滤掉不需要的数据：

案例6.7 尽早过滤掉不需要的数据

select a.s_no
from （
  select s_no,part
  from student_orc_partition
  --在自查询内部进行过滤
  where part>=1 and part<=2
）  a
left outer join student_orc_partition_only b
on a.s_no=b.s_no and a.part=b.part ;

#Hive# #大数据# #SQL#