积少成多, 聚沙成塔

Kiritow's Homepage

Clickhouse常用命令

Posted on Edited on Disqus:

查看表压缩率

1
2
3
4
5
6
7
8
9
10
11
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

统计字段压缩率

1
2
3
4
5
6
7
8
9
10
11
12
13
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

修改表的TTL

表本身是类似这样分区的. 注意建表的时候需要加 ttl_only_drop_parts = 1 否则 clickhouse 运行TTL清理的时候可能会执行分区内数据删除, 引起不必要的读写.

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE <table_name>
(
    ...
    `start_time` DateTime64(3, 'Asia/Singapore') CODEC(DoubleDelta, ZSTD(3)),
    ...
)
ENGINE = MergeTree
PARTITION BY toStartOfHour(start_time)
ORDER BY start_time
TTL toDateTime(start_time) + toIntervalDay(90)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1

现在需要缩短 TTL 到 80 天, 操作如下

1
2
3
4
5
6
7
8
-- 防止 Clickhouse 同步执行 TTL Materialize
set materialize_ttl_after_modify=0;

-- 修改TTL, 只修改schema不会立刻开始删除数据
alter table <table_name> modify ttl toDateTime(start_time) + toIntervalDay(80);

-- 在后台开始执行数据清理 (mutation). 不加 settings 会同步等待 TTL Materialized.
alter table <table_name> materialize ttl settings mutations_sync=0;

当然也可以直接drop掉表的某个分区, 这个操作是实时的.

1
alter table <table_name> drop partition '2026092801';