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'))