使用TTL管理ClickHouse数据生命周期

ClickHouse中数据随着时间变迁可能需要定期移动、删除或汇总数据。这依赖数据保留需求和历史数据的SLA(服务等级协议)，可以对历史数据采用更高的压缩级别节约更多空间。举例，对于超过1个月的数据采用lz4hc压缩算法，则需要DDL语法使用TTL的RECOMPRESS表达式指定压缩算法。本文主要介绍ClickHouse数据库的TTL子句实现大数据管理任务。

自动删除过期数据

有时存储过期数据不再有意义，需要从ClickHouse数据库中删除，通常称为保留策略，在后端任务会自动删除基于TTL条件的数据。

举例，下面event表保留策略需要删除过期一个月的数据：


CREATE TABLE events
(`event` String,`time` DateTime,`value` UInt64
)
ENGINE = MergeTree
ORDER BY (event, time)
TTL time + INTERVAL 1 MONTH DELETE

增加ttl语句，使用DELETE操作，条件为time + INTERVAL 1 MONTH。即当time列值为过期一个月，对应记录将被删除。

备注：删除记录属于异步后端进程，过期记录可能在一定时间内仍存在。下面插入一组记录，包括过期记录：

INSERT INTO events VALUES('error', now() - interval 2 month, 123), ('error', now(), 123);

插入之后，可以查询到两条记录：

SELECT * FROM events┌─event─┬────────────────time─┬─value─┐
│ error │ 2022-11-24 09:34:44 │   123 │
│ error │ 2023-01-24 09:34:44 │   123 │
└───────┴─────────────────────┴───────┘

过期记录有后端merge任务在定期内执行并删除：

SELECT * FROM events
┌─event─┬────────────────time─┬─value─┐
│ error │ 2023-01-24 09:34:44 │   123 │
└───────┴─────────────────────┴───────┘

管理后端删除

后端删除默认每4小时执行，可以通过表设置选项merge_with_ttl_timeout进行控制：

CREATE TABLE events
...
TTL time + INTERVAL 1 MONTH DELETE
SETTINGS merge_with_ttl_timeout = 1200

不建议周期设置小于300秒，避免I/O负载过大影响性能。一定时间之后过期记录已被删除：

SELECT * FROM events┌─event─┬────────────────time─┬─value─┐
│ error │ 2023-01-24 09:34:44 │   123 │
└───────┴─────────────────────┴───────┘

过滤删除记录

加入仅需要删除特定类型的记录（举例，event值是error），可以给ttl子句添加where条件：

CREATE TABLE events
(`event` String,`time` DateTime,`value` UInt64
)
ENGINE = MergeTree
ORDER BY (event, time)
TTL time + INTERVAL 1 MONTH DELETE WHERE event = 'error'

现在仅event=‘event’的过期记录才被删除：

INSERT INTO events VALUES('not_error', now() - interval 2 month, 123), ('error', now(), 123)

可以查询确认not_error记录没有被删除：

SELECT * FROM events┌─event─────┬────────────────time─┬─value─┐
│ error     │ 2023-01-24 09:48:05 │   123 │
│ not_error │ 2022-11-24 09:48:05 │   123 │
└───────────┴─────────────────────┴───────┘

多个删除条件

ClickHouse支持配置多个ttl语句，可以更灵活实现数据保留策略。下面示例实现删除1个月的非错误事件，删除6个月的所有错误数据：

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'

ttl语句可以配置任意多个规则。

移动数据至历史表

可以结合物化视图和ttl语句，解决在主表中删除之前，移动过期数据至另一张表中保留历史记录。
假设想在events表中删除过期数据之前移动error事件去errors_history。首先给物化视图创建目标表，其表结构与events表一致：

CREATE TABLE errors_history (`event` String,`time` DateTime,`value` UInt64
)
ENGINE = MergeTree
ORDER BY (event, time)

注意：这里不能使用CREATE TABLE errors_history AS events语句，注意会拷贝TTL表达式，我们就要修改TTL,然后创建物化视图触发自动抽取数据值error_history表：


CREATE MATERIALIZED VIEW errors_history_mv TO errors_history AS
SELECT * FROM events WHERE event = 'error'

现在当在events表中插入数据时，error事件会自动插入至errors_history表。另外，当ttl过程从events表中删除记录时，数据已经保留至errors_history表。

使用聚集压缩历史数据

有时并不像删除数据，而是为了节约资源需减少数据粒度。举例，下面场景并不删除error事件，但不需要一个月前的每秒的明细信息，仅保留每日汇聚数据量。可以通过在TTL语句中使用GROUP BY ... SET子句实现：

CREATE TABLE events
(`event` String,`time` DateTime,`value` UInt64
)
ENGINE = MergeTree
ORDER BY (toDate(time), event)
TTL time + INTERVAL 1 MONTH GROUP BY toDate(time), event SET value = SUM(value)

假设插入下面记录：

INSERT INTO events VALUES('error', now() - interval 2 month, 123),('error', now() - interval 2 month, 321);

等后端合并执行之后，可以看到数据已被汇总：

SELECT * FROM events┌─event─┬────────────────time─┬─value─┐
│ error │ 2022-11-24 12:36:23 │   444 │
└───────┴─────────────────────┴───────┘

改变压缩算法

当不能灵活实现删除或聚集数据时，需要更宽松查询服务级别协议的历史数据。我们可以采用针对过期数据采用更高的压缩级别节约空间，举例，让ClickHouse使用LZ4HC更高级别压缩率压缩一个月前的数据，需要使用RECOMPRESS子句：


CREATE TABLE events
(`event` String,`time` DateTime,`value` UInt64
)
ENGINE = MergeTree
ORDER BY (toDate(time), event)
TTL time + INTERVAL 1 MONTH RECOMPRESS CODEC(LZ4HC(10))

注意，重新压缩数据可占用更少空间，但需要更多时间去压缩，从而影响插入性能。

列级TTL

ClickHouse也支持列级TTL,控制单列的生命周期。假设表中有debug列存储额外的debug信息，仅需要保留一周，但占用大量空间。可以通过列ttl让其一周重置为缺省值：

CREATE TABLE events
(`event` String,`time` DateTime,`value` UInt64,`debug` String TTL time + INTERVAL 1 WEEK
)
ENGINE = MergeTree
ORDER BY (event, time)

现在插入过期记录，包括debug列值：

INSERT INTO events VALUES('error', now() - interval 1 month, 45, 'a lot of details');-- ttl处理之后查询，可以看到debug已为空字符串SELECT * FROM events┌─event─┬────────────────time─┬─value─┬─debug─┐
│ error │ 2022-12-24 15:13:54 │    45 │       │
└───────┴─────────────────────┴───────┴───────┘