记删除CK不彻底问题 及 新增表TTL机制

问题背景：对CK表进行删除时，发现无法彻底删除，并报错如下：

同时也会有下面的报错信息：

解决过程：

1. 确认CK 节点是否健康存活，select * from system.clusters 可以查看；
2. 确认CK元数据是否删除干净；如果未删除干净 需要清理；
3. 确认Zookeeper 是否删除干净；如果未删除干净 需要清理，并重启 防止元数据还在内存中；记得一定要重启zk；

如果是表中数据量过大，默认CK是将超过50G 删除会不生效；但CK 删除数据是异步的 并不会报错提示数据未删除失败；

这里有一种办法是找到ClickHouse安装目录下的配置文件，通常是/etc/clickhouse-server/config.xml。修改 并开启这两处配置，同时one by one 一台台节点修改重启，不建议大批量操作 防止业务数据查询受到影响。

重建表并添加TTL机制：

local表：

CREATE TABLE db_name.table_name_local on cluster ck_cluster_name (
`fdate` Int64 COMMENT '天分区', 
`s_hour` String COMMENT '数据小时', 
 ...
`data_day`        String COMMENT '数据日期字符型'
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/table_name_local', '{replica}') 
PARTITION BY fdate ORDER BY (fdate, s_hour) 
TTL toDate(data_day) + toIntervalDay(7)
SETTINGS index_granularity = 8192, storage_policy = 'ssd_to_hdd' 

?all表：

CREATE TABLE db_name.table_name_local on cluster ck_cluster_name (
    `fdate` Int64 COMMENT '天分区', 
    `s_hour` String COMMENT '数据小时', 
    ...
    `data_day`        String COMMENT '数据日期字符型'
) ENGINE = Distributed(ck_cluster_name, db_name, table_name_local, rand())

附：常用CK SQL

-- 删除数据
ALTER TABLE db_name.table_name_local ON CLUSTER ck_cluster_name DELETE WHERE fdate<=20230915;

-- 清空数据
truncate table db_name.table_name_local ON CLUSTER ck_cluster_name;

-- 查看CK表存储大小及表中数据量并按倒序排序
select 
  database,table,formatReadableSize(size) as size1 ,rows
  from 
(
select database,
       table,
       sum(bytes) as size,
       sum(rows) as rows,
       min(min_date) as min_date,
       max(max_date) as max_date,
       sum(bytes_on_disk) as bytes_on_disk,
       sum(data_uncompressed_bytes) as data_uncompressed_bytes,
       sum(data_compressed_bytes) as data_compressed_bytes,
       (data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,
       max_date - min_date as days,
       size / (max_date - min_date) as avgDaySize
  from system.parts
 where active
 group by database, table 
) a 
 order by size desc;

-- 查询CK 近半年查询是次数
select table,cnt
from
(
select table,count(*) as cnt
from 
(
SELECT 
        query, 
         arrayJoin(extractAll(query, 'FROM\\s+(\\S+)\.(\\S+)')) as table
    FROM system.query_log
    WHERE
        query_start_time >= subtractMonths(now(), 6)  
        AND query LIKE 'SELECT %' 
        ) a group by table
) b order by cnt desc limit 100;

参考文章：https://blog.csdn.net/weixin_43464964/article/details/117459652