Clickhouseテーブルサイズの取得方法

備忘録として残しておきます。

今後、頻繁に使うし確認に活躍してくれる内容だと思っています。

localhost :) SELECT
             concat(database, '.', table) AS table,
             formatReadableSize(sum(bytes)) AS size,
             sum(bytes) AS bytes_size,
             sum(rows) AS rows,
             max(modification_time) AS latest_modification,
             any(engine) AS engine
             FROM system.parts
             WHERE active
             GROUP BY
             database,
             table
             ORDER BY bytes_size DESC

SELECT
    concat(database, '.', table) AS table,
    formatReadableSize(sum(bytes)) AS size,
    sum(bytes) AS bytes_size,
    sum(rows) AS rows,
    max(modification_time) AS latest_modification,
    any(engine) AS engine
FROM system.parts
WHERE active
GROUP BY
    database,
    table
ORDER BY bytes_size DESC

Query id: 038f2169-e417-442d-8a7a-fc4e10e39658

lqtableqqqqqqqqqqqqqqqqqqqqqqqqqqwqsizeqqqqqqqwqbytes_sizeqwqqqqqqrowsqwqlatest_                                                                   modificationqwqengineqqqqk
x system.asynchronous_metric_log x 761.13 MiB x  798104827 x 373765141 x 2022-02                                                                   -12 20:55:33 x MergeTree x
x system.trace_log               x 340.66 MiB x  357209364 x  20406508 x 2022-02                                                                   -12 20:55:34 x MergeTree x
x system.metric_log              x 138.61 MiB x  145339100 x   1746377 x 2022-02                                                                   -12 20:55:32 x MergeTree x
x system.query_log               x 32.25 KiB  x      33026 x       103 x 2022-02                                                                   -12 20:54:59 x MergeTree x
x system.query_thread_log        x 27.22 KiB  x      27872 x       123 x 2022-02                                                                   -12 20:54:59 x MergeTree x
x system.session_log             x 10.87 KiB  x      11133 x       232 x 2022-02                                                                   -12 20:52:51 x MergeTree x
x system.part_log                x 1.60 KiB   x       1639 x         4 x 2022-02                                                                   -12 14:14:50 x MergeTree x
x backup.test                    x 904.00 B   x        904 x         4 x 2022-02                                                                   -12 14:36:43 x MergeTree x
mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqvqqqqqqqqqqqqvqqqqqqqqqqqqvqqqqqqqqqqqvqqqqqqqq                                                                   qqqqqqqqqqqqqvqqqqqqqqqqqj

8 rows in set. Elapsed: 0.016 sec.

localhost :)

We can check the size of ClickHouse tables with this query

Tables size in ClickHouse

Take it Easy!!

(Visited 205 times, 1 visits today)
The following two tabs change content below.
【好きなもの】 インフラ技術が好き。古いものが好き。 【生きてきたフィールド】 システム運用、ソフトウェア開発、ミドルウェア検証、OSSサポート、プリセールスエンジニア、プロジェクトマネジメント 【このサイトでの役割】 サイト管理者。