随着时间的推移，clickhouse 中的数据逐步增长。为了查询、存储效率的提升我们可能需要计划性删除、移动或聚合历史数据。针对此类数据生命周期管理，clickhouse 提供了简单且强大的工具——TTL，该工具作用于 DDL 子句中。这篇文章将探索 TTL 以及如何使用它来解决多种数据管理任务。
TTL 只能应用在 MergeTree 系列引擎中
一、删除数据
在一些特殊的场景中，有时存储过期的数据是没有意义的，因此需要定期执行删除操作。而 clickhouse 只需要在 DDL 中配置 TTL 就可以在后台自动完成。同时对于删除操作又可以细分为删除整行或只删除指标列
1.1 删除整行
1. 普通删除
假设有一张event表，同时我们期望自动删除所有超过一个月的记录
create table events
(
event String,
time DateTime,
value UInt64
)
engine = MergeTree
order by (event, time)
ttl time + interval 1 month;
上面的 DDL 中添加了一个 TTL 策略，意味着当time字段的时间超过当前时间一个月后这条记录将会被删除。因为delete是 TTL 的默认行为，该关键字可以省略但为了和第三节改变压缩方式行为做区分，TTL 策略也可以写成下面的形式
ttl time + interval 1 month delete
现在尝试插入几条数据，同时包含一条已经过期的数据
insert into events values ('error', now() - interval 2 month, 123), ('error', now(), 123);
请立刻查询events，幸运的话你可以看到两条数据
select * from events;
┌─event─┬────────────────time─┬─value─┐
│ error │ 2024-03-15 15:46:27 │ 123 │
│ error │ 2024-05-15 15:46:27 │ 123 │
└───────┴─────────────────────┴───────┘
等待一段时间后再次查询events可以看到2024-03-15 15:46:27的记录会被删除掉。如果你在第一次查询时观察到两条数据，那么这个"一段时间"往往是很长的，那条本该被删除的数据会"恶心"你很久，这点需要解释一下：
- TTL 策略执行是一个后台异步任务，在某一次 merge 时进行
- TTL 策略执行受
merge_with_ttl_timeout参数影响，默认值为 14400，单位：秒，也就是说后台删除操作默认每四个小时执行一次 - 官方建议
merge_with_ttl_timeout参数不能低于 300，频繁的删除操作会产生 IO 影响集群效率
为了更好的看到效果，可以将参数调整为 60，配置如下：
create table events
(
event String,
time DateTime,
value UInt64
)
engine = MergeTree
order by (event, time)
ttl time + interval 1 month
settings merge_with_ttl_timeout = 60;
💡Tips: 若第一次插入时过期数据被快速删除可以尝试
truncate table event后再执行一下插入语句，过期数据可以保留一个完整的 TTL 周期
2. 带有条件的删除
假设我们只需要删除特定记录的过期数据，TTL 也是支持where子句的，例如：只需要删除过期一个月的error事件记录
create table events_filter
(
event String,
time DateTime,
value UInt64
)
engine = MergeTree
order by (event, time)
ttl time + interval 1 month where event = 'error'
settings merge_with_ttl_timeout = 60;
插入两条数据验证策略
insert into events_filter
values ('no_error', now() - interval 2 month, 123),
('error', now(), 123);
可以观察到，即使过了一个 TTL 周期no_error记录依然不会被删除，因此该记录已经不符合删除策略了
select * from events_filter;
┌─event────┬────────────────time─┬─value─┐
│ error │ 2024-05-15 16:11:03 │ 123 │
│ no_error │ 2024-03-15 16:11:03 │ 123 │
└──────────┴─────────────────────┴───────┘
3. 多个条件的删除
clickhouse 允许执行多个 TTL 语句，使我们能够更加灵活和具体地确定删除内容和删除时间。假设我们要删除 1 个月后的所有非错误事件以及 6 个月后的所有错误
create table events_multiple
(
event String,
time DateTime,
value UInt64
)
engine = MergeTree
order by (event, time)
ttl time + interval 1 month where event != 'error',
time + interval 6 month where event = 'error'
settings merge_with_ttl_timeout = 60;
当然你可以配置任意多个 TTL 策略使用,隔开即可
1.2 删除指标列
该场景较为抽象，因为这个策略并不是从通用的业务场景中抽象出来的，而是 clickhouse 允许 TTL 作用在字段上，当字段满足 TTL 策略是会被置为默认值
create table events_for_column
(
event String,
time DateTime,
value UInt64,
col1 Int8 ttl time + interval 1 month,
col2 Float64 ttl time + interval 1 month,
col3 String ttl time + interval 1 month,
col4 bool ttl time + interval 1 month
)
engine = MergeTree
order by (event, time)
settings merge_with_ttl_timeout = 60;
常见数据类型的默认值:
- 数值型：0
- 字符型：空字符串(不是 null)
- 布尔型：false
可以插入一条记录进行验证
insert into events_for_column values ('error', now() - interval 2 month, 123, 10, 3.14, 'for ttl', true);
验证如下
select t.*, col3 is null, col3 == ''
from events_for_column t;
┌─event─┬────────────────time─┬─value─┬─col1─┬─col2─┬─col3─┬─col4──┬─isNull(col3)─┬─equals(col3, '')─┐
│ error │ 2024-03-15 16:28:09 │ 123 │ 0 │ 0 │ │ false │ 0 │ 1 │
└───────┴─────────────────────┴───────┴──────┴──────┴──────┴───────┴──────────────┴──────────────────┘
二、移动数据
2.1 到表
即为归档(archive)，该操作在 TP 数据库中经常使用。将历史数据定时写入到_archive表从而提高业务表的查询效率。在 clickhouse 中可以使用 TTL + materialized view，我们知道物化视图可以异步处理数据而 TTL 则是异步删除数据。因此则可以实现移动过期数据到归档表中
-- 为了不受上面操作影响，删表重建
drop table events;
create table events
(
event String,
time DateTime,
value UInt64
)
engine = MergeTree
order by (event, time)
ttl time + interval 1 month
settings merge_with_ttl_timeout = 60;
-- 创建同结构的归档表
create table events_archive
(
event String,
time DateTime,
value UInt64
)
engine = MergeTree
order by (event, time);
-- 创建物化视图，执行异步归档
create materialized view m_events to events_archive
as
select *
from events;
当数据插入events表时，因为物化视图的存在，数据会异步的写入events_archive中，当 TTL 策略执行时events表中记录会被删除
2.2 到卷
这就是大名鼎鼎的冷热数据分层存储！！！通常越新的数据查询频次越高，也就是我们所说的"热数据"，而历史数据则被称为"冷数据"。但是冷数据不代表是没用的数据，在偶尔的时刻还是需要被查询使用的因此不能被删除，但是两种类型的数据存储在一起无疑会增加热数据查询的响应时间。业内通常的做法是：将热数据存储在 ssd 中，过期的冷数据逐步迁移到 hdd 中。
我们需要在 clickhouse 上做一些前期准备，准备好冷盘、热盘，为了测试可以创建两个目录当做两个盘的挂载路径（主要是电脑只挂了一个盘），需要在 clickhouse 的 config.xml 中配置
<storage_configuration>
<disks>
<ssd>
<path>/Users/wjun/tmp/data/clickhouse/ssd/</path>
</ssd>
<hdd>
<path>/Users/wjun/tmp/data/clickhouse/hdd/</path>
</hdd>
</disks>
<policies>
<moving_from_ssd_to_hdd>
<volumes>
<hot>
<disk>ssd</disk>
</hot>
<cold>
<disk>hdd</disk>
</cold>
</volumes>
</moving_from_ssd_to_hdd>
</policies>
</storage_configuration>
- storage_configuration: 固定标签，用于标识 clickhouse 的存储配置区域
- disks: 固定标签，用于标识 clickhouse 可以使用哪些磁盘
- ssd,hdd: 自定义标签，用于标识磁盘名
- path: 固定标签，用于标识磁盘的实际存储路径
- policies: 固定标签，用于标识 clickhouse 的存储策略配置区域
- moving_from_ssd_to_hdd: 自定义标签，用于标识策略名称
- volumes: 固定标签，用于标识 clickhouse 可以使用哪些卷
- hot,cold: 自定义标签，用于标识卷名
- disk: 固定标签，用于标识卷可以使用哪些磁盘
上面配置的含义：定义了两个磁盘分别叫 ssd 和 hdd，同时定义了一个名为 moving_from_ssd_to_hdd 的存储策略，该策略定义了两个卷分别叫 hot 和 cold
将上面配置写入到 config.xml 中，具体位置可以搜索默认配置文件中 storage_configuration 标签位置，保存即可无需重启 clickhouse 服务
顺利的话可以查看系统表查看配置项
select * from system.disks\G
Row 1:
──────
name: default
path: /opt/homebrew/var/lib/clickhouse/
free_space: 93673529344
total_space: 494384795648
unreserved_space: 93673529344
keep_free_space: 0
type: local
is_encrypted: 0
is_read_only: 0
is_write_once: 0
is_remote: 0
is_broken: 0
cache_path:
Row 2:
──────
name: hdd
path: /Users/wjun/tmp/data/clickhouse/hdd/
free_space: 93673529344
total_space: 494384795648
unreserved_space: 93673529344
keep_free_space: 0
type: local
is_encrypted: 0
is_read_only: 0
is_write_once: 0
is_remote: 0
is_broken: 0
cache_path:
Row 3:
──────
name: ssd
path: /Users/wjun/tmp/data/clickhouse/ssd/
free_space: 93673529344
total_space: 494384795648
unreserved_space: 93673529344
keep_free_space: 0
type: local
is_encrypted: 0
is_read_only: 0
is_write_once: 0
is_remote: 0
is_broken: 0
cache_path:
select * from system.storage_policies\G
Row 1:
──────
policy_name: default
volume_name: default
volume_priority: 1
disks: ['default']
volume_type: JBOD
max_data_part_size: 0
move_factor: 0
prefer_not_to_merge: 0
perform_ttl_move_on_insert: 1
load_balancing: ROUND_ROBIN
Row 2:
──────
policy_name: moving_from_ssd_to_hdd
volume_name: hot
volume_priority: 1
disks: ['ssd']
volume_type: JBOD
max_data_part_size: 0
move_factor: 0.1
prefer_not_to_merge: 0
perform_ttl_move_on_insert: 1
load_balancing: ROUND_ROBIN
Row 3:
──────
policy_name: moving_from_ssd_to_hdd
volume_name: cold
volume_priority: 2
disks: ['hdd']
volume_type: JBOD
max_data_part_size: 0
move_factor: 0.1
prefer_not_to_merge: 0
perform_ttl_move_on_insert: 1
load_balancing: ROUND_ROBIN
这里需要对 disk 和 volume 做一下补充！！！在 clickhouse 中 disk 代表着实际的存储磁盘而 volume 是一个高层次抽象概念，一个 volume 包含一个或多个 disk。volume 可以对存储策略进行更灵活和高级的配置，特别是在数据的冷热分层管理中。
对于同一个策略配置的多个卷，会按配置顺序从上往下依次使用，同时默认内置move_factor策略(0.1)，当上层的 volume 存储空间剩余不足move_factor时 clickhouse 会自动迁移数据，这是一个默认、内置的策略。同时一个 volume 可以配置多个磁盘，多个磁盘的使用策略受load_balancing影响，其枚举值为：round_robin、least_used
更详细的使用说明请参考官网文档: https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-multiple-volumes
下面将基于 TTL 实现冷热数据分层存储，假设将一个月内的数据视为热数据反之则为冷数据
create table events_layer
(
event String,
time DateTime,
value UInt64
) engine = MergeTree
partition by toYYYYMMDD(time)
order by (event, time)
ttl time + interval 1 month to volume 'cold'
-- 应用存储策略
settings storage_policy = 'moving_from_ssd_to_hdd';
插入数据进行验证
insert into events_layer
values ('error', now() - interval 2 month, 123),
('error', now(), 123);
可以查询system.parts查看分区存储情况
select partition, disk_name from system.parts where table = 'events_layer' and active;
┌─partition─┬─disk_name─┐
│ 20240315 │ hdd │
│ 20240515 │ ssd │
└───────────┴───────────┘
可以看到过期的数据被存储在 hdd 中，成功实现了冷热数据分层存储
当然 TTL 子句还支持 to disk 语法，就交给观众老爷去探索了（在我看来 volume 要比 disk 有优势，使用 volume 就可以了）
三、聚合数据
3.1 聚合
更多情况是不想删除过期数据，期望通过降低颗粒度来节省资源。例如：我们不想删除数据，同时也不需要一个月后的数据依然是明细，因此我们可以将一个月之前的数据保留每日的汇总。此时依然可以使用 TTL
create table events_agg
(
event String,
time DateTime,
value UInt64
) engine = MergeTree
partition by toYYYYMMDD(time)
order by (toDate(time), event)
ttl time + interval 1 month group by toDate(time) set value = sum(value);
上面 TTL 策略意思为：对于一个月前的数据执行group by toDate(time)并将value的数据设置成sum(value)
需要注意的点是：
- group by 表达式必须是 order by 的前缀。例如上面可以根据业务写成
group by toDate(time)或group by (toDate(time), event) - 若 group by 表达式不完全等同 order by 时，缺省的维度列将使用分组中第一条数据填充(与 MergeTree 合并策略一致)
3.2 改变压缩方式
节省资源的方式不仅仅是聚合数据，也可以通过压缩数据来实现。当然 clickhouse 建表时每个字段已经有默认的压缩方式(LZ4)，那么可以对历史数据采用压缩比更高的算法来降低存储。例如第二节的events_layer期望存储在冷盘的数据采用ZSTD算法来压缩，相对默认的LZ4具有更高的压缩比
alter table events_layer modify ttl time + interval 1 month recompress codec(ZSTD);
再次查看system.parts表
select partition, bytes_on_disk, data_compressed_bytes, data_uncompressed_bytes, default_compression_codec,disk_name
from system.parts
where table = 'events_layer' and active;
┌─partition─┬─bytes_on_disk─┬─data_compressed_bytes─┬─data_uncompressed_bytes─┬─default_compression_codec─┬─disk_name─┐
│ 20240315 │ 381 │ 120 │ 18 │ ZSTD(1) │ hdd │
│ 20240515 │ 357 │ 96 │ 18 │ LZ4 │ ssd │
└───────────┴───────────────┴───────────────────────┴─────────────────────────┴───────────────────────────┴───────────┘
细心的小伙伴发现在测试recompress使用的是已存在的表，且该表已经配置过 TTL 了，没错上面隐约提及过 clikhouse 允许创建多个 TTL 且这些策略可以不是相同类型，例如可以将本文出现过的所有 TTL 配置到一张表中(如何你的业务有这么复杂的话)
create table events_multiple_ttl
(
event String,
time DateTime,
value UInt64
) engine = MergeTree
partition by toYYYYMMDD(time)
order by (toDate(time), event)
ttl
time + interval 1 second,
time + interval 1 minute where event = 'error',
time + interval 1 hour where event != 'error',
time + interval 1 day to volume 'cold',
time + interval 1 month to disk 'hdd',
time + interval 1 year recompress codec(ZSTD)
settings storage_policy = 'moving_from_ssd_to_hdd',
merge_with_ttl_timeout = 60;
7097
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
