背景及目的

MaxCompute的 分区表 是指在创建表时指定分区空间,即指定表内的某几个字段作为分区列。使用数据时,如果指定了需要访问的分区名称,则只会读取相应的分区,避免全表扫描,提高处理效率,降低费用。

分区剪裁是指对分区列指定过滤条件,使得SQL执行时只用读取表的部分分区数据,避免全表扫描引起的数据错误及资源浪费。但是分区失效的情况会经常发生,本文将通过示例为您介绍一些常见问题的解决方案。

问题示例

测试表test_part_cut的分区。

执行以下SQL代码。
select count(*)
from test_part_cut
where ds= bi_week_dim('20150102');
--其中为bi_week_dim自定义函数:返回格式为(年,第几周)。
--如果是正常日期,判断日期是所传入参数中年份所属周,以周四为一周的起始日期,如果碰到20140101因为属于周三所以算在2013年最后一周返回2013,52。而20150101则返回是2015,1。
--如果是类似20151231是周四又恰逢与20160101在同一周,则返回2016,1。
bi_week_dim(‘20150102’)的返回结果是2015,1,不符合表test_part_cut的分区值,通常会认为上面的SQL不会读任何分区,而实际情况却是该SQL读了表test_part_cut的所有分区,LogView如下。

从上图可见,该SQL在执行时读取了表test_part_cut的所有分区。
由上述示例可见,分区剪裁使用尽管简单,但也容易出错。因此,本文将从以下两方面进行介绍:
  • 判断SQL中分区剪裁是否生效。
  • 了解常见的导致分区剪裁失效的场景。

判断分区剪裁是否生效

通过explain命令查看SQL的执行计划,用于发现SQL中的分区剪裁是否生效。
  • 分区剪裁未生效
    explain
    select seller_id
    from xxxxx_trd_slr_ord_1d
    where ds=rand();


    由上图可见,SQL读取了表xxxxx_trd_slr_ord_1d的1344个分区,即该表的所有分区。
  • 分区剪裁生效
    explain
    select seller_id
    from xxxxx_trd_slr_ord_1d
    where ds='20150801';


    由上图可见,SQL只读取了表xxxxx_trd_slr_ord_1d的20150801的分区。

分区剪裁失效的场景分析

分区剪裁在使用自定义函数或者部分系统函数的时候会失效,在join关联时的where条件中也有可能会失效。下面针对这两种场景分别举例说明:

  • 自定义函数导致分区剪裁失效
    当分区剪裁的条件中使用了用户自定义函数,则分区剪裁会失效,即使是使用系统函数也可能会导致分区剪裁失效。所以,对于分区值的限定,如果使用了非常规函数需要用explain命令通过查看执行计划,确定分区剪裁是否已经生效。
    explain
    select ...
    from xxxxx_base2_brd_ind_cw
    where ds = concat(SPLIT_PART(bi_week_dim(' ${bdp.system.bizdate}'), ',', 1), SPLIT_PART(bi_week_dim(' ${bdp.system.bizdate}'), ',', 2))


    由上图可见,SQL因为分区剪裁使用了用户自定义的函数导致全表扫描。
    说明 UDF已支持分区裁剪,详情请参见WHERE子句过滤文中对应的说明。
  • 使用join时分区剪裁失效

    在SQL语句中,使用join进行关联时,如果分区剪裁条件放在where中,则分区剪裁会生效,如果放在on条件中,从表的分区剪裁会生效,主表则不会生效。下面针对三种join具体说明:

    • LEFT OUTER JOIN
      • 分区剪裁条件均放在on
        set odps.sql.allow.fullscan=true;
        explain
        select a.seller_id
            ,a.pay_ord_pbt_1d_001
        from xxxxx_trd_slr_ord_1d a
        left outer join
             xxxxx_seller b
        on a.seller_id=b.user_id
        and a.ds='20150801'
        and b.ds='20150801';


        由上图可见,左表进行了全表扫描,只有右表的分区裁剪有效果。
      • 分区剪裁条件均放在where
        set odps.sql.allow.fullscan=true;
        explain
        select a.seller_id
            ,a.pay_ord_pbt_1d_001
        from xxxxx_trd_slr_ord_1d a
        left outer join
            xxxxx_seller b
        on a.seller_id=b.user_id
        where a.ds='20150801'
        and b.ds='20150801';


        由上图可见,两张表的分区裁剪都有效果。
    • RIGHT OUTER JOIN

      left outer join类似,分区剪裁条件如果放在on中则只有right outer join的左表生效,如果放在where中,则两张表都会生效。

    • FULL OUTER JOIN

      分区剪裁条件只有都放在where中才会生效,放在on中则都不会生效。

影响及思考

  • 分区剪裁如果失效会影响比较大,且用户不容易发现。因此,分区剪裁失效最好在代码提交的时候发现比较合适。
  • 对于用户自定义函数不能用于分区剪裁的问题,需要再深入思考解决方法。