clickhouse如何查看数据库表的压缩比

在clickhouse，我们可以通过在default库下 system.parts查看clickhouse数据库和表的容量大小、行数、压缩率以及分区信息。

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

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

查询结果如下：
┌────总行数─┬─原始大小──┬─压缩大小─┬─压缩率─┐
│ 326819026 │ 77.15 GiB │ 5.75 GiB │      7 │
└───────────┴───────────┴──────────┴────────┘

1 rows in set. Elapsed: 0.047 sec. Processed 1.04 thousand rows, 520.93 KB (21.95 thousand rows/s., 
11.02 MB/s.)

2.查看数据库某个表的容量、行数、压缩率

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 table IN ('event')
GROUP BY table

查询结果如下：

┌─表名───┬──总行数─┬─原始大小───┬─压缩大小──┬─压缩率─┐
│ event │ 3127523 │ 838.21 MiB │ 60.04 MiB │      7 │
└────────┴─────────┴────────────┴───────────┴────────┘

1 rows in set. Elapsed: 0.008 sec.

3.查看数据库表某些分区内的容量、行数、压缩率

SELECT 
    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 ('default')) AND (table IN ('temp_1')) AND (partition LIKE '2019-12-%')
GROUP BY partition
ORDER BY partition ASC


查询结果如下：
┌─分区───────┬─总行数─┬─原始大小──┬─压缩大小───┬─压缩率─┐
│ 2019-12-01 │     24 │ 6.17 KiB  │ 2.51 KiB   │     41 │
│ 2019-12-02 │   9215 │ 2.45 MiB  │ 209.74 KiB │      8 │
│ 2019-12-03 │  17265 │ 4.46 MiB  │ 453.78 KiB │     10 │
│ 2019-12-04 │  27741 │ 7.34 MiB  │ 677.25 KiB │      9 │
│ 2019-12-05 │  31500 │ 8.98 MiB  │ 469.30 KiB │      5 │
│ 2019-12-06 │    157 │ 37.50 KiB │ 4.95 KiB   │     13 │
│ 2019-12-07 │    110 │ 32.75 KiB │ 3.86 KiB   │     12 │
└────────────┴────────┴───────────┴────────────┴────────┘

7 rows in set. Elapsed: 0.005 sec. 

注意

以上查询可以争对本地表，临时表，分布式表是无法查询的。