1. 执行SQL
clickhouse1 :)
clickhouse1 :) CREATE TABLE ttl_table_local ON CLUSTER sharding_ha
:-] (
:-] `id` UInt64,
:-] `create_time` DateTime,
:-] `code` String TTL create_time + toIntervalSecond(30),
:-] `type` UInt8 TTL create_time + toIntervalSecond(30)
:-] )
:-] ENGINE = ReplicatedMergeTree('/clickhouse/tables/ttl_table/{shard}', '{replica}')
:-] ORDER BY id
:-] TTL create_time + toIntervalMinute(1);
CREATE TABLE ttl_table_local ON CLUSTER sharding_ha
(
`id` UInt64,
`create_time` DateTime,
`code` String TTL create_time + toIntervalSecond(30),
`type` UInt8 TTL create_time + toIntervalSecond(30)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/ttl_table/{shard}', '{replica}')
ORDER BY id
TTL create_time + toIntervalMinute(1)
Query id: bfc40713-1fe0-458a-9ac8-3a59872a224a
┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse2 │ 9000 │ 0 │ │ 3 │ 0 │
│ clickhouse3 │ 9000 │ 0 │ │ 2 │ 0 │
│ clickhouse1 │ 9000 │ 0 │ │ 1 │ 0 │
│ clickhouse4 │ 9000 │ 0 │ │ 0 │ 0 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
4 rows in set. Elapsed: 0.149 sec.
clickhouse1 :)
clickhouse1 :) CREATE TABLE ttl_table_all ON CLUSTER sharding_ha
:-] (
:-] `id` UInt64,
:-] `create_time` DateTime,
:-] `code` String,
:-] `type` UInt8
:-] )
:-] ENGINE = Distributed(sharding_ha, default, ttl_table_local, rand());
CREATE TABLE ttl_table_all ON CLUSTER sharding_ha
(
`id` UInt64,
`create_time` DateTime,
`code` String,
`type` UInt8
)
ENGINE = Distributed(sharding_ha, default, ttl_table_local, rand())
Query id: 6f657775-d637-44fe-bf07-25a04084c72d
┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse2 │ 9000 │ 0 │ │ 3 │ 0 │
│ clickhouse3 │ 9000 │ 0 │ │ 2 │ 0 │
│ clickhouse1 │ 9000 │ 0 │ │ 1 │ 0 │
│ clickhouse4 │ 9000 │ 0 │ │ 0 │ 0 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
4 rows in set. Elapsed: 0.135 sec.
clickhouse1 :)
clickhouse1 :) insert into ttl_table_all(id, create_time, code, type) values(66, now(), 'A', 1), (66, now() + interval 5 minute, 'A', 1);
INSERT INTO ttl_table_all (id, create_time, code, type) VALUES
Query id: d18773fe-bcbc-4e85-9cc4-563a686de5db
Ok.
2 rows in set. Elapsed: 0.015 sec.
clickhouse1 :)
查询数据如下:
clickhouse1 :)
clickhouse1 :) select * from ttl_table_all limit 10;
SELECT *
FROM ttl_table_all
LIMIT 10
Query id: 5ab98d68-1253-4760-b654-ef130715cbd0
┌─id─┬─────────create_time─┬─code─┬─type─┐
│ 66 │ 2021-07-14 11:37:00 │ A │ 1 │
│ 66 │ 2021-07-14 11:42:00 │ A │ 1 │
└────┴─────────────────────┴──────┴──────┘
2 rows in set. Elapsed: 0.011 sec.
clickhouse1 :)
过了30秒之后,再次查看:
clickhouse1 :)
clickhouse1 :) optimize table ttl_table_local on cluster sharding_ha final;
OPTIMIZE TABLE ttl_table_local ON CLUSTER sharding_ha FINAL
Query id: b9b55917-6974-4d26-8e7d-697aee1cec1a
┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse2 │ 9000 │ 0 │ │ 3 │ 0 │
│ clickhouse1 │ 9000 │ 0 │ │ 2 │ 0 │
│ clickhouse3 │ 9000 │ 0 │ │ 1 │ 0 │
│ clickhouse4 │ 9000 │ 0 │ │ 0 │ 0 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
4 rows in set. Elapsed: 0.164 sec.
clickhouse1 :)
clickhouse1 :) select * from ttl_table_all limit 10;
SELECT *
FROM ttl_table_all
LIMIT 10
Query id: e814ed48-fea6-4a39-b27e-5b37dddc3bc1
┌─id─┬─────────create_time─┬─code─┬─type─┐
│ 66 │ 2021-07-14 11:37:00 │ │ 0 │
│ 66 │ 2021-07-14 11:42:00 │ A │ 1 │
└────┴─────────────────────┴──────┴──────┘
2 rows in set. Elapsed: 0.017 sec.
clickhouse1 :)
又过了30秒之后,再次查看:
clickhouse1 :)
clickhouse1 :) optimize table ttl_table_local on cluster sharding_ha final;
OPTIMIZE TABLE ttl_table_local ON CLUSTER sharding_ha FINAL
Query id: 63d61b9e-755f-4a17-a78f-c5cce6a76b93
┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse2 │ 9000 │ 0 │ │ 3 │ 0 │
│ clickhouse1 │ 9000 │ 0 │ │ 2 │ 0 │
│ clickhouse3 │ 9000 │ 0 │ │ 1 │ 0 │
│ clickhouse4 │ 9000 │ 0 │ │ 0 │ 0 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
4 rows in set. Elapsed: 0.131 sec.
clickhouse1 :)
clickhouse1 :) select * from ttl_table_all limit 10;
SELECT *
FROM ttl_table_all
LIMIT 10
Query id: 230225a3-3c2b-447a-a490-bfe1f936c9c8
┌─id─┬─────────create_time─┬─code─┬─type─┐
│ 66 │ 2021-07-14 11:42:00 │ A │ 1 │
└────┴─────────────────────┴──────┴──────┘
1 rows in set. Elapsed: 0.009 sec.
clickhouse1 :)
2. 说明
- 同时设置了列TTL和表TTL,以先到期的为主
- 主键字段不能设置TTL,TTL字段类型为Date或DateTime, TTL时间类型有second、minute、hour、day、 week、month、quarter和year
- 修改或添加列字段的TTL:alter table ttl_table_local on cluster sharding_ha modify column code String ttl create_time + interval 1 day
- 修改表TTL:alter table ttl_table_local on cluster sharding_ha modify ttl create_time + interval 3 day
- 没有提供取消列和表TTL的方法
3. TTL的运行原理
todo