2025年2月24日 星期一 甲辰(龙)年 腊月廿四 设为首页 加入收藏
rss
您当前的位置:首页 > 计算机 > 软件应用 > 数据库 > 其它

Clickhouse数据表&数据分区partition&数据生命周期操作

时间:09-01来源:作者:点击数:21

表操作

创建数据库
  • create database radar;
创建表
  • CREATE TABLE radar.traffic_event
  • (
  • `time_stamp` DateTime COMMENT '雷达上报时间',
  • `millisecond` Int8 COMMENT '毫秒数',
  • `detector_nbr` String COMMENT '检测器id',
  • `lane_nbr` String COMMENT '车道号',
  • `event_status` String COMMENT '事件状态',
  • `event_code` String COMMENT '事件码'
  • )
  • ENGINE = MergeTree
  • PARTITION BY toYYYYMMDD(time_stamp)
  • PRIMARY KEY(time_stamp)
  • ORDER BY(time_stamp, millisecond, detector_nbr)
  • SETTINGS index_granularity = 8192, old_parts_lifetime = 300;
  • ENGINE:表引擎,最常用的是MergeTree,或者说MergeTree家族的各个引擎,当然也可以选择其他引擎。但是,只有MergeTree系列的表引擎才支持主键索引,数据分区,数据副本,数据采样这样的特性,只有此系列的表引擎才支持alter操作
  • PARTITION BY:指定分区键,主要根据你的业务场景和数据量大小,可以按年、按月、按天或者其他时间间隔分区,也可以按照哈希去分区。当然,若不声明分区键,则clickhouse会生成一个名为all的分区
  • ORDER BY:排序字段,clickhouse表会根据排序字段建索引,方便快速查找。如果没有指定主键,排序字段就是主键
  • PRIMARY KEY:指定主键,它必须是分区键的前缀,或者等于分区键
  • SETTINGS:配置项,可以把一些配置在这里设置,多个逗号分割
  • index_granularity:默认8192,表示索引的粒度,即MergeTree的索引在默认情况下,每间隔8192行才生成一个索引。通常不需要修改此参数,不设置就是8192。
  • old_parts_lifetime:已合并的分区块,多久后删除,默认8分钟
删除表

生产环境,请谨慎使用删除命令,最好做好备份再删除

  • DROP TABLE IF EXISTS radar.traffic_event;
清空表
  • TRUNCATE table radar.traffic_event ;
  • truncate table if exists default.alter_table_rename_test;
删除表某个分区

分区删掉了,该分区所有数据都删掉了

  • ALTER TABLE traffic_event drop partition '20220704';
重命名或移动数据表
  • rename table default.alter_table_test to default.alter_table_rename_test;
  • 多个db.tb to db.tb用逗号分隔
  • 如果源表与目标表数据库不一样,则表示移动数据表, 但数据表的移动只能在同一服务器
  • 支持on cluster cluster_name操作

表字段column操作

也可以直接去看官方文档:https://clickhouse.com/docs/zh/sql-reference/statements/alter/column

  • ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN
添加字段
  • # 集群
  • ALTER table traffic_event ON CLUSTER cluster_name ADD COLUMN node_id, ADD COLUMN node_name;
  • # 非集群
  • alter table alter_table_test add column if not exists score Float32 default 8.8 after city;
删除字段
  • ALTER table traffic_event drop column if exists node_id;
修改字段
  • ALTER table traffic_event modify column if exists lane_nbr Int8;
  • # 修改字段数据类型、添加或修改字段默认值
  • alter table alter_table_test modify column if exists score Float64 default 0.0;
  • # 添加或修改字段备注
  • alter table alter_table_test comment column if exists score '分数';
修改字段名称
  • ALTER table signal_status RENAME COLUMN IF EXISTS green_end_time to stage_end_time;

数据分区partition的基本操作

测试表和测试数据的准备

  • clickhouse1 :)
  • clickhouse1 :) create table partition_table_test(
  • :-] id UInt32,
  • :-] name String,
  • :-] city String
  • :-] ) engine = MergeTree()
  • :-] order by id
  • :-] partition by city;
  • clickhouse1 :)
  • clickhouse1 :) insert into partition_table_test(id, name, city) values(1, 'name1', 'Beijing');
  • clickhouse1 :) insert into partition_table_test(id, name, city) values(2, 'name2', 'Shanghai');
  • clickhouse1 :)
  • clickhouse1 :) create table partition_table_test2(
  • :-] id UInt32,
  • :-] name String,
  • :-] city String
  • :-] ) engine = ReplacingMergeTree()
  • :-] order by id
  • :-] partition by city;
  • clickhouse1 :)
查询数据表partition相关信息
  • clickhouse1 :)
  • clickhouse1 :) select database, table, partition, partition_id, name, path from system.parts where database = 'default' and table = 'partition_table_test';
  • ┌─database─┬─table────────────────┬─partition─┬─partition_id─────────────────────┬─name───────────────────────────────────┬─path────────────────────────────────────────────────────────────────────────────────────────────────────┐
  • default │ partition_table_test │ Shanghai │ 6a9748c898bf80cb661db240706867aa │ 6a9748c898bf80cb661db240706867aa_2_2_0 │ /root/clickhouse/store/9eb/9ebd4336-b065-48ac-9ebd-4336b06588ac/6a9748c898bf80cb661db240706867aa_2_2_0/
  • default │ partition_table_test │ Beijing │ 8d2db6c332407299b732139fd8a261c0 │ 8d2db6c332407299b732139fd8a261c0_1_1_0 │ /root/clickhouse/store/9eb/9ebd4336-b065-48ac-9ebd-4336b06588ac/8d2db6c332407299b732139fd8a261c0_1_1_0/
  • └──────────┴──────────────────────┴───────────┴──────────────────────────────────┴────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────┘
  • clickhouse1 :)
删除partition
  • clickhouse1 :)
  • clickhouse1 :) alter table partition_table_test drop partition 'Beijing'
  • :-] ;
  • clickhouse1 :)
  • clickhouse1 :) select * from partition_table_test;
  • ┌─id─┬─name──┬─city─────┐
  • 2 │ name2 │ Shanghai │
  • └────┴───────┴──────────┘
  • clickhouse1 :)

上面我们删除了城市为Beijing的partition,然后再通过insert插入新的数据,就间接实现了数据更新

复制partition
  • clickhouse1 :)
  • clickhouse1 :) alter table partition_table_test2 replace partition 'Shanghai' from partition_table_test;
  • clickhouse1 :)
  • clickhouse1 :) select * from partition_table_test2;
  • ┌─id─┬─name──┬─city─────┐
  • 2 │ name2 │ Shanghai │
  • └────┴───────┴──────────┘
  • clickhouse1 :)
  • 将A表的数据partition,复制到B表的条件:
    • 两张表字段结构完全相同
    • 两张表partition by、order by一样
  • 会删除目标表partition_table_test2原来的城市Shanghai partition
将partition中某一列的数据变为默认值
  • clickhouse1 :)
  • clickhouse1 :) alter table partition_table_test clear column name in partition 'Shanghai';
  • clickhouse1 :)
  • clickhouse1 :) select * from partition_table_test;
  • ┌─id─┬─name─┬─city─────┐
  • 2 │ │ Shanghai │
  • └────┴──────┴──────────┘
  • clickhouse1 :)
  • 变更字段不能为primary key、order by、partition by定义的字段
  • 如果该字段未声明默认值,则以字段数据类型的默认值为准
partition的卸载和装载
  • clickhouse1 :)
  • clickhouse1 :) alter table partition_table_test detach partition 'Shanghai';
  • clickhouse1 :)
  • clickhouse1 :) select * from partition_table_test;
  • SELECT *
  • FROM partition_table_test
  • Query id: 45460933-7b2e-4389-a056-85d3d75184a8
  • Ok.
  • 0 rows in set. Elapsed: 0.005 sec.
  • clickhouse1 :)
  • clickhouse1 :) alter table partition_table_test attach partition 'Shanghai';
  • clickhouse1 :)
  • clickhouse1 :) select * from partition_table_test;
  • ┌─id─┬─name─┬─city─────┐
  • 2 │ │ Shanghai │
  • └────┴──────┴──────────┘
  • clickhouse1 :)
  • detach后,该分区目录被移动到数据表目录的detached目录下
  • clickhouse除了能对detached目录下的分区目录执行attach命令, 不能执行其它操作
  • attach则将detached目录下的分区目录重新移回去

ClickHouse表引擎–MergeTree数据生命周期

数据 TTL
  • TTL(Time To Live)表示数据的存活时间,在 Merge 中可以为某个字段或者整个表设置TTL。
  • 如果设置列级别的 TTL,那么到期时会删除这一列的数据
  • 如果设置表级别的 TTL,那么到期时会删除整个表的数据
  • 如果同时设置了列级别和表级别的 TTL,那么会以先到期的为主。
  • 无论是列级别还是表级别的 TTL 都要依靠 DateTime 或 Date 类型的字段,通过对这个字段的 Interval 操作,来表述 TTL 的过期时间。
  • INTERVAL 的完整操作包括:SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER 和 YEAR
  • -- 设置数据存活时间是 create_time 的三天后
  • TTL create_time + INTERVAL 3 DAY
  • -- 设置数据存活时间是 create_time 的三个月后
  • TTL create_time + INTERVAL 3 MONTH
列级别 TTL
  • 设置列级别的 TTL,需要在建表时设置列的 TTL 表达式
  • 主键不能被设置 TTL
  • -- 创建表并设置 TTL 字段
  • -- create_time 是事件类型的字段,
  • -- code1 和 code2 均被设置了 TTL,
  • -- 存活时间为create_time的基础上向后10 秒和 15 秒
  • CREATE TABLE merge_column_ttl
  • (
  • `id` String,
  • `create_time` DateTime,
  • `code1` String TTL create_time + INTERVAL 10 SECOND,
  • `code2` UInt8 TTL create_time + INTERVAL 15 SECOND
  • )
  • ENGINE = MergeTree
  • PARTITION BY toYYYYMM(create_time)
  • ORDER BY id;
  • -- 查看表结构
  • describe merge_column_ttl;
  • ┌─name────────┬─type─────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─────────────────────┐
  • │ id │ String │ │ │ │ │ │
  • │ create_time │ DateTime │ │ │ │ │ │
  • │ code1 │ String │ │ │ │ │ create_time + toIntervalSecond(10) │
  • │ code2 │ Int32 │ │ │ │ │ create_time + toIntervalSecond(15) │
  • └─────────────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────────────────────────┘
  • -- 写入测试数据
  • insert into table merge_column_ttl values
  • ('A01',now(),'A01-code1','100'),
  • ('A02',now() + INTERVAL 3 MINUTE,'A02-code1','99');
  • -- 当差不多过十秒后查看数据
  • select * from merge_column_ttl;
  • ┌─id──┬─────────create_time─┬─code1─────┬─code2─┐
  • │ A01 │ 2021-06-23 15:50:17 │ │ 100
  • │ A02 │ 2021-06-23 15:53:17 │ A02-code1 │ 99
  • └─────┴─────────────────────┴───────────┴───────┘
  • -- 强制触发 TTL 清理
  • optimize table merge_column_ttl final;
  • -- 查看测试数据
  • select * from merge_column_ttl;
  • ┌─id──┬─────────create_time─┬─code1─────┬─code2─┐
  • │ A01 │ 2021-06-23 15:50:17 │ │ 0
  • │ A02 │ 2021-06-23 15:53:17 │ A02-code1 │ 99
  • └─────┴─────────────────────┴───────────┴───────┘
  • -- 可以看到测试数据被还原成了 每个字段类型的默认值
  • 修改字段 TTL
  • alter table merge_column_ttl modify column code1 String TTL create_time + INTERVAL 10 MINUTE;
  • -- 查看表结构
  • describe merge_column_ttl;
  • ┌─name────────┬─type─────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─────────────────────┐
  • │ id │ String │ │ │ │ │ │
  • │ create_time │ DateTime │ │ │ │ │ │
  • │ code1 │ String │ │ │ │ │ create_time + toIntervalMinute(10) │
  • │ code2 │ Int32 │ │ │ │ │ create_time + toIntervalSecond(15) │
  • └─────────────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────────────────────────┘
表级别 TTL
  • 想要为整张表设置 TTL,需要在建表时设置表的 TTL 表达式
  • -- 建表设置表的 TTL
  • CREATE TABLE merge_table_ttl
  • (
  • `id` String,
  • `create_time` DateTime,
  • `code1` String TTL create_time + INTERVAL 10 SECOND,
  • `code2` UInt8
  • )
  • ENGINE = MergeTree
  • PARTITION BY toYYYYMM(create_time)
  • ORDER BY id
  • TTL create_time + INTERVAL 15 SECOND;
  • -- 写入测试数据
  • insert into table merge_table_ttl values
  • ('A01',now(),'A01-code1','100'),
  • ('A02',now() + INTERVAL 3 MINUTE,'A02-code1','99');
  • -- 查看数据,这是还没有过期数据
  • select * from merge_table_ttl;
  • ┌─id──┬─────────create_time─┬─code1─────┬─code2─┐
  • │ A01 │ 2021-06-23 16:26:12 │ A01-code1 │ 100
  • │ A02 │ 2021-06-23 16:29:12 │ A02-code1 │ 99
  • └─────┴─────────────────────┴───────────┴───────┘
  • -- 强制清理 TTL 数据
  • optimize table merge_table_ttl final;
  • -- 查看数据,这时 code1 有一行数据过期,被还原成默认值
  • select * from merge_table_ttl;
  • ┌─id──┬─────────create_time─┬─code1─────┬─code2─┐
  • │ A01 │ 2021-06-23 16:26:12 │ │ 100
  • │ A02 │ 2021-06-23 16:29:12 │ A02-code1 │ 99
  • └─────┴─────────────────────┴───────────┴───────┘
  • -- 强制清理 TTL 数据
  • optimize table merge_table_ttl final;
  • -- 查看数据,这时 A01 这行数据过期已经被删掉
  • select * from merge_table_ttl;
  • ┌─id──┬─────────create_time─┬─code1─────┬─code2─┐
  • │ A02 │ 2021-06-23 16:29:12 │ A02-code1 │ 99
  • └─────┴─────────────────────┴───────────┴───────┘
  • 修改表级别的 TTL
  • alter table merge_table_ttl modify ttl create_time + INTERVAL 15 MINUTE;
TTL 运行原理

如果一张表被设置了 TTL,在写入数据时,会以数据分区为单位,在每个分区目录中生成一个 ttl.txt 的文件,如 merge_table_ttl 表即被设置了列级别的 TTL 也被设置了表级别的 TTL,那么每个分区目录都会生成 ttl.txt 文件

  • cd /var/lib/clickhouse/data/db_merge/merge_table_ttl/202106_1_1_3
  • [root@node3 202106_1_1_3]# cat ttl.txt
  • ttl format version: 1
  • {"columns":[{"name":"code1","min":1624436962,"max":1624436962}],"table":{"min":1624436967,"max":1624436967}}
  • # 将上面的 json 格式化 并加上描述
  • # MergeTree 通过 json 结构的数据保存 ttl 的相关信息:
  • {
  • "columns": [ # 用于保存 列级别的 TTL 信息
  • {
  • "name": "code1",
  • "min": 1624436962,
  • "max": 1624436962
  • }
  • ],
  • "table": {# 用于保存 表级别的 TTL 信息
  • "min": 1624436967,
  • "max": 1624436967
  • }
  • }

min 与 max 保存了当前分区内,TTL 指定日期字段的最大值,最小值分别与 INTERVAL 表达式计算后的时间戳。

如果将table 属性中的min 和 max 时间戳格式化,并分别与 create_time 的最大,最小取值作对比。

  • SELECT
  • toDateTime('1624436967') AS ttl_min,
  • toDateTime('1624436967') AS ttl_max,
  • ttl_min - MIN(create_time) AS expire_min,
  • ttl_max - MAX(create_time) AS expire_max
  • FROM merge_table_ttl
  • Query id: ad5dc6af-1f8c-41a2-829a-802ad702cfbe
  • ┌─────────────ttl_min─┬─────────────ttl_max─┬─expire_min─┬─expire_max─┐
  • 2021-06-23 16:29:272021-06-23 16:29:271515
  • └─────────────────────┴─────────────────────┴────────────┴────────────┘

ttl.txt记录的 min,max 值恰好对应 create_time + INTERVAL 15 SECOND 的值。

  • MergeTree 处理 TTL 的大致逻辑如下:
  1. MergeTree 以分区目录为单位,通过 ttl.txt 文件记录过期时间,并将其作为后续判断依据
  2. 每当写入一批数据时,都会基于 INTERVAL 表达式的计算结果为这个分区生成 ttl.txt文件
  3. 只有在 MergeTree 分区合并时,才会触发删除过期数据的逻辑。
  4. 在删除数据时,会使用贪婪算法,算法规则是尽可能找到会最早过期的,同时年纪又最老的分区(合并次数最多,MaxBlockNum最大)
  5. 如果一个分区的某个字段因为 TTL 到期,导致数据被全部删除,那么合并分区时在生成新分区将不会创建该字段的数据文件(.mrk、.bin)
  • MergeTree 处理 TTL 的其他事项:
  1. TTL 的默认合并频率由MergeTree 的 merge_with_ttl_timeout 参数控制,默认 86400 秒,即 1 天,它维护一个专门的 TTL 队列。如果这个时间设置的过小会带来性能损耗。
  2. 除了被动触发 TTL,还可以使用optimize 命令强制触发合并。
  • -- 触发一个分区的合并
  • optimize table table_name
  • -- 触发所有分区的合并
  • optimize table table_name final
  1. Clickhouse 提供了控制全局 TTL 任务合并的方法,但是不能按照数据表停起
  • SYSTEM STOP/START MERGES
方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
推荐内容
相关内容
栏目更新
栏目热门
本栏推荐