skip to content

Why does a ClickHouse materialized view write uniqState(user_id) instead of uniq(user_id)?

level: middleimportance: must knowfreq 68%

answer

  1. the view only aggregated one batch
  2. distinct counts do not add up
  3. store something that can be combined later
  4. intermediate state, finished at read time
  5. -State writes it, -Merge finishes it

basics

~20 s

Because the view aggregates only the block being inserted, its output is partial. The -State combinator stores a mergeable intermediate aggregate instead of a final number, so AggregatingMergeTree can combine partial results from many inserts; readers finish the job with uniqMerge.

solid answer

~50 s

A ClickHouse materialized view sees only the inserted block, so `uniq(user_id)` would give the distinct count *within that block*. Summing those numbers later is wrong, because the same user can appear in many blocks. `uniqState(user_id)` returns an intermediate aggregation state — the sketch itself, typed `AggregateFunction(uniq, UInt64)` — which **is** mergeable. Store it in an `AggregatingMergeTree` whose `ORDER BY` equals the view's `GROUP BY` keys, and background merges combine states for rows sharing that key. At read time you finish the aggregation with the matching `-Merge` combinator: ```sql SELECT day, uniqMerge(uniq_users) FROM daily GROUP BY day; ``` The read-time `GROUP BY` is mandatory, not decorative: merges are asynchronous, so several unmerged state rows per key normally exist. For simple functions like `sum`, `min`, `max` and `any`, `SimpleAggregateFunction` is a cheaper alternative that stores a plain value.

code

sql · 16 lines
sql
CREATE TABLE daily
(
    day        Date,
    country    LowCardinality(String),
    uniq_users AggregateFunction(uniq, UInt64),
    events     SimpleAggregateFunction(sum, UInt64)
) ENGINE = AggregatingMergeTree
ORDER BY (day, country);

CREATE MATERIALIZED VIEW daily_mv TO daily AS
SELECT toDate(ts) AS day,
       country,
       uniqState(user_id) AS uniq_users,
       count()            AS events
FROM events
GROUP BY day, country;

go deeper

for a junior

Know the pairing: a view writes uniqState(...) and a query reads it back with uniqMerge(...). Selecting a state column directly returns binary you cannot use.

for a middle

Explain why partial aggregates need mergeable state, which functions need AggregateFunction versus SimpleAggregateFunction, and why the target's ORDER BY must equal the view's GROUP BY.

for a senior

Show you never rely on merges having run: read queries always re-aggregate, and you expose the rollup through a view so consumers cannot omit the -Merge step.

for a principal

Own the rollup contract across teams — which grains exist, what precision the sketches guarantee, and how the states migrate when someone needs a new dimension added to the key.

## The problem: partial aggregates A ClickHouse materialized view runs its `SELECT` over one inserted block. Whatever aggregate it computes is therefore a *partial* aggregate over a slice of the data, and the target table will accumulate one such partial per block per group. Some aggregates survive that treatment naturally. `count()` and `sum(x)` are additive: summing per-block sums gives the right total. Others do not. `uniq(user_id)` per block, summed, over-counts every user that appears in more than one block. `avg(x)` per block, averaged, is wrong whenever blocks differ in size. `quantile(0.99)(x)` per block cannot be combined at all. ## The mechanism: aggregate states ClickHouse solves this with *combinators* — suffixes that change what an aggregate function returns. - `-State` returns the function's internal intermediate state instead of the final value. For `uniq`, that is the probabilistic sketch; for `avg`, the (sum, count) pair; for `quantiles`, the sampled reservoir/digest. - `-Merge` takes a column of such states and folds them into the final value. - `-MergeState` folds states and returns a state again, which is what you need when chaining one rollup into a coarser one. The state's column type is `AggregateFunction(uniq, UInt64)` — the function name and argument types are part of the type, so the reader knows how to interpret the bytes. States are opaque binary; you cannot compare or sum them, only merge them. ## The table: AggregatingMergeTree States only become useful if something combines them. `AggregatingMergeTree` does that during background merges: rows sharing the full `ORDER BY` key are collapsed, with `AggregateFunction` columns merged and `SimpleAggregateFunction` columns reduced by their function. ```sql CREATE TABLE daily ( day Date, country LowCardinality(String), uniq_users AggregateFunction(uniq, UInt64), events SimpleAggregateFunction(sum, UInt64) ) ENGINE = AggregatingMergeTree ORDER BY (day, country); CREATE MATERIALIZED VIEW daily_mv TO daily AS SELECT toDate(ts) AS day, country, uniqState(user_id) AS uniq_users, count() AS events FROM events GROUP BY day, country; ``` The target's `ORDER BY` must be exactly the view's `GROUP BY` key list — that is the identity rows are collapsed on. If you group by `(day, country)` but sort the table by `day` alone, countries get merged together and the numbers are silently wrong. ## Why the read query must still GROUP BY Background merges are opportunistic. At any moment a key may have one row or fifty. So a query that reads a state column directly, or reads a `SimpleAggregateFunction` column without summing, sees an arbitrary subset. Always read like this: ```sql SELECT day, country, uniqMerge(uniq_users) AS users, sum(events) AS events FROM daily WHERE day >= today() - 7 GROUP BY day, country; ``` `FINAL` will also produce correct results by forcing the merge at read time, but it is more expensive than a `GROUP BY` over an already-small rollup table. Many teams hide the correct read query behind an ordinary `VIEW` so that analysts cannot forget the `-Merge`. ## AggregateFunction versus SimpleAggregateFunction When a function's partial result has the same type as its final result — `sum`, `min`, `max`, `any`, `anyLast`, `groupBitOr` — you do not need an opaque state. `SimpleAggregateFunction(sum, UInt64)` stores a plain number that merges by re-applying the function. It is smaller, human-readable, and needs no `-Merge` at read time (a plain `sum()` suffices). Reserve `AggregateFunction` + `-State` for the genuinely non-trivial ones: `uniq`, `uniqExact`, `avg`, `quantiles`, `topK`, `argMax`. ## Common failure modes 1. **A plain column in an AggregatingMergeTree.** Declaring `events UInt64` instead of a `SimpleAggregateFunction` means merges keep an arbitrary value for that column and discard the rest — data loss that looks like flaky numbers. 2. **Reading without merging.** Selecting a state column directly returns unreadable binary, or, worse, selecting a `SimpleAggregateFunction` without `sum()` returns one unmerged row's value. 3. **Mismatched sort key.** Target `ORDER BY` narrower or wider than the view's `GROUP BY`. 4. **Type drift.** `uniqState(user_id)` on a `String` column produces `AggregateFunction(uniq, String)`, which will not fit a column declared over `UInt64`. 5. **Assuming merges have run.** Any correctness argument that depends on a merge having completed is wrong; merges are eventual by design. ## The one-line answer *"The view only aggregates one block, so it has to store something mergeable. `-State` gives the intermediate state, `AggregatingMergeTree` merges states sharing the sort key in the background, and the reader calls `-Merge` with a `GROUP BY` because those background merges are not guaranteed to have happened."*

  • When can you use SimpleAggregateFunction instead of AggregateFunction with -State?
    When the partial result has the same type as the final result and re-applying the function merges it correctly — `sum`, `min`, `max`, `any`, `anyLast`, `groupBitOr`. It stores a plain readable value, is smaller on disk, and needs no `-Merge` at read time; a normal `sum()` or `max()` over the column is enough. Functions like `uniq`, `avg`, `quantiles` and `topK` carry real internal state and need `AggregateFunction`.
  • Why must the target table's ORDER BY match the view's GROUP BY keys?
    AggregatingMergeTree collapses rows that share the full sort key, so the sort key defines the aggregation identity. If the table sorts by `(day)` while the view groups by `(day, country)`, merges combine different countries into one row and the results are silently wrong. If the sort key is wider than the group key, rows never collapse and the rollup grows without bound.
  • A dashboard queries the rollup table right after a load and the numbers keep changing between runs. What is happening?
    The read query is not re-aggregating. Background merges collapse state rows over time, so a query that reads unmerged rows without `GROUP BY` plus `-Merge` sees a shifting subset. Fix the query rather than the table: group by the sort key and apply `uniqMerge`/`sum`. Wrapping the correct query in an ordinary VIEW stops analysts from hitting this.
  • How do you build an hourly rollup on top of a minute-level table that already stores aggregate states?
    Attach a second materialized view to the minute table and use the `-MergeState` combinator: `uniqMergeState(uniq_users)` reads the incoming states, merges them, and emits a coarser state for the hourly `AggregatingMergeTree`. Additive columns can simply be summed. Never call `-Merge` and then `-State` separately in the hope of round-tripping the value.

saying these in an interview costs you the question

  • Summing per-block distinct counts and calling it the total
  • Storing a plain UInt64 column in AggregatingMergeTree
  • Reading state columns without the matching -Merge function
  • Assuming background merges have already collapsed every key
  • Making the target's ORDER BY differ from the view's GROUP BY

context

resolving links…