数据库TTL——管理 clickhouse 数据的生命周期
王一川随着时间的推移,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;
|