【clickhouse笔记】查询表或列的磁盘占用大小

最新推荐文章于 2024-07-14 07:00:00 发布

卓修武

最新推荐文章于 2024-07-14 07:00:00 发布

阅读量1.4k

点赞数 6

分类专栏： clickhouse 文章标签： clickhouse 笔记数据库

本文链接：https://blog.csdn.net/zhuoxiuwu/article/details/136186717

版权

clickhouse 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文介绍了如何使用ClickHouse的system.parts和system.part_columns系统表来查询MergeTree表的磁盘占用情况，包括总磁盘占用、压缩大小、压缩比以及字段级别的详细信息。

摘要由CSDN通过智能技术生成

clickhouse 查询表或列的磁盘占用大小

通过系统表 system.parts 我们可以查询MergeTree表的磁盘占用信息，而通过 system_part_columns 表可以查询具体字段的磁盘占用信息

system.parts

示例:以下SQL 查询所有表的磁盘压缩大小和原始未压缩磁盘占用、压缩比等信息

SELECT
    database,
    table,
    formatReadableSize(sum(data_compressed_bytes) AS size) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes) AS usize) AS uncompressed,
    round(usize / size, 2) AS compr_rate,
    sum(rows) AS rows,
    count() AS part_count
FROM system.parts
WHERE (active = 1) AND (database LIKE '%') AND (table LIKE '%')
GROUP BY
    database,
    table
ORDER BY size DESC;

system.part_columns

示例:以下SQL 查询所有表的所有字段的磁盘压缩大小和原始未压缩磁盘占用信息

SELECT
    database,
    table,
    column,
    formatReadableSize(sum(column_data_compressed_bytes) AS size) AS compressed,
    formatReadableSize(sum(column_data_uncompressed_bytes) AS usize) AS uncompressed,
    round(usize / size, 2) AS compr_ratio,
    sum(rows) rows_cnt,
    round(usize / rows_cnt, 2) avg_row_size
FROM system.parts_columns
WHERE (active = 1) AND (database LIKE '%') AND (table LIKE '%')
GROUP BY
    database,
    table,
    column
ORDER BY size DESC;