instructions

The information_SCHEMA database in mysql holds information about all databases of the mysql server, while in Clickhouse you can view the capacity, number of rows, compression rate, and partition information of the Clickhouse database and tables using System.parts. This is illustrated here by testing the database.

1. View the database capacity, number of rows, and compression ratio

SELECT sum(rows) AS 'total rows', formatReadableSize(sum(data_uncompressed_bytes)) AS' original size ', FormatReadableSize (sum(data_compressed_bytes)) AS 'compress size', round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 2) AS 'compression rate' FROM system. Parts chrysene ── total number of rows ─┬─ original size ─┬─ compression size ─┬─ compression rate ─ 326819026 │ 77.15 GiB │ 5.75 GiB │ 7 │ └ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ┘ 1 rows in the set. The Elapsed: Processed 1.04 thousand Rows, 520.93 KB (21.95 Thousand Rows /s., 11.02 MB/s.)Copy the code

2. View the table capacity, number of rows, and compression ratio

SELECT table AS 'name', sum(rows) AS 'total number of rows', formatReadableSize(sum(data_uncompressed_bytes)) AS' original size ', FormatReadableSize (sum(data_compressed_bytes)) AS 'compress size', round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 1) AS 'compress' FROM system. Parts WHERE table IN ('temp_1') GROUP BY table chrysene ──┬── ─┬─ original size ─┬── ─┬── compress rate ─ temp_1 │ 60.04 MiB 3127523 838.21 MiB │ │ │ │ 7 └ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ┘ 1 rows in the set. The Elapsed: 0.008 SEC.Copy the code

3. View table partition information

SELECT partition AS 'partition', sum(rows) AS 'total number of rows', FormatReadableSize (sum(data_compressed_bytes)) AS 'original size', formatReadableSize(sum(data_compressed_bytes)) AS 'compressed size', round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, Parts WHERE (database IN ('default')) AND (table IN ('temp_1')) AND (partition LIKE '2019-12-%') GROUP BY partition ORDER BY partition ASC chrysene ──┬─ total number of rows ─┬─ original size ─┬─ compression size ─┬─ compression rate ─ 2019-12-01 │ 24 │ 6.17 KiB │ 2.51 KiB │ 2 │ 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 │ │ │ 2019-12-07 │ 120 │ 32.75 KiB │ 3.86 KiB │ 12 │ └ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ┘ 7 rows in the set. The Elapsed: 0.005 SEC.Copy the code

4. View the fields in the data table

SELECT column AS 'name', any(type) AS 'type', formatReadableSize(sum(column_data_uncompressed_bytes)) AS 'original size', FormatReadableSize (sum(column_data_compressed_bytes)) AS 'compress size', Sum (rows) AS 'rows' FROM system.parts_columns WHERE (database = 'default') AND (table = 'temp_1') GROUP BY column ORDER BY Column ASC ┌ ─ field name ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┬ type ─ ─ ─ ─ ─ ─ ┬ original size ─ ─ ─ ─ ┬ compression size ─ ─ ─ ─ ┬ ─ ─ ─ ─ rows ─ ┐ │ │ a String 23.83 MiB │ │ 134.13 KiB 3127523 │ │ │ │ b String │ 19.02 MiB │ 127.72 KiB │ 3127523 │ C │ String │ 5.97 MiB │ 49.09 KiB │ 3127523 │ D │ String │ 3.95 MiB │ │ totalDate │ DateTime │ 11.93 MiB │ 1.26 MiB │ totalDate │ DateTime │ 11.93 MiB │ 1.26 MiB │ 3127523 │ └ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┴ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┘Copy the code