网站设计公司深圳/中国十大电商平台排名

clickhouse分布式表tencent_table_20231208_DIST，本地表tencent_table_20231208_local；
30台clickhouse存储服务器；

本地表：tencent_table_20231208_local

 CREATE TABLE tencent_sz.tencent_table_20231208_local
(`id` Int64 DEFAULT CAST(0, 'Int64'),`pid` Int64,`user` String,`host` String,`db` Nullable(String),`COMMAND` String,`TIME` Int64,`STATE` Nullable(String),`source_sql` Nullable(String),`INFO` Nullable(String),`create_time` DateTime DEFAULT now(),`collect_create_time` DateTime,`instance_name` String,`source_ip` String,`source_port` UInt32,`_date` Date DEFAULT toDate(create_time),`scan_row` Int64 DEFAULT CAST(0, 'Int64'),`use_key` Nullable(String),`crc32` Int64 DEFAULT CAST(0, 'Int64')
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/tencent_table_20231208_local', '{replica}')
PARTITION BY _date
ORDER BY (source_ip, create_time)
TTL _date + toIntervalDay(14)
SETTINGS index_granularity = 8192

分布式表：

CREATE TABLE tencent_sz.tencent_table_20231208_DIST
(`id` Int64 DEFAULT CAST(0, 'Int64'),`pid` Int64,`user` String,`host` String,`db` Nullable(String),`COMMAND` String,`TIME` Int64,`STATE` Nullable(String),`source_sql` Nullable(String),`INFO` Nullable(String),`create_time` DateTime DEFAULT now(),`collect_create_time` DateTime,`instance_name` String,`source_ip` String,`source_port` UInt32,`_date` Date DEFAULT toDate(create_time),`scan_row` Int64 DEFAULT CAST(0, 'Int64'),`use_key` Nullable(String),`crc32` Int64 DEFAULT CAST(0, 'Int64')
)
ENGINE = Distributed('clk_dba', 'tencent_sz', 'tencent_table_20231208_local', rand())

一段时间后，数据过大，手动删除；
先查询该表的所有分区：

SELECT　　database,　　table,　　partition,　　name,　　active FROM system.parts WHERE table = 'tencent_table_20231208_local'

输出：

┌─database─────┬─table──────────────────────────┬─partition──┬─name─────────────────┬─active─┐
│ tencent_sz │ tencent_table_20231208_local │ 2023-12-07 │ 20231207_4740_5083_4 │      1 │
│ tencent_sz │ tencent_table_20231208_local │ 2023-12-07 │ 20231207_5084_5330_4 │      1 │
│ tencent_sz │ tencent_table_20231208_local │ 2023-12-07 │ 20231207_5331_5441_3 │      1 │
│ tencent_sz │ tencent_table_20231208_local │ 2023-12-07 │ 20231207_5442_5467_2 │      1 │
│ tencent_sz │ tencent_table_20231208_local │ 2023-12-07 │ 20231207_5468_5468_0 │      1 │
│ tencent_sz │ tencent_table_20231208_local │ 2023-12-07 │ 20231207_5469_5469_0 │      1 │
│ tencent_sz │ tencent_table_20231208_local │ 2023-12-07 │ 20231207_5470_5470_0 │      1 │
│ tencent_sz │ tencent_table_20231208_local │ 2023-12-08 │ 20231208_0_611_4     │      1 │

删除分区名：

alter table tencent_sz.tencent_table_20231208_local DROP PARTITION '2023-12-07';