ClickHouse Schema Design Guide

CREATE TABLE insurance.ein3
(
    `filename`          LowCardinality(String)                CODEC(ZSTD(3)),
    `provider_group_id` LowCardinality(String)                CODEC(ZSTD(3)),
    `tin_type`          Nullable(Enum8('ein' = 1, 'npi' = 2)) CODEC(ZSTD(3)),
    `tin_value`         LowCardinality(Nullable(String))      CODEC(ZSTD(3)),
    `npi`               Array(Nullable(UInt64))               CODEC(ZSTD(3))
)
ENGINE = MergeTree
ORDER BY (filename, tin_type, tin_value, npi, provider_group_id)

SETTINGS allow_nullable_key = 1;

SELECT
	formatReadableQuantity(uniq(filename))          AS filename,
	formatReadableQuantity(uniq(provider_group_id)) AS provider_group_id,
	formatReadableQuantity(uniq(tin_type))          AS tin_type,
	formatReadableQuantity(uniq(tin_value))         AS tin_value,
	formatReadableQuantity(uniq(npi))               AS npi
FROM ein3
FORMAT Vertical;

--- sample output

SELECT
    formatReadableQuantity(uniq(filename)) AS filename,
    formatReadableQuantity(uniq(provider_group_id)) AS provider_group_id,
    formatReadableQuantity(uniq(tin_type)) AS tin_type,
    formatReadableQuantity(uniq(tin_value)) AS tin_value,
    formatReadableQuantity(uniq(npi)) AS npi
FROM ein3
FORMAT Vertical;

Query id: 2101c9f0-1eb9-4a78-bbd2-42b9fd64c142

Row 1:
──────
filename:          29.00
provider_group_id: 23.12 million
tin_type:          2.00
tin_value:         476.97 thousand
npi:               820.06 thousand

1 row in set. Elapsed: 17.781 sec. Processed 163.89 million rows, 8.75 GB (9.22 million rows/s., 491.86 MB/s.)

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 'insurance') AND (table LIKE '%')
GROUP BY
    database,
    table,
    column
ORDER BY size DESC;

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 'insurance') AND (table LIKE '%')
GROUP BY
    database,
    table
ORDER BY size DESC;