Clickhouse常用命令

查看表压缩率

SELECT
    database,
    table,
    sum(rows) AS row_count, 
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed, formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 2) AS compression_ratio
FROM 
    system.parts 
WHERE database != 'system' and active
GROUP BY database, table
ORDER BY compression_ratio ASC

统计字段压缩率

SELECT
    name,
    table,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed, 
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    sum(data_compressed_bytes) as compressed_bytes,
    round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 2) AS compression_ratio
FROM 
    system.columns
WHERE
    database = '...' and table = '...'
GROUP BY table, name
ORDER BY name, compressed_bytes ASC