- 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;
-
生产环境,请谨慎使用删除命令,最好做好备份再删除
- 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;
-
也可以直接去看官方文档: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;
-
测试表和测试数据的准备
- 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 :)
-
- 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 :)
-
- 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插入新的数据,就间接实现了数据更新
- 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 :)
-
- 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 :)
-
- 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 :)
-
- -- 设置数据存活时间是 create_time 的三天后
- TTL create_time + INTERVAL 3 DAY
-
- -- 设置数据存活时间是 create_time 的三个月后
- TTL create_time + INTERVAL 3 MONTH
-
- -- 创建表并设置 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 │
- └─────┴─────────────────────┴───────────┴───────┘
- -- 可以看到测试数据被还原成了 每个字段类型的默认值
-
-
- 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
- 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 │
- └─────┴─────────────────────┴───────────┴───────┘
-
- alter table merge_table_ttl modify ttl create_time + INTERVAL 15 MINUTE;
-
如果一张表被设置了 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:27 │ 2021-06-23 16:29:27 │ 15 │ 15 │
- └─────────────────────┴─────────────────────┴────────────┴────────────┘
-
ttl.txt记录的 min,max 值恰好对应 create_time + INTERVAL 15 SECOND 的值。
- -- 触发一个分区的合并
- optimize table table_name
-
- -- 触发所有分区的合并
- optimize table table_name final
-
- SYSTEM STOP/START MERGES
-