clickhouse之表和列的TTL规则与实践

42 阅读 0 评论 28 点赞

我是靠谱客的博主活力野狼，最近开发中收集的这篇文章主要介绍clickhouse之表和列的TTL规则与实践，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1.表和列的TTL

定义值的生命周期

可以为整个表设置，也可以为每个单独的列设置。

表级的TTL也可以指定在磁盘和卷之间自动移动数据的逻辑。

设置TTL的表，必须包含Date或DateTime类型的字段。

定义数据的生命周期，需要在这个日期字段使用操作符：
TTL time_column
TTL time_column + interval
示例：
TTL date_time + INTERVAL 1 MONTH
TTL date_time + INTERVAL 15 HOUR

2.列级TTL

当列中的值过期时，ClickHouse将它们替换为该列对应数据类型的默认值。

如果数据片段中所有列值都过期，则删除该数据片段下的该列的文件。

TTL子句不能用于key列。

示例：

创建TTL表
TTL过期验证
给列增加TTL
修改列的TTL

3.表级TTL

表级的TTL定义了过期行的删除、磁盘和卷之间自动移动数据的逻辑。
一张表可以定义一个过期行移除的表达式和多个磁盘和卷之间自动移动数据的逻辑的表达式。
TTL expr [DELETE|TO DISK 'aaa'|TO VOLUME 'bbb'], ...
当表中的数据过期时，ClickHouse删除所有对应的行。
TTL规则的类型跟在每个TTL表达式后面，它表示表达式满足后（达到当前时间）要执行的操作。
DELETE - 删除过期的行（默认操作）；
TO DISK ‘aaa’ - 将片段移至磁盘aaa；
TO VOLUME ‘bbb’ - 将片段移动至磁盘bbb；
创建表示例：
CREATE TABLE example_table
(
d DateTime,
a Int
)ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH [DELETE],
d + INTERVAL 1 WEEK TO VOLUME 'aaa',
d + INTERVAL 2 WEEK TO DISK 'bbb'
SETTINGS storage_policy = 'moving_from_ssd_to_hdd';
注意：当TTL表达式指定了磁盘和卷之间移动数据的逻辑，那么ClickHouse的表必须指定存储策略，且该存储策略中要包含相应的磁盘和卷。
使用案例：TTL过期后执行数据删除案例。
1).当ClickHouse合并数据片段时，将删除TTL过期的数据。
2).当ClickHouse发现数据过期时，它将执行一个计划外的合并。要控制这类合并的频率，可设置参数 merge_with_ttl_timeout。如果该值设置的过低，它将导致执行许多的计划外合并，这可能会消耗大量资源。
3).如果在合并的时候执行SELECT查询，则可能会得到过期的数据。为了避免这种情况，可以在SELECT 之前使用OPTIMIZE查询。

4.列级TTL示例

创建带TTL的表

DROP TABLE example_table;
CREATE TABLE example_table
(
d DateTime,
a Int TTL d + INTERVAL 1 MINUTE,
b String TTL d + INTERVAL 1 MINUTE,
c String
)
ENGINE = MergeTree
ORDER BY d;
xxxxx :) show create table example_table;
SHOW CREATE TABLE example_table
┌─statement─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.example_table
(
`d` DateTime,
`a` Int32 TTL d + toIntervalMinute(1),
`b` String TTL d + toIntervalMinute(1),
`c` String
)
ENGINE = MergeTree
ORDER BY d
SETTINGS index_granularity = 8192 │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.007 sec.
xxxxx :)

插入数据

insert into example_table values (now(), 1, 'value1', 'ccc1');
insert into example_table values(now(), 2, 'value2', 'ccc2');

等待1分钟之后，执行：

xxxxx :) select * from example_table;
SELECT *
FROM example_table
┌───────────────────d─┬─a─┬─b──────┬─c────┐
│ 2020-11-26 14:53:16 │ 2 │ value2 │ ccc2 │
└─────────────────────┴───┴────────┴──────┘
┌───────────────────d─┬─a─┬─b──────┬─c────┐
│ 2020-11-26 14:53:15 │ 1 │ value1 │ ccc1 │
└─────────────────────┴───┴────────┴──────┘
2 rows in set. Elapsed: 0.009 sec.
xxxxx :) optimize table example_table;
OPTIMIZE TABLE example_table
Ok.
0 rows in set. Elapsed: 0.004 sec.
xxxxx :) select * from example_table;
SELECT *
FROM example_table
┌───────────────────d─┬─a─┬─b─┬─c────┐
│ 2020-11-26 14:53:15 │ 0 │
│ ccc1 │
│ 2020-11-26 14:53:16 │ 0 │
│ ccc2 │
└─────────────────────┴───┴───┴──────┘
2 rows in set. Elapsed: 0.007 sec.
xxxxx :)

给表的列添加TTL：

ALTER TABLE example_table
MODIFY COLUMN
c String TTL d + INTERVAL 1 DAY;
xxxxx :) ALTER TABLE example_table
:-] MODIFY COLUMN
:-] c String TTL d + INTERVAL 1 DAY;
ALTER TABLE example_table
MODIFY COLUMN `c` String TTL d + toIntervalDay(1)
Ok.
0 rows in set. Elapsed: 0.018 sec.
xxxxx :)

修改列的TTL：

ALTER TABLE example_table
MODIFY COLUMN
c String TTL d + INTERVAL 1 MONTH;
xxxxx :) ALTER TABLE example_table
:-]
MODIFY COLUMN
:-]
c String TTL d + INTERVAL 1 MONTH;
ALTER TABLE example_table
MODIFY COLUMN `c` String TTL d + toIntervalMonth(1)
Ok.
0 rows in set. Elapsed: 0.015 sec.
xxxxx :) show create table example_table;
SHOW CREATE TABLE example_table
┌─statement────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.example_table
(
`d` DateTime,
`a` Int32 TTL d + toIntervalMinute(1),
`b` String TTL d + toIntervalMinute(1),
`c` String TTL d + toIntervalMonth(1)
)
ENGINE = MergeTree
ORDER BY d
SETTINGS index_granularity = 8192 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.004 sec.
xxxxx :)

5.表级TTL示例

创建表

drop table example_table ;
CREATE TABLE example_table
(
d DateTime,
a Int,
b String,
c String
)
ENGINE = MergeTree
ORDER BY d
TTL d + INTERVAL 1 MINUTE DELETE;

插入数据

insert into example_table values (now(), 1, 'value1', 'ccc1');
insert into example_table values(now(), 2, 'value2', 'ccc2');

然后查询结果：

xxxxx :) select * from example_table;
SELECT *
FROM example_table
┌───────────────────d─┬─a─┬─b──────┬─c────┐
│ 2020-11-26 15:24:53 │ 1 │ value1 │ ccc1 │
│ 2020-11-26 15:24:54 │ 2 │ value2 │ ccc2 │
└─────────────────────┴───┴────────┴──────┘
2 rows in set. Elapsed: 0.008 sec.
xxxxx :)

等待1分钟后，执行optimize操作。

xxxxx :) optimize table example_table;
OPTIMIZE TABLE example_table
Ok.
0 rows in set. Elapsed: 0.006 sec.
xxxxx :) select * from example_table;
SELECT *
FROM example_table
Ok.
0 rows in set. Elapsed: 0.003 sec.
xxxxx :)