#!/usr/bin/env python3 """ 第 11 章 · 分区与 TTL 实操脚本 演示: 1. 灌入跨多月的数据 → 看分区自动拆成多个目录 (system.parts) 2. 演示分区裁剪 (有/无 WHERE event_date) 的 read_rows 差距 3. DROP / DETACH / ATTACH PARTITION 的耗时与效果 4. 用 OPTIMIZE PARTITION FINAL 触发 TTL，观察列级 TTL 把 debug_log 置空 5. MOVE PART 演示（仅在配置了多盘时生效） 前置: 1. clickhouse-client --multiquery < ../init.sql 2. pip install clickhouse-connect 用法: python3 ttl_demo.py python3 ttl_demo.py --rows 200000 """ from __future__ import annotations import argparse import random import time from datetime import date, datetime, timedelta import clickhouse_connect def banner(title: str) -> None: print("\n" + "=" * 72) print(f" {title}") print("=" * 72) def show_parts(client, table: str, only_active: bool = True) -> None: cond = "AND active" if only_active else "" sql = f""" SELECT partition, name, disk_name, rows, formatReadableSize(bytes_on_disk) AS size, toString(min_time)::String AS mn, toString(max_time)::String AS mx FROM system.parts WHERE database = 'learn_ck' AND table = '{table}' {cond} ORDER BY partition, name """ rs = client.query(sql).result_rows if not rs: print(f" (no {'active' if only_active else 'any'} parts in {table})") return print(f" {'partition':<10} {'name':<28} {'disk':<10} {'rows':>10} {'size':>10} range") for r in rs: print(f" {r[0]:<10} {r[1]:<28} {r[2]:<10} {r[3]:>10} {r[4]:>10} {r[5]} ~ {r[6]}") def seed_multi_month(client, total_rows: int) -> None: cols = ["event_date", "event_time", "user_id", "event_type", "payload", "debug_log"] today = date.today() # 跨 6 个月：今天、上月、再上月、... 各塞一部分 months_back = [0, 30, 60, 95, 130, 200] per = max(1, total_rows // len(months_back)) for back in months_back: rows = [] for _ in range(per): d = today - timedelta(days=back + random.randint(0, 5)) rows.append(( d, datetime(d.year, d.month, d.day, random.randint(0, 23), random.randint(0, 59)), random.randint(1, 100_000), random.choice(["click", "view", "buy", "share"]), "P" * random.randint(50, 200), "DEBUG-" + "X" * random.randint(20, 100), )) client.insert("learn_ck.ttl_events_log", rows, column_names=cols) print(f" inserted {len(rows)} rows for date {today - timedelta(days=back)} (back={back}d)") def demo_pruning(client) -> None: banner("Demo 2: 分区裁剪对比") sql_full = "SELECT count() FROM learn_ck.ttl_events_log" sql_pruned = ( "SELECT count() FROM learn_ck.ttl_events_log " f"WHERE event_date >= '{(date.today()-timedelta(days=15)).isoformat()}'" ) for label, sql in [("无 WHERE (全表扫)", sql_full), ("WHERE event_date >= today-15 (裁剪)", sql_pruned)]: # query_log 有几秒延迟，所以先 sync 一下 t0 = time.time() cnt = client.query(sql).result_rows[0][0] cost = (time.time() - t0) * 1000 # 取 EXPLAIN ESTIMATE 看预估读 part 数 est = client.query(f"EXPLAIN ESTIMATE {sql}").result_rows print(f"\n {label}") print(f" SQL : {sql}") print(f" rows : {cnt}") print(f" cost : {cost:.1f} ms") print(f" EXPLAIN ESTIMATE:") for row in est: print(f" {row}") def demo_partition_ops(client) -> None: banner("Demo 3: DROP / DETACH / ATTACH PARTITION") parts = client.query( "SELECT DISTINCT partition FROM system.parts " "WHERE database='learn_ck' AND table='ttl_events_log' AND active " "ORDER BY partition" ).result_rows if len(parts) < 2: print(" 分区不足 2 个，跳过该 demo") return # 选最老的一个分区演示 oldest = parts[0][0] print(f"\n 选定最老分区：{oldest}") # DETACH t0 = time.time() client.command(f"ALTER TABLE learn_ck.ttl_events_log DETACH PARTITION '{oldest}'") print(f" DETACH PARTITION '{oldest}' cost={1000*(time.time()-t0):.1f}ms") # 看 detached det = client.query( "SELECT partition_id, name FROM system.detached_parts " f"WHERE database='learn_ck' AND table='ttl_events_log' AND partition_id = '{oldest}'" ).result_rows print(f" detached_parts: {det}") # ATTACH 回来 t0 = time.time() client.command(f"ALTER TABLE learn_ck.ttl_events_log ATTACH PARTITION '{oldest}'") print(f" ATTACH PARTITION '{oldest}' cost={1000*(time.time()-t0):.1f}ms") # 演示 DROP（用最老的分区，假装不要了） if len(parts) >= 3: victim = parts[0][0] t0 = time.time() client.command(f"ALTER TABLE learn_ck.ttl_events_log DROP PARTITION '{victim}'") print(f" DROP PARTITION '{victim}' cost={1000*(time.time()-t0):.1f}ms (毫秒级!)") def demo_ttl(client) -> None: banner("Demo 4: 强制 OPTIMIZE 触发 TTL，看 debug_log 列被清空") # 先看一行老数据 sample = client.query( "SELECT event_date, length(debug_log) FROM learn_ck.ttl_events_log " "WHERE event_date < today() - 7 LIMIT 1" ).result_rows if not sample: print(" 没有 7 天前的数据，跳过") return print(f" TTL 触发前: event_date={sample[0][0]}, len(debug_log)={sample[0][1]}") # OPTIMIZE 触发 TTL parts = client.query( "SELECT DISTINCT partition FROM system.parts " "WHERE database='learn_ck' AND table='ttl_events_log' AND active " "ORDER BY partition LIMIT 1" ).result_rows if parts: p = parts[0][0] t0 = time.time() client.command(f"OPTIMIZE TABLE learn_ck.ttl_events_log PARTITION '{p}' FINAL") print(f" OPTIMIZE PARTITION '{p}' FINAL cost={1000*(time.time()-t0):.0f}ms") after = client.query( "SELECT event_date, length(debug_log) FROM learn_ck.ttl_events_log " "WHERE event_date < today() - 7 LIMIT 1" ).result_rows if after: print(f" TTL 触发后: event_date={after[0][0]}, len(debug_log)={after[0][1]} (列被置空)") def main() -> None: parser = argparse.ArgumentParser() parser.add_argument("--host", default="127.0.0.1") parser.add_argument("--port", type=int, default=8123) parser.add_argument("--user", default="default") parser.add_argument("--password", default="") parser.add_argument("--rows", type=int, default=120_000, help="总灌入行数") parser.add_argument("--skip-seed", action="store_true") args = parser.parse_args() client = clickhouse_connect.get_client( host=args.host, port=args.port, username=args.user, password=args.password, ) if not args.skip_seed: banner("Demo 1: 灌入跨多月数据 + 看分区自动拆") client.command("TRUNCATE TABLE learn_ck.ttl_events_log") seed_multi_month(client, args.rows) time.sleep(1) print("\n 现在的 active parts (注意每个月一个 partition 目录):") show_parts(client, "ttl_events_log") demo_pruning(client) demo_partition_ops(client) demo_ttl(client) banner("最终状态") show_parts(client, "ttl_events_log") print("\n看完后可以手动跑：") print(" ALTER TABLE learn_ck.ttl_events_log MOVE PART '' TO DISK 'cold';") print(" (前提：服务端配置了名为 cold 的 disk)") if __name__ == "__main__": main()