在数据仓库开发指标时，需要回溯历史数据，对下面的表改为非动态表并添加更早时间的分区：

-- 改为非动态分区
ALTER TABLE test SET ("dynamic_partition.enable" = "false")
-- 手动添加更早的时间分区
ALTER TABLE test
ADD PARTITION p20230912 VALUES [("2023-09-12"), ("2023-09-13"));
  --改为动态分区
ALTER TABLE test SET ("dynamic_partition.enable" = "true")

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL COMMENT '主键',  
  `dt` date NULL COMMENT '创建时间'
) ENGINE=OLAP
DUPLICATE KEY(`id`)
COMMENT 'olap'
PARTITION BY RANGE(`dt`)
PARTITION p20231010 VALUES [('2023-10-10'), ('2023-10-11')),
PARTITION p20231011 VALUES [('2023-10-11'), ('2023-10-12')),
PARTITION p20231012 VALUES [('2023-10-12'), ('2023-10-13')),
PARTITION p20231013 VALUES [('2023-10-13'), ('2023-10-14')),
PARTITION p20231014 VALUES [('2023-10-14'), ('2023-10-15')),
PARTITION p20231015 VALUES [('2023-10-15'), ('2023-10-16')),
PARTITION p20231016 VALUES [('2023-10-16'), ('2023-10-17')),
PARTITION p20231017 VALUES [('2023-10-17'), ('2023-10-18')),
PARTITION p20231018 VALUES [('2023-10-18'), ('2023-10-19')),
PARTITION p20231019 VALUES [('2023-10-19'), ('2023-10-20')),
PARTITION p20231020 VALUES [('2023-10-20'), ('2023-10-21')))
DISTRIBUTED BY HASH(`id`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-7",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 1",
"dynamic_partition.buckets" = "10",
"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.storage_policy" = "",
"in_memory" = "false",
"storage_format" = "V2",
"disable_auto_compaction" = "false"
);

然而奇怪的事情发生了，创建的p20230912 不见了，后来考虑到可能是动态分区搞的鬼，是由于没有正确动态分区的属性 。

看到表结构的”dynamic_partition.start” = “-7”,这表示
动态分区的起始范围。表示从今天开始向前7天。 而
p20230912 远远超过这个范围，后来把这个值调大后，果然不会凭空消失了。

ALTER TABLE test SET
(    "dynamic_partition.start" = "-75"
);

关注公众号“大模型全栈程序员”回复“大数据面试”获取800页左右大数据面试宝典 ，回复“大数据”获取多本大数据电子书