谓词下推
最近公司做审计,任务有点重。然后发现spark sql跑出来的结果和实际情况有出入,于是经过多方打探和测试,今天做个了结。
所谓谓词下推,也就是返回值是true和false的函数,做开发经常用到filter函数,这个高阶函数传入的参数就是一个返回true或false的函数。在SQL中,没有方法,只有表达式,where后边的表达式起的作用就是过滤的作用,而这部分语句被SQL引擎解析处理后,在数据库内部正式以谓词的形式呈现。
SparkSQL首先会对输入的SQL语句进行一系列的分析,包括词法分析以及语义分析(例如判断table是否存在,group by等规则),之后是执行计划的生成,包括逻辑计划和物理计划,其中在逻辑计划阶段会有很多的优化,而物理计划则是RDD的DAG的生成;这两步完成之后则是具体的load数据和计算了。
那么在加载数据时,很容易想到当然是加载的数据量越小,越节约资源。这就涉及到了操作符,scan操作符直接面向扫描,filter操作符面向完成扫描后数据的过滤。具体不深入研究。
join关联
SQL关联以及连接条件是重点,join看似简单,日常用时都不会过脑,但是稍不注意就被谓词下推坑到。
举例:
select left.val, right.val
from left_table left
left join right_table right
on left.id=right.id and left.id>1
where right.id>2
on left.id=right.id and left.id>1语句被称为join中条件,where right.id>2 被称为join后条件。上边提到的谓词下推能否在两类关联条件中使用,在SparkSQL中则有特定的规则,以左连接查询为例,规则如下:
左表 | 右表 | |
join中条件 | 不下推 | 下推 |
join后条件 | 下推 | 不下推 |
分析
假设两张表,如下:
id | value |
1 | one |
2 | two |
id | value |
1 | one |
2 | two |
1. 左表join后条件下推
select left.id left.val, right.val
from left_table left
left join right_table right
on left.table = right.table
where left.id > 1
经过 left.id>1 过滤后,左表变为:
id | value |
2 | two |
然后和右表进行左连接,左表id为2的行,在右表中能找到id为2的行,结果变成:
left.id | left.value | right.value |
2 | two | two |
可见,谓词下推过滤了 50%的数据量,虽然只有两条。根本是sparksql将其语句解析为:
select left.id, left.val, right.val
from (
select id, val from left_table where id>1
) tmp
left join right_table right
on tmp.id = right.id
2. 左表join中条件不下推
还是原来的SQL。谓词下推是为了提高效率,如果不下推也应该得到正确的结果,下面来看下,不下推计算的正确结果。join过程如下:
第一步:左表id为1的行在右表中能找到相等的id,但是左表的id为1,是不满足第二个join条件的(left.id > 1),所以左表这一条相当于没有和右表关联上,所以左表的value值保留,而右表的value值为null。
第二步:左表id为2的行在右表中能找到,而且左表id为2的行id大于1,两个条件都满足,所以关联成功,左表和右表的val值都保留。
结果为:
left.id | left.val | right.val |
1 | one | null |
2 | two | two |
那么如果把left.id > 1的条件下推到表里,结果是什么呢?
首先左表会过滤到id为1的行,此时再和右表关联,左表id为2的行关联成功,那么结果是:
left.id | left.val | right.val |
2 | two | two |
这个结果肯定是不对的,所以也就论证了上面那个观点,左表join中条件是不能下推到数据过滤的。
3. 右表join中条件下推
select left.id, left.val, right.val
from left_table left
left join right_table right
on left.id = right.id
and right.id > 1
把 right.id > 1这个右表join中条件下推,过滤后结果只剩id=2的行。
然后左右表关联:
第一步:左表为1的行在右表中没有,那么左表的值保留,右表的值为null。
第二步:id为2的行关联成功。结果为:
left.id | left.val | right.val |
1 | one | null |
2 | two | two |
上面是join中下推的结果,那如果不下推呢?
结果也是一样的,所以下不下推都一样,肯定是选择谓词下推喽,毕竟提前过滤一半数据。
3. 右表join后条件不下推
select left.id, left.val
from left_table left
left join right_table right
on left.id=right.id
where right.id > 1
首先来看,join后条件不下推,流程如下:
第一步:左表为1的行在右表关联上,但是不满足where的过滤条件,所以丢掉这一行
第二步:左表为2的行关联成功。结果为:
left.id | left.value | right.value |
2 | two | two |
这条结果也是正确的,那么如果下推呢?
第一步:使用 right.id > 1过滤右表,过滤后右表只剩一行id为2的行
第二步:左表为1的行在过滤后的右表中没有,此时左表值保留,右表值为null。
第三步:左表为2的行关联成功。结果为:
left.id | left.value | right.value |
1 | one | null |
2 | two | two |
结果是错误的。
四条规则全分析完,在SparkSql中对于外连接查询时的过滤条件,并不能在所有情况下都用来进行数据源过滤,如果使用得当能够提升性能,使用不当,还会产生错误的查询结果。