Skip to content

Instantly share code, notes, and snippets.

@tom-clickhouse
Created October 17, 2023 19:56
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save tom-clickhouse/beebf6b9f7876b4c100c4e234771b221 to your computer and use it in GitHub Desktop.
WITH
'default' AS db_name,
'pypi' AS table_name,
(
SELECT uuid
FROM system.tables
WHERE (database = db_name) AND (name = table_name)
) AS table_id,
-- get uncompressed table size from system.parts
-- system.part_log has a `bytes_uncompressed` column but it is always set to 0 for some reason
(SELECT
sum(data_uncompressed_bytes)
FROM system.parts
WHERE database = db_name AND table = table_name AND active) AS table_size_uncompressed_bytes,
(SELECT
sum(data_compressed_bytes)
FROM system.parts
WHERE database = db_name AND table = table_name AND active) AS table_size_compressed_bytes,
(SELECT
count()
FROM system.parts
WHERE database = db_name AND table = table_name AND active) AS table_active_parts
-- get number of initial parts from system.part_log
-- system.parts contains level-0 parts only for ~8 minutes after merges
SELECT
formatReadableQuantity(countIf(event_type = 'NewPart')) AS initial_parts,
formatReadableQuantity(maxIf(rows, event_type = 'NewPart')) AS rows_max,
formatReadableQuantity(minIf(rows, event_type = 'NewPart')) AS rows_min,
formatReadableQuantity(avgIf(rows, event_type = 'NewPart')) AS rows_avg,
formatReadableSize(table_size_uncompressed_bytes / countIf(event_type = 'NewPart')) AS size_uncompressed_avg,
formatReadableSize(maxIf(size_in_bytes, event_type = 'NewPart')) AS size_compressed_max,
formatReadableSize(minIf(size_in_bytes, event_type = 'NewPart')) AS size_compressed_min,
formatReadableSize(avgIf(size_in_bytes, event_type = 'NewPart')) AS size_compressed_avg,
formatReadableQuantity(sumIf(rows, event_type = 'NewPart')) AS rows_total,
formatReadableSize(table_size_uncompressed_bytes) AS size_total_uncompressed,
formatReadableSize(table_size_compressed_bytes) AS size_total_compressed,
formatReadableQuantity(table_active_parts) AS active_parts
FROM clusterAllReplicas(default, system.part_log)
WHERE table_uuid = table_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment