clickhouse21.7.11.3 表引擎-MergeTree-TTL 数据存活时间

作者：马育民 • • 阅读：10196

什么是TTL？

TTL的意思是 Time To Live ，表示数据的存活时间。

由于数据的价值会根据保存的时间成反比，出于存储成本的考虑，通常只会保留近一年的数据。

在 MergeTree （合并树）引擎 中，可以通过设置 TTL 来管理数据的存活时间，使数据的价值最大化。

设置方式

在ClickHouse 中，TTL可以设置 值的生命周期，设置方式有：

  • 为整张表设置。还可以指定数据在磁盘和卷上自动转移
  • 为每个列字段单独设置
  • 也可以同时设置 列级别 和 表级别 的TTL，以先到期的为准

依赖 日期时间 字段

无论列级别还是表级别的TTL，都需要依托某个 Datetime 或者 date 类型的字段，通过对这个时间字段的 INTERVAL 操作来表述TTL的过期时间：
示例：

TTL time_column + interval 3 DAY

表示数据存活的时间为 time_column 时间的3天之后。

TTL 表达式的计算结果必须是 日期（date） 或 日期时间（datetime）

应用场景

对于某部分业务，是不需要旧数据的

比如：用户的画像数据，用户画像是按天按小时更新甚至实时更新，比较旧的画像数据，没必要保存，所以要删除。

列级别TTL

当列中的值过期时, ClickHouse会将它们替换成数据类型的默认值。如果分区内，某一列的所有值均已过期，ClickHouse会从文件系统中删除此列。

ClickHouse的 INTERVAL（时间间隔）支持的操作有：

  • second(秒)
  • minute(分钟)
  • hour(小时)
  • day(日)
  • week(周)
  • month(月)
  • quarter(季度)
  • year(年)

创建表时指定 TTL

CREATE TABLE t_access_log_mt
(
    ip String TTL access_time + INTERVAL 1 minute,
    url String TTL access_time + INTERVAL 2 minute,
    access_time DateTime
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(access_time)
ORDER BY access_time;

为表中已存在的列字段添加 TTL

ALTER TABLE t_access_log_mt
    MODIFY COLUMN `ip` String TTL access_time + toIntervalMinute(1)

修改列字段的 TTL

ALTER TABLE t_access_log_mt
    MODIFY COLUMN
    ip String TTL access_time + INTERVAL 3 minute;

查看表结构哦

desc t_access_log_mt

执行结果：

name type ttl_expression
ip String access_time + toIntervalMinute(1)
url String access_time + toIntervalMinute(2)
access_time DateTime

插入数据

insert into t_access_log_mt values
('192.168.1.100','index.html',now()),
('192.168.1.101','list.html',now()),
('192.168.1.103','list.html','2022-05-31 11:07:00'),

解释：

  • now()：获取当前 年月日时分秒
  • '2022-05-31 11:07:00'：该时间肯定超出 TTL 了

执行结果：

  1. 第一次执行时，'2022-05-31 11:07:00' 的 ip、url 可能 都是空的
  2. 之后再执行时，即使 TTL 超时，ip、url也 不会清空

TTL 触发时机

  • 第一次执行 insert 时，TTL超时的数据 可能 会立即清除
  • 手动合并分区

表级别TTL

表可以设置一个用于移除过期行的表达式，以及多个用于在磁盘或卷上自动转移数据片段的表达式。当表中的行过期时，ClickHouse 会删除所有对应的行。

示例：event_date超过一个月的数据自动删除

创建时指定 TTL

CREATE TABLE t_access_log_mt2
(
    ip String TTL access_time + INTERVAL 1 minute,
    url String TTL access_time + INTERVAL 2 minute,
    access_time DateTime
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(access_time)
ORDER BY access_time
TTL access_time + INTERVAL 10 second;

查看表 TTL

show create table t_access_log_mt2;

或者

SELECT 
    database, 
    name, 
    engine, 
    data_paths, 
    metadata_path, 
    metadata_modification_time, 
    partition_key, 
    sorting_key,
    create_table_query 
FROM system.tables
WHERE name = 't_access_log_mt2'

修改表的 TTL

access_time 超过 10秒 的数据自动删除

ALTER TABLE t_access_log_mt2 MODIFY TTL access_time + INTERVAL 10 second;

插入数据

insert into t_access_log_mt2 values
('192.168.1.100','index.html',now()),
('192.168.1.101','list.html',now()),
('192.168.1.103','list.html','2022-05-31 11:07:00'),

解释：

  • now()：获取当前 年月日时分秒
  • '2022-05-31 11:07:00'：该时间肯定超出 TTL 了

执行结果：

  1. 第一次执行时，'2022-05-31 11:07:00' 的 数据被删除了，只剩2条数据
  2. 之后再执行时，即使 TTL 超时，数据也 不会被删除

TTL 触发时机

  • 第一次执行 insert 时，TTL超时的数据会立即清除
  • 再次执行 insert 时，TTL超时，并不会立即清理数据，在 合并临时分区时，才会清理数据（执行 5 次 insert时）
  • 手动合并分区

感谢：
https://juejin.cn/post/6909997781268578317

原文出处：/show_1IX3Pb7WqmAO.html

吉ICP备15003043号