clickhouse 查询表或列的磁盘占用大小
通过系统表 system.parts 我们可以查询MergeTree表的磁盘占用信息,而通过 system_part_columns 表可以查询具体字段的磁盘占用信息
system.parts
示例:以下SQL 查询所有表的 磁盘压缩大小 和 原始未压缩磁盘占用、压缩比等信息文章来源:https://www.toymoban.com/news/detail-832523.html
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 查询所有表的所有字段的磁盘压缩大小 和 原始未压缩磁盘占用信息文章来源地址https://www.toymoban.com/news/detail-832523.html
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;
到了这里,关于【clickhouse笔记】 查询表或列的磁盘占用大小的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!