集群表的使用

创建分布式库

create database applydata_bi_dim on cluster cluster_5shards_2replicas;

创建集群本地表

CREATE TABLE dbname.tablename on cluster cluster_name
(
  xx String COMMENT 'xx',
  xx INT COMMENT 'xx'
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/dbname.tabname', '{replica}')     注意： //clickhouse/tables/{shard}/(库名).(表名)
PRIMARY KEY (xx, xx,xx)
ORDER BY (xx, xx,xx)
SETTINGS use_minimalistic_part_header_in_zookeeper = 1, index_granularity = 8192
COMMENT 'xx';

创建分布式分区表

CREATE TABLE if not exists dbname.dis_tablename ON                                                                   注意：命名的时候分布式表在上面的本地表基础上面加上dis_前缀。
    CLUSTER cluster_name AS dbname.tablename ENGINE = Distributed(cluster_name,                                      注意：这里的dbname.tablename对应上面的分布式本地表
dbname,                                                                                                              注意：这里的qcc_bi_test上面的分布式本地表的数据库。
tablename,                                                                                                           注意：这里的tiansl_kyc_cost_convert_data上面的分布式本地表。
rand());

删除本地表语句

drop table dbname.tablename on cluster cluster_name;

删除分布式表语句

drop table dbname.dis_tablename on cluster cluster_name;

添加和删除字段

alter table dbname.tablename on cluster cluster_5shards_2replicas  add column group_id String;
alter table qdbname.tablename on cluster cluster_5shards_2replicas drop column `group_id`;

删除分区

ALTER TABLE db.local_table on cluster cluster_5shards_2replicas DROP PARTITION 20241002;

替换分区

ALTER TABLE db.destination_local_table ON CLUSTER cluster_5shards_2replicas REPLACE PARTITION 20240929 FROM db.source_local_table;

创建ttl表

drop table applydata_bi_ods_test.test_ttl on cluster cluster_5shards_2replicas;
drop table applydata_bi_ods_test.dis_test_ttl on cluster cluster_5shards_2replicas;

-- 建表的时候设置ttl
CREATE TABLE applydata_bi_ods_test.test_ttl on cluster cluster_5shards_2replicas
(
    `qcc_id`     Int64 COMMENT 'qcc_id',
    `session_id` Int64 COMMENT 'session_id',
    `date_info`  DateTime COMMENT '时间'
)
    ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/applydata_bi_ods_test.test_ttl1', '{replica}')
        PRIMARY KEY (qcc_id)
        ORDER BY (qcc_id)
        TTL date_info + INTERVAL 10 SECOND
        SETTINGS use_minimalistic_part_header_in_zookeeper = 1, index_granularity = 8192;

-- 写入测试数据
insert into applydata_bi_ods_test.test_ttl values (1,10,now());

-- 创建分布式表
CREATE TABLE if not exists applydata_bi_ods_test.dis_test_ttl ON
    CLUSTER cluster_5shards_2replicas AS applydata_bi_ods_test.test_ttl ENGINE = Distributed(cluster_5shards_2replicas,
applydata_bi_ods_test,
test_ttl,
rand());

-- 修改表的ttl
ALTER TABLE applydata_bi_ods_test.test_ttl on cluster cluster_5shards_2replicas MODIFY TTL date_info + INTERVAL 120 SECOND;

select * from applydata_bi_ods_test.dis_test_ttl;

SELECT
    *
FROM
    system.tables
WHERE
    database = 'applydata_bi_ods_test'
    AND name = 'test_ttl';

ClickHouse 在执行查询时不会即时检查 TTL 规则。相反，TTL 检查是在后台进行的。ClickHouse 有一个后台任务（默认为每小时执行一次），用于检查并删除过期数据。

ttl_only_drop_parts设置为true时ClickHouse 只会在部分中的所有行根据 TTL 配置已过期时删除整个数据部分。这意味着混合了过期和未过期行的部分将不会被删除；只有每行都已达到其 TTL 过期时间的部分才会被删除。通俗的讲就是需要等每条数据都过期时，才会进行删除操作而且不会产生重写数据和merge,减少了任务对系统性能的影响 ttl_only_drop_parts设置为false（默认值）时，ClickHouse 会正常处理 TTL 操作，这可能涉及删除过期的行并重写部分内容，即使只有部分行过期。 通俗的讲就是只要有检查出过期的数据，就会进行删除操作删除部分过期的数据，而没有过期的数据则会被重写和merge，相对来说比较浪费系统资源

AB表的切换

RENAME TABLE qcc_bi.user_regist_report_data TO qcc_bi.user_regist_report_data_tmp ,
        qcc_bi.user_regist_report_data_latest TO qcc_bi.user_regist_report_data ,
        qcc_bi.user_regist_report_data_tmp TO qcc_bi.user_regist_report_data_latest ON CLUSTER  cluster_5shards_2replicas;

删除对应的数据(因为删除是异步的需要等待会)

ALTER TABLE applydata_bi_ods.pc_trajectory ON CLUSTER cluster_5shards_2replicas
DELETE WHERE uuid = '2e6dfda2-391a-4c00-89ea-71d87b405228';

-- 在所有节点执行优化
OPTIMIZE TABLE applydata_bi_ods.pc_trajectory FINAL 
ON CLUSTER cluster_5shards_2replicas;

存储优化

-- 查看表大小
SELECT formatReadableSize(sum(bytes)) as size
FROM system.parts
WHERE active AND table = 'pc_trajectory';

-- 修改数据过期以后多少天进行压缩
ALTER TABLE applydata_bi_ods.pc_trajectory on cluster test_ck_cluster
MODIFY TTL day + INTERVAL 365 DAY RECOMPRESS CODEC(ZSTD(1));

-- 压缩立即生效
OPTIMIZE TABLE applydata_bi_ods.pc_trajectory on cluster test_ck_cluster FINAL;

--删除对应的ttl
ALTER TABLE applydata_bi_ods.pc_trajectory ON CLUSTER test_ck_cluster REMOVE TTL;
这一波操作可以节省百分之50的存储的吧

官方文档说明
https://clickhouse.com/docs/community-wisdom/cost-optimization
https://clickhouse.com/docs/observability/managing-data#recompressing-data