如何在ClickHouse中按月统计各表的数据推送大小（无分区场景）？

查询system.parts无法得到有效结果，核心原因是未分区表的part没有分区键标识，且system.parts仅记录当前存在的part（合并操作会清理旧part），无法反映历史按月的写入（推送）情况。以下是两种可行的解决方法：

方法一：利用system.part_log表（推荐） #

system.part_log记录了所有part的生命周期事件（创建、合并、删除等），其中Created事件对应INSERT操作生成的新part，可通过该表按时间分组统计写入数据大小。

查询语句 #

SELECT
    database,
    table,
    toStartOfMonth(event_time) AS month,
    sum(data_uncompressed_bytes) AS total_uncompressed_size, -- 原始数据大小
    sum(data_compressed_bytes) AS total_compressed_size -- 压缩后存储大小
FROM system.part_log
WHERE event_type = 'Created'
GROUP BY database, table, month
ORDER BY database, table, month;

注意事项 #

• system.part_log的日志保留时长由配置项part_log_retention_days控制，默认通常为7天。若需要长期统计，需修改该配置（例如设置为365），或者将system.part_log的数据定期同步到自定义的持久化表中。
• 该方法能精准统计每个写入操作产生的part大小，结果最准确。

方法二：利用system.query_log估算（备选） #

如果system.part_log未开启或保留时长不足，可通过system.query_log过滤INSERT语句，结合表的平均行大小估算写入数据量。

查询语句 #

SELECT
    t.database,
    t.table,
    toStartOfMonth(t.query_start_time) AS month,
    sum(t.rows_inserted) AS total_rows_inserted,
    -- 估算原始数据大小：总插入行数 × 表的平均行大小
    sum(t.rows_inserted) * (
        SELECT total_data_uncompressed_bytes / total_rows
        FROM system.tables
        WHERE database = t.database AND name = t.table
    ) AS estimated_uncompressed_size
FROM system.query_log t
WHERE t.query_type = 'Insert' AND t.is_initial_query = 1
GROUP BY t.database, t.table, month
ORDER BY t.database, t.table, month;

说明 #

• 该方法为估算值，因为表的平均行大小会随数据内容变化而波动，结果精度不如system.part_log。
• 需确保system.query_log已开启，且保留时长覆盖需要统计的时间段。

可选方案：修改表结构添加分区键 #

如果允许调整表结构，可以为表添加按月分区的分区键（例如基于表中已有的时间字段），示例：

ALTER TABLE your_database.your_table
    MODIFY PARTITION BY toYYYYMM(your_time_column);

修改后，后续写入的数据会按月份分区，此时即可通过system.parts表按月统计数据量，但该操作仅对修改后的新数据生效，历史数据仍需通过上述两种方法统计。

内容的提问来源于stack exchange，提问作者achyuth