clickhouse查看数据库和表的容量信息

      在mysql中information_schema这个数据库中保存了mysql服务器所有数据库的信息，那在clickhouse如何查询库表信息呢？

 可以通过system.parts查看clickhouse数据库和表的容量大小、行数、压缩率以及分区信息。
     详见正面实操，只是其中一例，大家可以举一返三。

 

1、查看数据表容量、行数、压缩率

SELECT
    table AS `表名`,
    sum(rows) AS `总行数`,
    formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
    round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率`
FROM system.parts
WHERE (database IN ('mydatabasename'))
GROUP BY table
 

2、查看数据表分区信息

SELECT
 table AS `表名`,
    partition AS `分区`,
    sum(rows) AS `总行数`,
    formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
    round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率`
FROM system.parts
WHERE (database IN ('mydatabasename'))
GROUP BY table,partition
ORDER BY partition ASC
 

3、查看数据库容量、行数、压缩率

SELECT
    sum(rows) AS `总行数`,
    formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
    round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率`
FROM system.parts
WHERE (database IN ('mydatabasename'))
posted @ 2024-02-23 10:24  xuzhujack  阅读(275)  评论(0)  编辑  收藏  举报
;