skip to content

In ClickHouse, when should attributes live in a Map column rather than a Tuple or separate columns?

level: middleimportance: nice to knowfreq 38%

answer

  1. flexibility is paid for on every scan
  2. stored as two parallel arrays
  3. a missing key is not NULL
  4. fixed arity gets its own type
  5. hot keys can be promoted at insert time

basics

~20 s

Use Map only for genuinely dynamic, sparse keys: it is stored as parallel key and value arrays, so reading one key reads them all. Use a Tuple for a fixed small group of related fields, and plain columns for any attribute you filter or group on regularly.

solid answer

~50 s

`Map(K, V)` holds arbitrary key-value pairs per row, accessed as `attrs['os']`. Two things follow from its physical layout as parallel key and value arrays: a missing key returns the **value type's default** (`''`, `0`) rather than `NULL`, so you need `mapContains(attrs, 'os')` to tell absent from empty; and reading a single key still reads the whole keys and values arrays for every granule scanned, so it never performs like a real column. `Tuple(a UInt8, b String)` is fixed-arity and heterogeneous, its elements stored as separate subcolumns and read individually via `t.a` (or `t.1` positionally) — good for grouping a few related fields, not for dynamic data. The rule of thumb: known, frequently-filtered attributes become real columns, which compress and prune best; a genuinely open key space goes in a `Map`; and hot keys inside a `Map` can be promoted with a `MATERIALIZED` column.

code

sql · 9 lines
sql
CREATE TABLE events
(
    ts    DateTime,
    attrs Map(String, String),
    -- promote a hot key to a real, prunable column
    os    LowCardinality(String) MATERIALIZED attrs['os']
)
ENGINE = MergeTree
ORDER BY (os, ts)

go deeper

for a junior

Recall the syntax: attrs['key'] for a Map, t.a or t.1 for a Tuple, and that a missing map key gives a default value rather than NULL.

for a middle

Explain the parallel key/value array layout, why reading one key touches both arrays, and why Tuple elements are separate subcolumns while Map keys are not.

for a senior

Show the promotion pattern with MATERIALIZED columns, argue compression and pruning differences, and be able to justify the older parallel-arrays idiom where it still fits.

for a principal

Own the schema policy for semi-structured payloads: which attributes are contract, which are long tail, how promotions are rolled out, and what the flexibility costs per scan at your volumes.

## The three options Schemas for event data always face the same question: the payload has attributes, some known, some not. ClickHouse offers three answers with quite different physical behaviour. **Real columns.** A declared `String`/`UInt32`/`LowCardinality(String)` column is stored and compressed on its own, participates in the sorting key and in skipping structures, and is read without touching anything else. This is always the fastest option and the one to default to for any attribute you filter or group on. **`Tuple`.** `Tuple(a UInt8, b String)` is a fixed-arity, heterogeneous group. Named tuples are read as `t.a`; anonymous ones positionally as `t.1` (1-based). Elements are stored as separate subcolumns, so `SELECT t.a` reads only that element's data — a tuple is closer to "several columns wearing one name" than to a blob. It is useful for keeping related fields together (a coordinate pair, a min/max range) and for returning multiple values from an expression; `untuple(t)` expands it into separate result columns. What it is *not* is a way to store dynamic data, because the arity and types are fixed in the schema. **`Map(K, V)`.** Arbitrary keys per row, written `map('os', 'linux', 'ver', '14')` or via the literal syntax, read as `attrs['os']`. This is the right tool when the key space is genuinely open — customer-supplied labels, tracing attributes, tag bags. ## What a Map actually costs A `Map` is stored as parallel arrays of keys and values. Three consequences matter: 1. **Reading one key is not a cheap column read.** `attrs['os']` must decompress the keys array and the values array for every granule the query touches, then search for the key. Compared with a dedicated `os` column, the I/O and CPU can be an order of magnitude worse on a wide map. The `.keys` and `.values` subcolumns can be read alone when you only need one side, which helps but does not close the gap. 2. **Missing keys return defaults, not NULL.** `attrs['nope']` on a `Map(String, String)` yields `''`. You cannot distinguish "absent" from "present but empty" without `mapContains(attrs, 'nope')`. Reports that filter `attrs['plan'] != ''` therefore silently conflate two different states. 3. **Compression is worse.** A dedicated column holds one homogeneous run of values that compresses well; a map's values array interleaves values of different meanings, and the keys array repeats the same strings on every row (`LowCardinality(String)` as the key type mitigates this). Useful map functions: `mapKeys`, `mapValues`, `mapContains`, `mapFromArrays`, `mapApply` and `mapFilter` (which take lambdas, like the array functions). ## The promotion pattern The practical schema keeps a `Map` for the long tail and promotes hot keys to real columns with a `MATERIALIZED` expression, so the value is computed once at insert and stored as its own column: ```sql CREATE TABLE events ( ts DateTime, attrs Map(String, String), os LowCardinality(String) MATERIALIZED attrs['os'] ) ENGINE = MergeTree ORDER BY (os, ts); ``` Now `WHERE os = 'linux'` reads a small dedicated column and can participate in the sorting key, while the rare keys stay accessible through `attrs`. `MATERIALIZED` columns are not returned by `SELECT *`, which keeps the wire format stable, and they are back-filled for new parts only — existing parts keep the old layout until they are rewritten. ## An older alternative Before `Map` existed the idiom was two parallel arrays, `keys Array(String)` and `values Array(String)`, queried with `values[indexOf(keys, 'os')]`. It is more verbose but gives you explicit control: you can read only the `values` array, give each array its own codec, and avoid the map's key-search machinery. Plenty of production schemas still use it, and it is a legitimate answer as long as you can say why. Recent ClickHouse versions also add a dedicated JSON column type for semi-structured data; whether it is available and production-ready depends on your version, so check before designing around it. ## What interviewers probe The expected reasoning is physical: what does reading one key actually cost, how does the engine tell missing from empty, and which attributes deserve to be real columns. A candidate who reaches for `Map` for everything because it is flexible has not thought about the scan; one who forbids `Map` entirely has no answer for genuinely open key spaces.

  • How do you tell a missing key from an empty value in a ClickHouse Map column?
    Use `mapContains(attrs, 'plan')`. Subscript access returns the value type's default for an absent key — `''` for `String`, `0` for numbers — so `attrs['plan'] = ''` is true both when the key is absent and when it is present with an empty value. If the distinction matters for the report, test containment explicitly, or make the value type `Nullable` so absence and emptiness differ.
  • Why can a MATERIALIZED column be cheaper than reading the same key out of a Map?
    Because it becomes a real column: `os LowCardinality(String) MATERIALIZED attrs['os']` is computed once at insert and stored on its own, so queries read only that column's compressed data instead of decompressing the map's key and value arrays and searching them. It can also join the sorting key and drive part pruning, which a map subscript cannot.

saying these in an interview costs you the question

  • Expects a missing Map key to return NULL
  • Thinks reading one Map key reads only that key's bytes
  • Uses Map for a fixed, known set of attributes
  • Believes Tuple is a way to store dynamic key-value data
  • Ignores that Map keys repeat their strings on every row

context

resolving links…