MaxCompute最佳实践：分区剪裁合理性评估

背景及目的


MaxCompute的 分区表是指在创建表时指定分区空间，即指定表内的某几个字段作为分区列。在使用数据时，如果指定了需要访问的分区名称，则只会读取相应的分区，避免全表扫描，提高处理效率，降低费用。
分区剪裁是指对分区列指定过滤条件，使得SQL执行时只用读取表的部分分区数据，避免全表扫描引起的数据错误及资源浪费。看起来非常简单，但是实际情况却常常出人意料，经常会出现分区失效的情况，先看看下面这个例子。
测试表test_part_cut的分区

执行以下SQL代码：

1. select count(*)
2. from test_part_cut
3. where ds= bi_week_dim('20150102');
5. --其中为bi_week_dim自定义函数:返回格式为 （年,第几周）：
6. --如果是正常日期，判断日期是所传入参数中年份所属周，以周四为一周的起始日期，如果碰到20140101因为属于周三所以算在2013年最后一周返回2013,52。而20150101则返回是2015,1。
7. --如果是类似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中的分区剪裁是否生效。  

• 
  分区剪裁没生效的效果
  1.   explain
  2.   select  seller_id
  3.   from    xxxxx_trd_slr_ord_1d
  4.   where   ds=rand();
  
  
    看上图的红色框的内容，表示读取了表xxxxx_trd_slr_ord_1d的1344个分区，即该表的所有分区。
• 
  分区剪裁生效的效果
  1. explain
  2. select  seller_id
  3. from    xxxxx_trd_slr_ord_1d
  4. where   ds='20150801';
  
  
    看上图红色框的内容，表示只读取了表xxxxx_trd_slr_ord_1d的20150801的分区。

分区剪裁失效的场景分析


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

自定义函数导致分区剪裁失效


当分区剪裁的条件中使用了用户自定义函数，则分区剪裁会失效，即使是使用系统函数也可能会导致分区剪裁失效。所以，对于分区值的限定，如果使用了非常规函数需要用explain命令通过查看执行计划确定分区剪裁是否已经生效。

1. explain
2. select  ...
3. from    xxxxx_base2_brd_ind_cw
4. where   ds = concat(SPLIT_PART(bi_week_dim(' ${bdp.system.bizdate}'), ',', 1), SPLIT_PART(bi_week_dim(' ${bdp.system.bizdate}'), ',', 2))

可以看出上面的SQL因为分区剪裁使用了用户自定义的函数导致全表扫描。

Join使用时分区剪裁失效


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

• 分区剪裁条件均放在on中
  1. explain
  2. select  a.seller_id
  3.      ,a.pay_ord_pbt_1d_001
  4. from    xxxxx_trd_slr_ord_1d a
  5. left outer join
  6.       xxxxx_seller b
  7. on      a.seller_id=b.user_id
  8. and     a.ds='20150801'
  9. and     b.ds='20150801';
  

  从上图可以看出两张表都走了分区剪裁。

• 
  分区剪裁条件均放在where中
  1. explain
  2. select  a.seller_id
  3.      ,a.pay_ord_pbt_1d_001
  4. from    xxxxx_trd_slr_ord_1d a
  5. left outer join
  6.       xxxxx_seller b
  7. on      a.seller_id=b.user_id
  8. where   a.ds='20150801'
  9. and     b.ds='20150801';
  
  
    从上图可以看出主表的分区剪裁有效果，而从表则是进行了全表扫描。

(2) Right Outer Join: 
与Left Outer Join类似，分区剪裁条件如果放在on中则两张表都会生效，如果放在where中，则只有Right Outer Join右侧的主表会生效，不再举例。
(3) Full Outer Join:
分区剪裁条件只有都放在on中才会生效，放在where中则都不会生效。

影响及思考


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