[ClickHouse 运维系列] 数据 TTL 学习笔记整理

clickhouseTTL">一、clickhouse TTL

ClickHouse的MergeTree引擎支持数据生命周期管理，即TTL（Time To Live）。TTL可以通过设置列字段或整张表的过期时间来实现。列字段的TTL会在时间到期时删除该列数据，而表级别的TTL会删除整张表的数据。如果同时设置了列级别和表级别的TTL，那么先到期的设置将被优先执行。TTL的设置依赖于日期或时间类型的字段，通过INTERVAL操作来指定数据的存活时间。

ClickHouse中分为表级TTL和列级TTL，表级TTL就是表中的某些行数据到期了，会自动删除；列级TTL则是对表中的某些字段设置过期时间，一旦过期，表中的该字段则会变成0。
只谈表级TTL，因为列级TTL基本很少用，用的时候百度下就可以了。
涉及判断的字段，下面语句的create_time, 必须是 Date 或者 Datetime 类型，推荐使用分区的日期字段。
建表时，指定表级TTL, 下面语句中，说明只保留create_time值是1月内的数据。
使用过期时间的单位:SECOND、MINUTE、MINUTE、DAY、WEEK、MONTH、QUARTER YEAR

二、验证测试

[root@prod-ck-01 ~]# clickhouse-client --host 10.110.5.135 --port 9000 --user default --password 'xxx!' -m

ClickHouse client version 22.9.4.32 (official build).

Connecting to 10.110.5.135:9000 as user default.

Connected to ClickHouse server version 22.9.4 revision 54460.

Warnings:

* Some obsolete setting is changed. Check 'select * from system.settings where changed' and read the changelog.

prod-ck-01 :) CREATE TABLE ckdb.student(

id UInt32,

stu_id String,

total_score Decimal(16,2),

create_time Datetime

) ENGINE = MergeTree

PARTITION BY toYYYYMMDD(create_time)

PRIMARY KEY (id)

ORDER BY (id, stu_id)

TTL create_time + INTERVAL 1 MONTH DELETE;

CREATE TABLE ckdb.student

(

`id` UInt32,

`stu_id` String,

`total_score` Decimal(16, 2),

`create_time` Datetime

)

ENGINE = MergeTree

PARTITION BY toYYYYMMDD(create_time)

PRIMARY KEY id

ORDER BY (id, stu_id)

TTL create_time + toIntervalMonth(1)

Query id: dcfbc78f-9d7a-47fc-a52e-4abf7bbd0149

Ok.

0 rows in set. Elapsed: 0.014 sec.

prod-ck-01 :) INSERT INTO ckdb.student VALUES

(1, 'S001', 95.5, '2024-03-01 10:00:00'),

(2, 'S002', 87.0, '2024-04-01 10:00:00'),

(3, 'S003', 78.0, '2024-05-01 10:00:00'),

(4, 'S004', 88.5, '2024-06-01 10:00:00'),

(5, 'S005', 92.3, '2024-07-01 10:00:00');

INSERT INTO ckdb.student FORMAT Values

Query id: 14238ade-d4c1-45ff-9108-2ff0c7644c64

Ok.

5 rows in set. Elapsed: 0.026 sec.

prod-ck-01 :) SELECT * FROM ckdb.student;

SELECT *

FROM ckdb.student

Query id: 6e83b672-834b-4274-a899-9f0db6007a32

┌─id─┬─stu_id─┬─total_score─┬─────────create_time─┐

│ 3 │ S003 │ 78 │ 2024-05-01 10:00:00 │

└────┴────────┴─────────────┴─────────────────────┘

┌─id─┬─stu_id─┬─total_score─┬─────────create_time─┐

│ 5 │ S005 │ 92.3 │ 2024-07-01 10:00:00 │

└────┴────────┴─────────────┴─────────────────────┘

┌─id─┬─stu_id─┬─total_score─┬─────────create_time─┐

│ 4 │ S004 │ 88.5 │ 2024-06-01 10:00:00 │

└────┴────────┴─────────────┴─────────────────────┘

3 rows in set. Elapsed: 0.009 sec.

prod-ck-01 :) INSERT INTO ckdb.student VALUES

(6, 'S006', 85.0, '2022-01-01 10:00:00'),

(7, 'S007', 90.0, '2022-02-01 10:00:00');

INSERT INTO ckdb.student FORMAT Values

Query id: 7386f602-849f-42f7-a347-b733062fa7aa

Ok.

2 rows in set. Elapsed: 0.011 sec.

prod-ck-01 :) SELECT * FROM ckdb.student;

SELECT *

FROM ckdb.student

Query id: 30672af4-ef29-4bf7-814f-ba32406d34b1

┌─id─┬─stu_id─┬─total_score─┬─────────create_time─┐

│ 5 │ S005 │ 92.3 │ 2024-07-01 10:00:00 │

└────┴────────┴─────────────┴─────────────────────┘

┌─id─┬─stu_id─┬─total_score─┬─────────create_time─┐

│ 3 │ S003 │ 78 │ 2024-05-01 10:00:00 │

└────┴────────┴─────────────┴─────────────────────┘

┌─id─┬─stu_id─┬─total_score─┬─────────create_time─┐

│ 4 │ S004 │ 88.5 │ 2024-06-01 10:00:00 │

└────┴────────┴─────────────┴─────────────────────┘

3 rows in set. Elapsed: 0.009 sec.