ClickHouse_ClickHouse数据生命周期管理

发表于 | 分类于

ClickHouse_ClickHouse数据生命周期管理

1.背景

ClickHouse作为数据库存储容量有限，数据随着时间变迁可能需要定期移动或删除数据，而且通常这类直接面向业务产品的olap引擎数据库中数据有较强的业务性质，需要随业务时间过期，而不应该随数据写入或更新时间过期，否则刷数就会造成数据生命周期紊乱。ClickHouse数据库本身提供了TTL子句帮助用户实现较为灵活的数据生命周期管理。

2.ClickHouse中常见的TTL使用场景

2.1根据业务字段自动删除过期数据

在建表时设置TTL

如下案例表示按照time字段日期往前推算，超过一个月的数据自动删除，注意在TTL子句中使用的过期业务字段一定要是DateTime类型，其他类型也要通过日期函数转化为DateTime类型：

1
2
3
4
5
6
7
8
9
CREATE TABLE events
(
    `event` String,
    `time` DateTime,
    `value` UInt64
)
ENGINE = MergeTree
ORDER BY (event, time)
TTL time + INTERVAL 1 MONTH DELETE

修改表的TTL

如下案例表示按照dt字段往前推算，超过765天的数据自动删除

1
2
3
4
5
ALTER TABLE sz.app_jdr_traffic_sz_all_chan_mvp_i_d_d on
cluster LF02_CK_Pub_202 MODIFY TTL toDate(dt) + toIntervalDay(765)
DELETE
	,
	toDate(dt) + toIntervalDay(180) TO VOLUME 'cold';

2.2 带过滤条件的自动删除记录

TTL自己可以添加Where子句，指定满足特定条件的数据才会自动过期删除，如下示例仅满足业务字段event=’error’条件的过期记录才被自动过期删除：

1
2
3
4
5
6
7
8
9
CREATE TABLE events
(
    `event` String,
    `time` DateTime,
    `value` UInt64
)
ENGINE = MergeTree
ORDER BY (event, time)
TTL time + INTERVAL 1 MONTH DELETE WHERE event = 'error'

那么使用该功能就可以实现保存指定绝对时间了，如下：

1
TTL time + INTERVAL 1 MONTH DELETE WHERE time = '2023-06-18'

使用该功能再配合同比日期推数任务可以实现保存同比时间。同比时间相当于一个滑动窗口，每新一天的数据之前已经被过期自动删除了，所以需要重新推数，而设置过滤条件的TTL语句可以保障刚推进来的同比数据不会被直接删除，TTL语句如下：

1
TTL time + INTERVAL 1 MONTH DELETE WHERE time <= subtractYears(today(), 1) and time >= subtractYears(today(), 1) - INTERVAL 30 DAY

也可以通过新增一个时间类型业务字段来实现保存同比，方法很多，总能实现。

2.3 多个删除条件

ClickHouse支持配置多个TTL语句，不同TTL语句之间是或的关系，数据只有满足其中一个TTL语句就会被自动过期删除。如下面示例表示删除1个月以前的非错误事件，删除6个月以前的所有错误数据：

1
2
3
4
5
6
7
8
9
10
CREATE TABLE events
(
    `event` String,
    `time` DateTime,
    `value` UInt64
)
ENGINE = MergeTree
ORDER BY (event, time)
TTL time + INTERVAL 1 MONTH DELETE WHERE event != 'error',
    time + INTERVAL 6 MONTH DELETE WHERE event = 'error'

2.4 根据业务字段自动移动磁盘

自动移动磁盘

如下示例表示180天以前的数据存储到冷盘中：

1
2
3
4
5
ALTER TABLE sz.app_jdr_traffic_sz_all_chan_mvp_i_d_d on
cluster LF02_CK_Pub_202 MODIFY TTL toDate(dt) + toIntervalDay(765)
DELETE
	,
	toDate(dt) + toIntervalDay(180) TO VOLUME 'cold';

手动移动磁盘

也可以手动执行MOVE PARTITION命令移动分区的存储磁盘：

1
ALTER TABLE sz.app_jdr_traffic_sz_all_chan_mvp_i_d_d ON CLUSTER LF02_CK_Pub_202 MOVE PARTITION '2023-09-18' TO VOLUME 'hot';

查看分区数据的磁盘分布情况

查询system.parts_all表可以获取每个分区PARTITION的存储磁盘名：

1
2
3
4
5
6
7
SELECT * FROM system.parts_all
WHERE database = 'sz' and table = 'app_jdr_traffic_sz_lead_ord_cart_mvp_i_d_d'

--查看具体分区分布
SELECT partition,disk_name FROM system.parts_all
WHERE database = 'sz' and table = 'app_jdr_traffic_sz_lead_ord_cart_mvp_i_d_d' 
group by partition,disk_name ORDER BY partition desc;

2.5 列级TTL

ClickHouse也支持列级TTL，控制单个数据列的生命周期。例如表中有debug列存储debug相关信息，但此类信息占用大量空间，仅需要保留一周即可，如下示例通过列TTL使该列数据在一周后自动重置为缺省值：

1
2
3
4
5
6
7
8
9
CREATE TABLE events
(
    `event` String,
    `time` DateTime,
    `value` UInt64,
    `debug` String TTL time + INTERVAL 1 WEEK
)
ENGINE = MergeTree
ORDER BY (event, time)

当使用列TTL自动充值缺省值时，如果该列定义了Default值，则过期后列数据会填充为Default值，否则填充为NULL。

参考文献

https://blog.csdn.net/neweastsun/article/details/130783048