浅谈一个新人的大数据之路-HiveQL&Spark-SQL中谓词下推

最新推荐文章于 2024-01-03 06:30:00 发布

置顶 Cold丶kl

最新推荐文章于 2024-01-03 06:30:00 发布

阅读量2.6w

点赞数 2

分类专栏：大数据之禅文章标签：大数据谓词下推

本文链接：https://blog.csdn.net/weixin_42792621/article/details/102649428

版权

大数据之禅专栏收录该内容

9 篇文章 1 订阅

订阅专栏

CCCCCold丶大数据之禅

什么是谓词下推？

在传统关系型数据库中，优化关系 SQL 查询的一项基本技术是，将外层查询块的 WHERE 子句中的谓词移入所包含的较低层查询块（例如视图），从而能够提早进行数据过滤以及有可能更好地利用索引。

这在分区数据库环境中甚至更为重要，其原因在于，提早进行过滤有可能减少必须在数据库分区之间传递的数据量。

此优化技术在 SQL 中被称为谓词下推(Predicate pushdown) 。
基本策略是，始终将过滤表达式尽可能移至靠近数据源的位置。
所谓谓词(predicate)，英文定义是这样的：A predicate is a function that returns bool (or something that can be implicitly converted to bool），也就是返回值是true或者false的函数，使用过scala或者spark的同学都知道有个filter方法，这个高阶函数传入的参数就是一个返回true或者false的函数。

但是如果是在sql语言中，没有方法，只有表达式。where后边的表达式起的作用正是过滤的作用，而这部分语句被sql层解析处理后，在数据库内部正是以谓词的形式呈现的。

对于大数据领域来说，谓词下推即：**不影响结果的情况下，尽量将过滤条件提前执行。

谓词下推后，过滤条件在map端执行，减少了map端的输出，降低了数据在集群上传输的量，节约了集群的资源，也提升了任务的性能。

为什么要进行谓词下推？

在大数据领域，影响程序性能主要原因并不是数据量的大小，而是数据倾斜，通过谓词下推可以使程序提前过滤部分数据，降低Join等一系列操作的数据量级，尽可能避免因数据倾斜导致程序性能问题。

谓词下推优化方式

谓词下推一般有两层含义：

一层含义是指由谁来完成数据过滤
第二层含义是指何时完成数据过滤

以上两层含义我们需要了解HiveQL及Spark-Sql的Sql语句处理逻辑，大致可以把不同计算引擎SQL查询处理流程做如下的划分：
SQL执行过程

词法解析类似于文本分词；
词法分析及语义分析会检查Database及table是否存在，group操作是否结合聚合函数等一些基础语法是否正确；
在逻辑计划阶段会有很多的优化，对谓词的处理就在这个阶段完成；
对Spark-Sql来说，物理计划则是RDD的DAG图的生成过程，对于HiveQL来说，物理计划会对MapReduce进行任务的变换，生成最终的执行计划；

以上完成之后则是具体的执行了(也就是各种重量级的计算逻辑，例如join、groupby、filter以及distinct等)，这就会有各种物理操作符(RDD的Transformation)的乱入。

能够完成数据过滤的主体有两个，对于HiveQL来讲：

第一是在map阶段
第二个是数据源。

对于Spark-Sql来讲：

第一是分布式Sql层(在execute阶段)
第二个是数据源。

那么谓词下推的第一层含义就是指由Sql层的Filter操作符来完成过滤，还是由Scan操作符在扫描阶段完成过滤。

我们可以通过封装SparkSql的Data Source API完成各类数据源的查询，那么如果底层数据源无法高效完成数据的过滤，就会执行全局扫描，把每条相关的数据都交给SparkSql的Filter操作符完成过滤，虽然SparkSql使用的Code Generation技术极大的提高了数据过滤的效率，但是这个过程无法避免大量数据的磁盘读取，甚至在某些情况下会涉及网络IO(例如数据非本地化存储时)

谓词下推第二层含义，即何时完成数据过滤则一般是在指连接查询中，是先对单表数据进行过滤再和其他表连接还是在先把多表进行连接再对连接后的临时表进行过滤，

谓词下推在Join中应用

INNER JOIN

【本文演示SQL返回结果均为explain】

table1: dev.dev_tmp_test_1 a
table1
table2: dev.dev_tmp_test_2 b

table2
直接上代码

explain
select
	*
from
	dev.dev_tmp_test_1 a
inner join dev.dev_tmp_test_2 b
on
	a.id = b.id
where 
	a.name = 'a'
	and b.name = 'a'

这是一个标准的内连接查询，如果不开启谓词下推，即 hive.optimize.ppd=false，在map阶段table1与table2会全量拉取所有数据，在Reduce阶段table1与table2内连接，输出table1与table2共计4个字段，然后进行filter操作，即 a.name = ‘a’ and b.name = ‘a’
贴几行关键执行计划：

Map Operator Tree: TableScan alias: a Filter Operator predicate: id is not “NULL”
Map Operator Tree: TableScan alias: b Filter Operator predicate: id is not “NULL”
Reduce Operator Tree: Filter Operator predicate: ((_col1 = ‘a’) and (_col6 = ‘a’))

反之，开启谓词下推后的执行计划如下：

Map Operator Tree: TableScan alias: a Filter Operator predicate: (id is not “NULL” and (name = ‘a’))
Map Operator Tree: TableScan alias: b Filter Operator predicate: (id is not “NULL” and (name = ‘a’))
Reduce 没有对column进行filter

可见如果关闭谓词下推，SQL会按照顺序正常执行，在数据量特别大的情况现下，数据会先JOIN后过滤，非常影响程序性能和计算资源。

HiveQL及Spark-SQL会在不影响结果的前提下对SQL进行优化（谓词下推）

现在看看HiveQL在INNER JOIN下不会谓词下推情况：

explain
select
	*
from
	dev.dev_tmp_test_1 a
inner join dev.dev_tmp_test_2 b
on
	a.id = b.id
where 
	a.name = 'a'
	or b.name = 'a';

Reduce Operator Tree: Filter Operator predicate: ((_col1 = ‘a’) or (_col6 = ‘a’)) (type: boolean)

explain
select
	*
from
	dev.dev_tmp_test_1 a
inner join dev.dev_tmp_test_2 b
on
	a.id = b.id
where 
	a.id= 1
	or b.id = 1;

Reduce Operator Tree: Filter Operator predicate: ((_col0 = 1) or (_col5 = 1)) (type: boolean)

引擎由MR换为Spark试试，执行Spark-SQL

explain
select
	*
from
	dev.dev_tmp_test_1 a
inner join dev.dev_tmp_test_2 b
on
	a.id = b.id
where 
	a.name = 'a'
	or b.name = 'a';

BroadcastHashJoin [id#2], [id#4], Inner, BuildRight, ((name#3 = a) || (name#5 = a)) Filter isnotnull

explain
select
	*
from
	dev.dev_tmp_test_1 a
inner join dev.dev_tmp_test_2 b
on
	a.name = b.name
where 
	a.name = 'a'
	or b.name = 'a';

BroadcastHashJoin [name#3], [name#5], Inner, BuildRight, ((name#3 = a) || (name#5 = a)) Filter (isnotnull(name#3) && (name#3 = a)) Spark谓词下推

通过两种SQL在不同引擎的explain,总结如下:

JOIN外的条件（where条件中），不含OR操作符，MR&Spark都会进行谓词下推
JOIN中的条件（ON条件中），MR&Spark都会进行谓词下推
JOIN外的条件（where条件中），含OR操作符，如果过滤字段与JOIN主键一致，Spark会进行谓词下推，MR不会进行谓词下推
JOIN外的条件（where条件中），含OR操作符，过滤字段与JOIN主键不一致，Spark&MR均不会进行谓词下推

LEFT/RIGHT OUTER JOIN

HiveQL,引擎为MR

explain
select
	*
from
	dev.dev_tmp_test_1 a
left join dev.dev_tmp_test_2 b
on
	a.name = b.name
    and a.name = 'a'

Reduce Operator Tree: Left Outer Join0 to 1 filter predicates:0 {(KEY.reducesinkkey0 = ‘a’)}

explain
select
	*
from
	dev.dev_tmp_test_1 a
left join dev.dev_tmp_test_2 b
on
	a.name = b.name6
where  a.name = 'a'

Map Operator Tree: alias: a Filter Operator predicate: (name = ‘a’) (type: boolean)
Spark-SQL,引擎为Spark

explain
select
	*
from
	dev.dev_tmp_test_1 a
left join dev.dev_tmp_test_2 b
on
	a.name = b.name6
where  a.name = 'a'

BroadcastHashJoin Filter (isnotnull(name#3) && (name#3 = a))

explain
select
	*
from
	dev.dev_tmp_test_1 a
left join dev.dev_tmp_test_2 b
on
	a.name = b.name6
and a.name = 'a'

BroadcastHashJoin [name#3], [name#5], LeftOuter, BuildRight, (name#3 = a)

Spark引擎&Hive引擎一致
总结规律如下：

LEFT JOIN 过滤条件在ON中，MR&Spark过滤字段取自左表不下推，右表下推
LEFT JOIN 过滤条件在WHERE中,且为ADN关系，过滤字段为关联主键MR&Spark均会进行谓词下推
LEFT JOIN 过滤条件在WHERE中,且为ADN关系，过滤字段非关联主键取自左表统统下推，引擎为MR右表不下推,为Spark右表下推
LEFT JOIN 过滤条件在WHERE中,且为OR关系，MR&Spark均不会进行谓词下推
RIGHT JOIN 反之

如果遇到分区字段存在OR或者ADN关系，SparkSql在这里使用了一种叫做“分区裁剪”的优化手段，即把分区并不看做普通的过滤条件，而是使用了“一刀切”的方法，把不符合查询分区条件的目录直接排除在待扫描的目录之外。判断字段是否属于分区是通过元数据进行区分。

至此，在MR&Spark引擎下不同JOIN关联分析完了。可以看出，在Spark-SQL&HiveQL中对于外连接查询时的过滤条件，并不能在所有情况下都用来进行数据源的过滤，如果使用得当会极大的提升查询性能，如果使用不当，则会产生错误的查询结果，而这种错误结果又不易发觉，所以使用时要格外小心。

参考：
https://cloud.tencent.com/developer/article/1404730
https://cloud.tencent.com/developer/article/1404730

人生苦短，我用Python，CCCCCold的大数据之禅

Cold丶kl

关注

2
点赞
踩
16

收藏

觉得还不错? 一键收藏
0
评论
浅谈一个新人的大数据之路-HiveQL&Spark-SQL中谓词下推

CCCCCold丶大数据之禅什么是谓词下推？为什么要进行谓词下推？谓词下推优化方式谓词下推在Join中应用INNER JOIN什么是谓词下推？在传统关系型数据库中，优化关系 SQL 查询的一项基本技术是，将外层查询块的 WHERE 子句中的谓词移入所包含的较低层查询块（例如视图），从而能够提早进行数据过滤以及有可能更好地利用索引。这在分区数据库环境中甚至更为重要，其原因在于，提早进行过滤有可...
复制链接

扫一扫

专栏目录