With system tables, you can learn the details of the tables and columns on ClickHouse with the following queries. In this article, we will explain two system tables and give examples.
First, system.parts table. It contains information about parts of MergeTree tables.
Second, system.part_columns table. It contains information about parts and columns of MergeTree tables.
Example;
Lets give and example and examine results of the queries from both system tables.
How to Check Table Size
SELECT
database,
table,
formatReadableSize(sum(data_compressed_bytes) AS size) AS compressed,
formatReadableSize(sum(data_uncompressed_bytes) AS usize) AS uncompressed,
round(usize / size, 2) AS compr_rate,
sum(rows) AS rows,
count() AS part_count
FROM system.parts
WHERE (active = 1) AND (database LIKE '%') AND (table LIKE '%')
GROUP BY
database,
table
ORDER BY size DESC;
Result;
As you can see in the query output, there are database, table, compressed and uncompressed data is shown below. Also you can see how many rows tables have. Also there is a compression ratio column which is show the ratio between the uncompressed size and compressed size. For more information about compression, visit here.
┌─database─┬─table─────────────────────┬─compressed─┬─uncompressed─┬─compr_rate─┬─────rows─┬─part_count─┐ │ default │ uk_price_paid │ 232.27 MiB │ 767.40 MiB │ 3.3 │ 27176256 │ 1 │ │ system │ trace_log │ 181.46 MiB │ 3.66 GiB │ 20.63 │ 12233516 │ 13 │ │ system │ metric_log_0 │ 37.59 MiB │ 1.39 GiB │ 37.99 │ 486595 │ 2 │ │ system │ asynchronous_metric_log_0 │ 34.18 MiB │ 1.03 GiB │ 30.96 │ 73972421 │ 3 │ │ system │ metric_log_4 │ 33.73 MiB │ 1.24 GiB │ 37.7 │ 433306 │ 8 │ │ system │ asynchronous_metric_log_4 │ 30.67 MiB │ 931.32 MiB │ 30.36 │ 65088657 │ 9 │ │ system │ metric_log │ 12.72 MiB │ 477.83 MiB │ 37.56 │ 162781 │ 7 │ │ system │ asynchronous_metric_log │ 11.46 MiB │ 347.15 MiB │ 30.28 │ 24260705 │ 7 │ │ system │ metric_log_2 │ 8.54 MiB │ 313.58 MiB │ 36.71 │ 106828 │ 1 │ │ system │ asynchronous_metric_log_2 │ 7.50 MiB │ 227.80 MiB │ 30.36 │ 15920077 │ 2 │ │ system │ asynchronous_metric_log_1 │ 92.97 KiB │ 1.02 MiB │ 11.27 │ 45852 │ 1 │ │ system │ query_log │ 47.33 KiB │ 208.81 KiB │ 4.41 │ 151 │ 4 │ │ system │ metric_log_1 │ 43.43 KiB │ 788.93 KiB │ 18.17 │ 310 │ 2 │ │ system │ asynchronous_metric_log_3 │ 34.50 KiB │ 373.26 KiB │ 10.82 │ 16348 │ 1 │ │ system │ asynchronous_metric_log_5 │ 23.09 KiB │ 199.01 KiB │ 8.62 │ 8592 │ 1 │ │ system │ metric_log_3 │ 18.29 KiB │ 282.49 KiB │ 15.45 │ 111 │ 1 │ │ system │ metric_log_5 │ 15.21 KiB │ 150.15 KiB │ 9.87 │ 59 │ 1 │ │ system │ part_log │ 2.77 KiB │ 11.47 KiB │ 4.14 │ 61 │ 1 │ └──────────┴───────────────────────────┴────────────┴──────────────┴────────────┴──────────┴────────────┘
How to Check Column Size
SELECT
database,
table,
column,
formatReadableSize(sum(column_data_compressed_bytes) AS size) AS compressed,
formatReadableSize(sum(column_data_uncompressed_bytes) AS usize) AS uncompressed,
round(usize / size, 2) AS compr_ratio,
sum(rows) rows_cnt,
round(usize / rows_cnt, 2) avg_row_size
FROM system.parts_columns
WHERE (active = 1) AND (database LIKE '%') AND (table LIKE '%')
GROUP BY
database,
table,
column
ORDER BY size DESC;
Result;
Like the table output, we get similar results in the column output.
─database─┬─table─────────────────────┬─column────────────────────────────────────────────────────────┬─compressed─┬─uncompressed─┬─compr_ratio─┬─rows_cnt─┬─avg_row_size─┐ │ default │ uk_price_paid │ price │ 75.45 MiB │ 103.67 MiB │ 1.37 │ 27176256 │ 4 │ │ system │ trace_log │ timestamp_ns │ 69.23 MiB │ 93.30 MiB │ 1.35 │ 12230640 │ 8 │ │ default │ uk_price_paid │ date │ 52.00 MiB │ 51.83 MiB │ 1 │ 27176256 │ 2 │ │ system │ trace_log │ event_time_microseconds │ 51.97 MiB │ 93.30 MiB │ 1.8 │ 12230640 │ 8 │ │ default │ uk_price_paid │ addr1 │ 42.19 MiB │ 120.61 MiB │ 2.86 │ 27176256 │ 4.65 │ │ system │ asynchronous_metric_log_0 │ value │ 29.87 MiB │ 564.36 MiB │ 18.89 │ 73972421 │ 8 │ │ system │ trace_log │ trace │ 28.74 MiB │ 2.48 GiB │ 88.28 │ 12230640 │ 217.51 │ │ system │ asynchronous_metric_log_4 │ value │ 26.79 MiB │ 496.57 MiB │ 18.54 │ 65088657 │ 8 │ │ system │ trace_log │ size │ 21.32 MiB │ 93.30 MiB │ 4.38 │ 12230640 │ 8 │ │ default │ uk_price_paid │ locality │ 12.67 MiB │ 51.02 MiB │ 4.03 │ 27176256 │ 1.97 │ │ default │ uk_price_paid │ street │ 10.77 MiB │ 60.59 MiB │ 5.63 │ 27176256 │ 2.34 │ │ system │ asynchronous_metric_log │ value │ 10.12 MiB │ 184.23 MiB │ 18.2 │ 24152159 │ 8 │ │ default │ uk_price_paid │ type │ 9.87 MiB │ 25.92 MiB │ 2.63 │ 27176256 │ 1 │ │ default │ uk_price_paid │ addr2 │ 7.81 MiB │ 45.57 MiB │ 5.83 │ 27176256 │ 1.76 │ │ system │ asynchronous_metric_log_2 │ value │ 6.56 MiB │ 121.42 MiB │ 18.5 │ 15920077 │ 8 │ │ system │ trace_log │ query_id │ 6.46 MiB │ 703.49 MiB │ 108.88 │ 12230640 │ 60.31 │ │ default │ uk_price_paid │ postcode2 │ 6.03 MiB │ 51.85 MiB │ 8.6 │ 27176256 │ 2 │ │ system │ asynchronous_metric_log_0 │ event_time │ 3.97 MiB │ 282.18 MiB │ 71.02 │ 73972421 │ 4 │ │ default │ uk_price_paid │ is_new │ 3.95 MiB │ 25.92 MiB │ 6.55 │ 27176256 │ 1 │ │ system │ asynchronous_metric_log_4 │ event_time │ 3.61 MiB │ 248.29 MiB │ 68.79 │ 65088657 │ 4 │ │ default │ uk_price_paid │ category │ 3.51 MiB │ 25.92 MiB │ 7.37 │ 27176256 │ 1 │ │ default │ uk_price_paid │ duration │ 3.23 MiB │ 25.92 MiB │ 8.02 │ 27176256 │ 1 │ │ system │ metric_log_0 │ event_time_microseconds │ 2.83 MiB │ 3.71 MiB │ 1.31 │ 486595 │ 8 │ │ default │ uk_price_paid │ district │ 2.71 MiB │ 51.90 MiB │ 19.18 │ 27176256 │ 2 │ │ system │ metric_log_4 │ event_time_microseconds │ 2.52 MiB │ 3.31 MiB │ 1.31 │ 433306 │ 8 │ │ system │ trace_log │ thread_id │ 2.00 MiB │ 93.30 MiB │ 46.69 │ 12230640 │ 8 │ │ system │ metric_log_0 │ event_time │ 1.86 MiB │ 1.86 MiB │ 1 │ 486595 │ 4