如何将年薪从十五万到五十万系列之从一个sql引发的hive谓词下推的全面复盘及源码分析（上）

VIP文章嘉祐-小萝卜算子

已于 2022-04-16 10:36:39 修改

阅读量2.4k

点赞数 3

分类专栏： Hive系列文章标签：大数据 hadoop spark

于 2022-03-31 15:49:25 首次发布

本文链接：https://blog.csdn.net/weixin_39282730/article/details/123871724

版权

嗨，大家好，我是小萝卜算子。（微信公众号：数据仓库践行者。感谢关注）
首先先更新一下我的VIP群里的状态
在这里插入图片描述
日常答疑

在这里插入图片描述
飞书上的视频课程也已经有条不紊的进行了。
欢迎有需要的小伙伴一起来搬砖。

下面开始今天的正题
Hive版本：hive-2.1.1
经常听到**【谓词下推】**这个词，却从来没有对它进行全面的深入的研究，直到前些天，我们的数据产品跑过来跟我讨论他写的一个sql，这个sql最终出现的结果并不是他想要的。看了具体的sql后，引发了我的一些思考，决定来挖一挖谓词下推。

1、那个引人思考的sql

原sql业务比较复杂，我们用一些简单的测试数据复现场景。
创建两张表：

create table test1(id int,openid string) PARTITIONED BY ( day string ) STORED AS ORC;
create table test2(id int,openid string)  PARTITIONED BY ( day string ) STORED AS ORC;

数据：

insert into table test1 partition (day='20190521') values(1,'apple');
insert into table test1 partition (day='20190521') values(2,'peach'); 
insert into table test2 partition (day='20190521') values(1,'apple');
insert into table test2 partition (day='20190521') values(3,'lemon');

数据产品跑过来跟我讨论的那个sql:

select 
  count(distinct case when b.openid is null then a.openid end) as n1,
    count(distinct case when a.openid is null then b.openid end) as n2 
from test2 a full join   test1 b on a.openid = b.openid  
where a.day = '20190521' and b.day = '20190521'

本意：想计算出20190521 这天，test1和test2表 openid字段各自己非交集的条数，如下图
在这里插入图片描述
理想中的结果应该是 1，1
但实际上输出结果是：0，0

为什么呢？其实也不难判断
是使用者对该sql的执行顺序理解有误（Joins occur BEFOREWHERE CLAUSES）

这里需要明白一点， join之后的where决定了最终要呈现的数据

hive full join不管会不会进行谓词下推， where后面的a.day = ‘20190521’ and b.day = ‘20190521’ 的条件一定会在full join之后过滤一遍，full join的中间结果如下：
在这里插入图片描述
在这样的结果中，计算a.day = ‘20190521’ and b.day = ‘20190521’ 条件下，a.openid为null的条数及b.openid为null的条数，肯定都是0了

谓词下推复盘

上面那条sql引发的场景，实际上跟有没有进行谓词下推关系不大，但是这样的一个sql却引发我们的思考。
这次复盘的hive版本是 hive 2.1.1，不同的版本，对谓词下推做的优化有所不同，尤其是不同大版本间，相差比较多，比如：hive1.x和hive2.x。当然，hive2.x相对于hive1.x是更智能，更优化了。

2.1 谓词

谓词下推概念中的谓词指返回bool值即true和false的函数，或是隐式转换为bool的函数，
比如：like ，is null,in,exists,=,!、= 这些返回bool值的函数

2.2 几个概念

2.2.1 Preserved Row table（保留表）

在outer join中需要返回所有数据的表叫做保留表，也就是说在left outer join中，左表需要返回所有数据，则左表是保留表；right outer join中右表则是保留表；在full outer join中左表和右表都要返回所有数据，则左右表都是保留表。

2.2.2 Null Supplying table（空表）

在outer join中对于没有匹配到的行需要用null来填充的表称为Null Supplying table。在left outer join中，左表的数据全返回，对于左表在右表中无法匹配的数据的相应列用null表示，则此时右表是Null Supplying table，相应的如果是right outer join的话，左表是Null Supplying table。但是在full outer join中左表和右表都是Null Supplying table，因为左表和右表都会用null来填充无法匹配的数据。

2.2.3 During Join predicate（Join中的谓词）

Join中的谓词是指 Join On语句中的谓词。如：R1 join R2 on R1.x = 5 the predicate R1.x = 5是Join中的谓词

2.2.4 After Join predicate（Join之后的谓词）

where语句中的谓词称之为Join之后的谓词

2.3 谓词下推

谓词下推的基本思想：将过滤表达式尽可能移动至靠近数据源的位置，以使真正执行时能直接跳过无关的数据。

在hive官网上给出了outer join【left outer join、right out join、full outer join】的谓下推规则：
在这里插入图片描述
翻译一下如下：

Join(只包括left join ,right join,full join)中的谓词如果是保留表的，则不会下推
Join(只包括left join ,right join,full join)之后的谓词如果是Null Supplying tables的，则不会下推

这种规则在hive2.x版本以后，就不是很准确了，hive2.x对CBO做了优化，CBO也对谓词下推规则产生了一些影响。

因此在hive2.1.1中影响谓词下推规则的，主要有两方面

Hive逻辑执行计划层面的优化
CBO
下面写例子来加深理解：

为了方便我们再往test1里插入几条数据

insert into table test1 partition (day='20190521') values(1,'pear');
insert into table test1 partition (day='20190521') values(2,'pear');
insert into table test1 partition (day='20190521') values(3,'banana');

在执行例子之前，避免map join影响到各个例子的执行计划，先关闭map join：

set hive.auto.convert.join=false;
set hive.cbo.enable=false;

case1 inner join 中的谓词

由于CBO对谓词下推也做了优化，我们在分析时，主要分析两部分，CBO功能关闭和开启，控制CBO的参数：hive.cbo.enable 在hive2版本中默认都是开启的 true

select t1.*,t2.* from test1 t1 join test2 t2 on t1.id=t2.id and t1.openid='pear' 
and t2.openid='apple';

hive> set hive.cbo.enable=false;
hive> explain select t1.*,t2.* from test1 t1 join test2 t2 on t1.id=t2.id and t1.openid='pear' and t2.openid='apple';
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: t1
            Statistics: Num rows: 5 Data size: 465 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: ((openid = 'pear') and id is not null) (type: boolean)
              Statistics: Num rows: 2 Data size: 186 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: id (type: int)
                sort order: +
                Map-reduce partition columns: id (type: int)
                Statistics: Num rows: 2 Data size: 186 Basic stats: COMPLETE Column stats: NONE
                value expressions: day (type: string)
          TableScan
            alias: t2
            Statistics: Num rows: 2 Data size: 186 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: ((openid = 'apple') and id is not null) (type: boolean)
              Statistics: Num rows: 1 Data size: 93 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: id (type: int)
                sort order: +
                Map-reduce partition columns: id (type: int)
                Statistics: Num rows: 1 Data size: 93 Basic stats: COMPLETE Column stats: NONE
                value expressions: day (type: string)
      Reduce Operator Tree:
        Join Operator
          condition map:
               Inner Join 0 to 1
          keys:
            0 id (type: int)
            1 id (type: int)
          outputColumnNames: _col0, _col2, _col6, _col8
          Statistics: Num rows: 2 Data size: 204 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col0 (type: int), 'pear' (type: string), _col2 (type: string), _col6 (type: int), 'apple' (type: string), _col8 (type: string)
            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
            Statistics: Num rows: 2 Data size: 204 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              Statistics: Num rows: 2 Data size: 204 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

Time taken: 0.682 seconds, Fetched: 59 row(s)

hive> set hive.cbo.enable=true;
hive> explain select t1.*,t2.* from test1 t1 join test2 t2 on t1.id=t2.id and t1.openid='pear' and t2.openid='apple';
OK
STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1

最低0.47元/天解锁文章

嘉祐-小萝卜算子

关注

3
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
如何将年薪从十五万到五十万系列之从一个sql引发的hive谓词下推的全面复盘及源码分析（上）

嗨，大家好，我是小萝卜算子。（微信公众号：数据仓库践行者。感谢关注）下面开始今天的正题Hive版本：hive-2.1.1经常听到**【谓词下推】**这个词，却从来没有对它进行全面的深入的研究，直到前些天，我们的数据产品跑过来跟我讨论他写的一个sql，这个sql最终出现的结果并不是他想要的。看了具体的sql后，引发了我的一些思考，决定来挖一挖谓词下推。
复制链接

扫一扫