1，手动删除分区数据

测试clickhouse 分区表：查询分区，删除分区

• engine: ReplicatedReplacingMergeTree, MergeTree

###1, 创建分区表 (时间格式化函数：toYYYYMMDD, toYYYYMM )
ck01 :)  CREATE TABLE tb_test_partitions
(
    `id` Int64,
    `saleDate` Date,
    `saleMoney` Float32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(saleDate)
ORDER BY (saleMoney);

ck01 :)  insert into tb_test_partitions values (1,'2020-10-02 14:15:23',300.50),(2,'2020-11-02 14:15:23',100.50),(3,'2020-11-02 14:15:23',200.50);

ck01 :)  select * from tb_test_partitions;
┌─id─┬───saleDate─┬─saleMoney─┐
│  2 │ 2020-11-02 │     100.5 │
│  3 │ 2020-11-02 │     200.5 │
└────┴────────────┴───────────┘
┌─id─┬───saleDate─┬─saleMoney─┐
│  1 │ 2020-10-02 │     300.5 │
└────┴────────────┴───────────┘

###2, 查看分区信息
[root@ck01 ~]# ll /app/pocdata/ck/clickhouse/data/default/tb_test_partitions/
total 16
drwxr-x--- 2 clickhouse clickhouse 4096 Dec  8 10:02 202010_3_3_0
drwxr-x--- 2 clickhouse clickhouse 4096 Dec  8 10:02 202011_4_4_0
drwxr-x--- 2 clickhouse clickhouse 4096 Dec  8 10:01 detached
-rw-r----- 1 clickhouse clickhouse    1 Dec  8 10:01 format_version.txt

ck01 :) select database,table,partition,name, bytes_on_disk  from system.parts where table='tb_test_partitions';
┌─database─┬─table──────────────┬─partition─┬─name─────────┬─bytes_on_disk─┐
│ default  │ tb_test_partitions │ 202010    │ 202010_3_3_0 │           221 │
│ default  │ tb_test_partitions │ 202011    │ 202011_4_4_0 │           232 │
└──────────┴────────────────────┴───────────┴──────────────┴───────────────┘

###3, 删除某分区
ck01 :) alter table tb_test_partitions drop partition 202010;

ck01 :) select database,table,partition,name, bytes_on_disk  from system.parts where table='tb_test_partitions';
┌─database─┬─table──────────────┬─partition─┬─name─────────┬─bytes_on_disk─┐
│ default  │ tb_test_partitions │ 202011    │ 202011_4_4_0 │           232 │
└──────────┴────────────────────┴───────────┴──────────────┴───────────────┘

###4, 查看表数据
ck01 :) select * from tb_test_partitions;
┌─id─┬───saleDate─┬─saleMoney─┐
│  2 │ 2020-11-02 │     100.5 │
│  3 │ 2020-11-02 │     200.5 │
└────┴────────────┴───────────┘

1.1 配置定时任务，清理过期表分区

删除分布式表的旧分区：先查分区 SELECT DISTINCT partition FROM cluster('集群名', system.parts) WHERE database = '库名' AND table = '表名' AND partition < '最小分区如20260401' ORDER BY partition

[root@ck02 oper]# cat /opt/del_ck.sh
#!/bin/bash
# 删除多分片上的分区表
CLICKHOUSE_HOST="ck01"
CLICKHOUSE_PORT="9000"
CLICKHOUSE_USER="default"
CLICKHOUSE_PASSWORD="xxxx"
CLUSTER='ck'

DATABASE_NAME="mydb"
TABLE_NAME="ck_test1"
RETENTION_DAYS=180
LOG_FILE="/var/log/clickhouse_clean.log"

# 计算保留截止日期（格式：YYYYMMDD）
RETENTION_DATE=$(date -d "$RETENTION_DAYS days ago" +%Y%m%d)

# 日志函数
log() {
    #echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" 
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" >> $LOG_FILE
}

# 获取需删除的分区列表
get_partitions() {
    log "开始查询需要删除的分区..."
    query="SELECT DISTINCT partition    FROM cluster('$CLUSTER', system.parts)  WHERE database = '$DATABASE_NAME' AND table = '$TABLE_NAME' AND  partition < '$RETENTION_DATE' ORDER BY partition"
    #log $query
    partitions=$(clickhouse-client --host=$CLICKHOUSE_HOST --port=$CLICKHOUSE_PORT \
        --user=$CLICKHOUSE_USER --password=$CLICKHOUSE_PASSWORD \
        --query="$query") 
    
    if [ -z "$partitions" ]; then
        log "未找到需要删除的分区"
        exit 0
    fi
    echo $partitions
}

# 执行删除操作
drop_partitions() {
    local parts=($1)
    for part in "${parts[@]}"; do
        log "正在删除分区: $part"
        cmd="ALTER TABLE $DATABASE_NAME.$TABLE_NAME ON CLUSTER $CLUSTER  DROP PARTITION '$part'"
        clickhouse-client --host=$CLICKHOUSE_HOST --port=$CLICKHOUSE_PORT  --user=$CLICKHOUSE_USER --password=$CLICKHOUSE_PASSWORD  --query="$cmd" 2>>$LOG_FILE
        
        if [ $? -ne 0 ]; then
            log "删除分区 $part 失败，检查ZK状态或重试"
            exit 1
        else
            log "分区 $part 删除成功"
        fi
    done
}

# 主流程
main() {
    log "启动分区清理任务（保留$RETENTION_DAYS天数据）"
    partitions=$(get_partitions)
    if [ -n "$partitions" ]; then
        drop_partitions "$partitions"
    fi
}

main

2，设置表数据生命周期

• toIntervalMinute：n分过期
• toIntervalDay：n天过期
• toIntervalMonth：n月过期

CREATE TABLE default.tb_test_partitions3
(
    `id` Int64,
    `saleDate` Date,
    `saleMoney` Float32
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(saleDate)
ORDER BY saleMoney
TTL toDate(saleDate) + toIntervalMinute(10) //10分钟后过期

#验证：插入一条当前时间的数据，过一段时间，再来查找该数据：如果没有查询到，则被过期删除了

修改表的生命周期

• 验证是否生效: show create table xx;

#1，在单个主机范围修改：需要在所有主机执行一次
ALTER TABLE tb_test_partitions MODIFY TTL toDate(`saleDate`) + toIntervalMinute(1);
#等价于：ALTER TABLE tb_test_partitions MODIFY TTL toDate(`saleDate`) + INTERVAL 1 MINUTE;

#2，在集群范围修改：只需要在任意主机执行一次
alter table tb_test_partitions on cluster ck_cluster_1 MODIFY TTL toDate(`saleDate`) + toIntervalDay(90);