Apache Doris使用部分日期函数导致分区裁剪失效总结

Apache Doris使用部分日期函数导致分区裁剪失效总结
背景:

Apache Doris 是一个分布式的MPP分析型数据仓库,能够实现数据的高并发查询和亚秒级响应需求。在FE和BE两个节点中,FE节点主要功能是与客户端进行交互,元数据管理及查询计划规划等;BE则主要负责数据的存储和查询计划的执行等。

BE节点数据存储划分主要分为两级:分区和分桶。

分区(Partition):主要有日期分区和枚举List分区两种形式,是一种较粗粒度的数据裁剪。

分桶(Bucket): 是一种更细粒度的数据裁剪,建立在分区基础之上,根据指定的分桶列字段, 通过哈希函数,将分区中的数据散列在不同的Bucket中。

在实际环境中,对分区表而言,通常会采用日期分区的方式,根据预期的分区字段,将一个周,一个月或者一年的数据存放在一个分区中,这样,在进行数据查询时,我们可以根据分区字段来过滤数据,减少数据的扫描量,避免大查询给集群带来较大的计算压力,从而影响集群的整体稳定性。

通过分区和分桶策略在很大程度上能够提高查询性能。按照date或者datetime字段类型分区的数据表,可能我们在查询时需要对分区字段的数据进行一些特殊处理。

比如:如果分区字段类型为datetime类型,但我们想查询整月的数据,那我们可能更倾向于将datetime先转换为date类型,然后再根据create_date = ”2023-06“这种形式来查询6月份的数据。

对此,Doris官网为我们提供了很多日期函数,让我们能够更好的操作日期字段。但在实际应用过程中,也发现了一些问题:

比如:如果我们根据create_date字段按月分区创建一个分区表,当我们在查询create_date = ”2023-06“的数据时,我们期望的是只扫描2023-06分区的数据,而不是全部的数据,但实际发现,在SQL中使用部分日期函数后发现会导致分区裁剪失效,导致全表扫描数据,这完全不符合我们的预期效果。下面会列举一些导致分区裁剪失效的日期函数,大家在使用过程中,要谨慎使用。

结论:

根据测试,会导致分区裁剪异常的情况主要有以下几种:

  • 如果符号两边同时对分区字段使用函数,分区不会进行裁剪:

     DATE_FORMAT(order_create_time,'%Y-%m') =  DATE_FORMAT(curdate(),'%Y-%m')
    
  • 如果WHERE条件中对分区字段使用DATE_FORMAT,不管是单个使用还是组合使用DATE_FORMAT,分区都不会进行裁剪:

     order_create_date = DATE_FORMAT(curdate(),'%Y-%m')
     order_create_time = DATE_SUB(DATE_FORMAT(curdate(),'%Y-%m-%d') , INTERVAL 1 day )
    
  • 如果WHERE条件中对分区字段使用DAY_FLOOR,DAY_CEIL,MONTH_FLOOR,MONTH_CEIL,YEAR_FLOOR,YEAR_CEIL这些函数,数据也不会分区裁剪:

    DAY_FLOOR(CURDATE());DAY_CEIL(CURDATE())
    MONTH_FLOOR(CURDATE());MONTH_CEIL(CURDATE());
    YEAR_FLOOR(CURDATE());YEAR_CEIL(CURDATE())
    
  • 如果WHERE条件中对分区字段使用部分组合函数,可能也会导致分区裁剪失效,导致全表扫描:

     DATE_SUB(curdate(), INTERVAL DAYOFMONTH(curdate()) -1 DAY)
     DATE_SUB(CURDATE(), dayofyear(CURDATE())-1)
    
  • 如果WHERE条件中对分区字段使用IF条件判断,目前只能使用DATE_FORMAT ,其他函数替代函数暂时没办法解决:

    DATE_FORMAT(if(hour(now()) < 8 ,DATE_SUB(curdate(),1),curdate()),'yyyy-MM-dd')
    
  • 在使用CURDATE()CURRENT_DATE() 这个两个函数时,发现同样的SQL语句,使用CURDATE()可以走分区裁剪,使用CURRENT_DATE()这个函数不会走分区裁剪,这块要注意:

    str_to_date(CURDATE(),'%Y-%m-%d')  --走分区裁剪
    str_to_date(CURRENT_DATE(),'%Y-%m-%d')  --不走分区裁剪
    
  • 使用Doris 1.2版本中最新的日期函数LAST_DAY()也不会进行分区裁剪。

解决方案:

我们可以通过一些其他函数,来代替以上异常函数,避免全表扫描:

1.格式化日期:
date -> datetimeSELECT str_to_date(CURDATE(), ‘%Y-%m-%d %H:%i:%s’)2023-06-25 00:00:00
datetime ->dateSELECT str_to_date(‘2023-06-25 00:00:00’, ‘%Y-%m-%d’)2023-06-25
2.计算今天

返回datetime类型

函数返回结果
今天开始select str_to_date(CURDATE(), ‘%Y-%m-%d %H:%i:%s’)2023-06-25 00:00:00
今天结束select str_to_date(date_add(CURDATE(),1), ‘%Y-%m-%d %H:%i:%s’)2023-06-26 00:00:00
今天结束select str_to_date(days_add(CURDATE(),1), ‘%Y-%m-%d %H:%i:%s’)2023-06-26 00:00:00
3.计算本月

返回date类型。

函数返回结果
本月开始SELECT date_sub(CURDATE(), day(CURDATE()) -1)2023-06-01
本月结束SELECT months_add(date_sub(CURDATE(), day(CURDATE()) -1) , 1)2023-07-01
本月结束SELECT months_add(days_sub(CURDATE(), day(CURDATE()) -1) , 1)2023-07-01
4.计算上个月及下个月
函数返回结果
上个月1号SELECT months_sub(days_sub(CURDATE(), day(CURDATE()) -1) , 1)2023-05-01
下个月今天SELECT months_add(CURDATE() , 1 )2023-07-25
5.计算今年
函数返回结果
今年年初SELECT months_sub(days_sub(CURDATE(), day(CURDATE()) -1), month(CURDATE())-1)2023-01-01
今年年初select date_sub(days_sub(CURDATE(), day(CURDATE()) -1), INTERVAL MONTH(CURDATE()) -1 month)2023-01-01
今年年底select years_add(months_sub(days_sub(CURDATE(), day(CURDATE()) -1), month(CURDATE())-1), 1)2024-01-01
如何判断一个SQL是否进行分区裁剪

举例:

表创建语句:

CREATE TABLE `ods_test_sub_partition_demo` (
  `create_time` datetime NULL COMMENT "创建时间",
  `execution_id` varchar(96) NULL COMMENT "执行单id",
  `user_id` bigint(20) NULL COMMENT "用户id",
  `_is_delete` varchar(5) NULL DEFAULT "1" COMMENT "删除标识:1:未删除,0:已删除",
  `updateStamp` datetime NULL COMMENT "落库时间"
) ENGINE=OLAP
UNIQUE KEY(`create_time`, `execution_id`, `user_id`)
COMMENT "测试分区裁剪表"
PARTITION BY RANGE(`create_time`)
(PARTITION P_000000 VALUES [('0000-01-01 00:00:00'), ('2023-01-01 00:00:00')),
PARTITION P_202301 VALUES [('2023-01-01 00:00:00'), ('2023-02-01 00:00:00')),
PARTITION P_202302 VALUES [('2023-02-01 00:00:00'), ('2023-03-01 00:00:00')),
PARTITION P_202303 VALUES [('2023-03-01 00:00:00'), ('2023-04-01 00:00:00')),
PARTITION P_202304 VALUES [('2023-04-01 00:00:00'), ('2023-05-01 00:00:00')),
PARTITION P_202305 VALUES [('2023-05-01 00:00:00'), ('2023-06-01 00:00:00')),
PARTITION P_202306 VALUES [('2023-06-01 00:00:00'), ('2023-07-01 00:00:00')),
PARTITION P_202307 VALUES [('2023-07-01 00:00:00'), ('2023-08-01 00:00:00')),
PARTITION P_202308 VALUES [('2023-08-01 00:00:00'), ('2023-09-01 00:00:00')))
DISTRIBUTED BY HASH(`execution_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "2",
"dynamic_partition.prefix" = "P_",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "1",
"dynamic_partition.create_history_partition" = "false",
"dynamic_partition.history_partition_num" = "-1",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.start_day_of_month" = "1",
"in_memory" = "false",
"storage_format" = "V2"
);

查询本月的数据:

explain SELECT * from test.ods_test_sub_partition_demo where create_time > month_floor(curdate()) and create_time < month_ceil(curdate())

从SQL中可以看到,只是查询了create_time为当前月的所有数据,该表按月分区,理论上只会扫描P_202306这一个分区,但实际情况我们通过explain看到,这个表一共9个分区,该SQL扫描了7个分区,扫描基数25010954,全表扫描所有数据。

0:OlapScanNode
     TABLE: ods_test_sub_partition_demo
     PREAGGREGATION: OFF. Reason: No AggregateInfo
     PREDICATES: `create_time` > month_floor('2023-06-25'), `create_time` < month_ceil('2023-06-25'), `default_cluster:test.ods_test_sub_partition_demo`.`__DORIS_DELETE_SIGN__` = 0
     partitions=7/9
     rollup: ods_test_sub_partition_demo
     tabletRatio=7/7
     tabletList=21968907,21968903,21968899,21968895,21968891,21968887,21968883,21968947,21968943,21968939 ...
     cardinality=25010954
     avgRowSize=75.90796
     numNodes=10

这个执行计划明显是不正确的,我们可以修改一下SQL语句:

explain SELECT * from  test.ods_test_sub_partition_demo where create_time > str_to_date(date_sub(CURDATE(), day(CURDATE()) -1), '%Y-%m-%d %H:%i:%s') and create_time < str_to_date(month_ceil(curdate()), '%Y-%m-%d %H:%i:%s')

我们再看一下执行计划:

从下面这个执行计划可以看到,SQL总共只扫描了1个分区,扫描基数只有539336,扫描数据量为原来的1/50,符合我们的预期。

0:OlapScanNode
     TABLE: ods_test_sub_partition_demo
     PREAGGREGATION: OFF. Reason: No AggregateInfo
     PREDICATES: `create_time` > '2023-06-01 00:00:00', `create_time` < str_to_date(month_ceil('2023-06-25'), '%Y-%m-%d %H:%i:%s'), `default_cluster:test.ods_test_sub_partition_demo`.`__DORIS_DELETE_SIGN__` = 0
     partitions=1/9
     rollup: ods_test_sub_partition_demo
     tabletRatio=1/1
     tabletList=21968919
     cardinality=539336
     avgRowSize=71.08554
     numNodes=3
总结:

虽然Doris内置了很多日期函数,但在分区字段上使用部分日期函数会导致分区裁剪异常,导致谓词无法下推,全量扫描数据。我们在实际应用中,合理使用日期函数会提高查询效率和系统稳定性,本文只是总结了部分不生效的情况,但其实很多函数还是可以正常,比如date_sub,date_add,days_sub,days_add,months_sub,months_add等,大家在使用时,可以选择正确的函数即可。

  • 1
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值