1.对表进行ttl设置
ALTER TABLE my_dm.tb_test MODIFY TTL parseDateTimeBestEffort(toString(pt_day)) + INTERVAL 1 MONTH DELETE;
查看该表的ddl语句
CREATE TABLE my_dm.tb_test
(
`target_user` String,
`first_active_date` String,
`last_active_date` String,
`pt_day` Int32 COMMENT '分区日期',
`group_id` Int32,
`experiment_id` Int64 DEFAULT 0
)
ENGINE = MergeTree
PARTITION BY (pt_day, group_id)
ORDER BY target_user
TTL parseDateTimeBestEffort(toString(pt_day)) + toIntervalMonth(1)
SETTINGS index_granularity = 8192
执行该语句不会对存量数据进行触发的，需要执行如下命令手工触发
2.执行MATERIALIZE TTL
ALTER TABLE tb_test MATERIALIZE TTL;
执行该语句后系统在后台进行数据的merge,比较消耗性能
可以通过如下语句查询执行情况
SELECT partition,name,delete_ttl_info_min, delete_ttl_info_max
FROM system.parts
WHERE
`database` = 'mydm'
AND `table` = 'tb_test'
