ClickHouse — A Columnar Analytics Database from the Inside

What a materialized view saw and what it missed

Goal

You hook a materialized view onto a source table and confirm that INSERTs flow into the target table, and you reproduce in numbers the three things the view cannot see — rows from before the view, duplicate keys before merging, and changes to the right-hand table of a JOIN.

Why it matters

A ClickHouse materialized view is not a "stored query" but an INSERT trigger. So if you skip the backfill, the past is empty; if you backfill twice, it doubles; if you read the target table without adding, the numbers wobble depending on when merging happens; and a dimension-table join permanently misses a dimension that arrives late. This lab's grader does not trust the numbers you wrote — it compares the target table cell by cell with values recomputed from the source table (or the fixture's generating expression), and re-runs the SELECTs you saved read-only to measure the result and the rows read.

Steps

Create the database mv and the table mv.orders — columns order_id UInt64, ts DateTime, shop LowCardinality(String), product_id UInt32, user_id UInt32, qty UInt32, price UInt32 (in this order), engine MergeTree, ORDER BY (shop, ts). Then insert /opt/lab/fixtures/mv/orders_1.sql (September 1–15, 200,000 orders) once.
Create the target table mv.daily_sales (day Date, shop LowCardinality(String), orders UInt64, revenue UInt64) as SummingMergeTree, ORDER BY (shop, day), and create the materialized view mv.daily_sales_mv as TO mv.daily_sales — toDate(ts) AS day, shop, count() AS orders, sum(qty * price) AS revenue with GROUP BY day, shop.
Right after inserting /opt/lab/fixtures/mv/orders_2.sql (September 16–30, 200,000 orders), write the row count of mv.orders as source_orders and the sum(orders) of mv.daily_sales as view_orders to /root/ch/mv/gap.json.
Backfill into mv.daily_sales the first batch the view did not see, so that the target table holds the whole source exactly, per (shop, day). You must not insert the second batch again.
Write /root/ch/mv/q_daily.sql, which gives the per-day order count and revenue of shop-3 from mv.daily_sales — columns day, orders, revenue, in date order. You must read it by adding so that it is right even before merging.
Create the target table mv.shop_stats (shop LowCardinality(String), day Date, orders AggregateFunction(count), buyers AggregateFunction(uniqExact, UInt32)) as AggregatingMergeTree, ORDER BY (shop, day), and the view mv.shop_stats_mv as TO mv.shop_stats (countState(), uniqExactState(user_id)), and backfill the existing source once. Then write /root/ch/mv/q_buyers.sql, which gives the distinct buyers per shop for the month from mv.shop_stats — columns shop, buyers, in shop order.
Create mv.raw, with the same columns as mv.orders plus status LowCardinality(String) at the end, with ENGINE = Null, create a view mv.raw_mv that sends only rows with status = 'paid' to mv.orders, and then insert /opt/lab/fixtures/mv/raw.sql once.
Create mv.products (product_id UInt32, category LowCardinality(String)) (MergeTree, ORDER BY product_id), mv.cat_sales (category LowCardinality(String), orders UInt64) (SummingMergeTree, ORDER BY category), and a view mv.cat_sales_mv that sends the count() per category to it by INNER JOINing mv.orders with mv.products. Then insert in the order products.sql → late.sql → products_new.sql (all in /opt/lab/fixtures/mv/), and write the number of late orders (order_id >= 2000000) as late_orders, the sum(orders) of mv.cat_sales as joined_orders, and their difference as lost_orders to /root/ch/mv/join.json.

Notes

The server is already running (clickhouse-client). If it has stopped, run ch-up.
When measuring rows read, give --use_query_condition_cache 0. If you run a query with the same condition a second time, the condition cache skips granules and the numbers can differ. The grader measures with the cache off too.
The fixtures are divided by order number — the first batch from 0, the second from 200000, raw from 1000000, late orders from 2000000.
Common mistakes: putting even the second batch into the backfill so that September 16–30 doubles, making the JOIN view a LEFT JOIN so that empty categories appear, and inserting the fixtures twice.
If you break it, TRUNCATE TABLE the target table and insert the whole source once more (leave the view as it is).
Official docs: Incremental materialized view · CREATE VIEW · Use materialized views · AggregatingMergeTree · Refreshable materialized view

Create the source table and insert the first batch

Create the database mv and the table mv.orders. The columns are, in order, order_id UInt64, ts DateTime, shop LowCardinality(String), product_id UInt32, user_id UInt32, qty UInt32, price UInt32, the engine is MergeTree, and it is ORDER BY (shop, ts). Then run /opt/lab/fixtures/mv/orders_1.sql once to insert 200,000 orders.

After CREATE DATABASE and CREATE TABLE, pass the fixture with clickhouse-client --queries-file. The fixture is data made with a hash, so the same rows come out however many times you run it, but MergeTree does not prevent duplicates, so if you insert twice it becomes 400,000 orders.

Create the target table and the materialized view

Create the target table mv.daily_sales (day Date, shop LowCardinality(String), orders UInt64, revenue UInt64) as SummingMergeTree, ORDER BY (shop, day), and create the materialized view mv.daily_sales_mv as TO mv.daily_sales. The view's SELECT is toDate(ts) AS day, shop, count() AS orders, sum(qty * price) AS revenue FROM mv.orders GROUP BY day, shop. Right after creating it, check the row count of mv.daily_sales.

The view does not store the result directly; it writes to the table specified by TO. You must align the target table's sorting key with the view's GROUP BY for the same (shop, day) to be combined at merge time. Why the target table of the view you just made is empty is the first lesson of this module.

Compare what the view saw with the source

Right after inserting /opt/lab/fixtures/mv/orders_2.sql once, write the row count of mv.orders as source_orders and the sum(orders) of mv.daily_sales as view_orders to /root/ch/mv/gap.json.

A view is a trigger that takes the INSERT block as input. The second batch's INSERT came in while the view existed, and the first batch came in before the view was created. The difference between the two numbers is exactly the amount to backfill.

Backfill only the missing range

Backfill the first batch the view did not see (September 1–15) with INSERT INTO mv.daily_sales SELECT ..., so that the value of mv.daily_sales added per (shop, day) equals, cell by cell, the value of re-aggregating the whole source.

The key is to use the view's SELECT as it is but restrict the source to a range with WHERE. If you insert again even the days the view already inserted, the SummingMergeTree adds those days twice. The boundary is the time at which the second batch starts.

Read the target table so it is right even before merging

Write /root/ch/mv/q_daily.sql, which gives the per-day order count and revenue of shop = 'shop-3' from mv.daily_sales. The result columns are day, orders, revenue, in date order. You must read only the target table.

The view's GROUP BY combines only within one block. So before merging finishes, the same (shop, day) exists as several lines. If you sum again and GROUP BY day in the query, the same answer comes out regardless of when merging happens.

Store values that cannot be added as states

Create mv.shop_stats (shop LowCardinality(String), day Date, orders AggregateFunction(count), buyers AggregateFunction(uniqExact, UInt32)) as AggregatingMergeTree, ORDER BY (shop, day), and the view mv.shop_stats_mv as TO mv.shop_stats (countState() AS orders, uniqExactState(user_id) AS buyers, GROUP BY shop, day), and backfill the existing source once. Then write /root/ch/mv/q_buyers.sql, which gives the distinct buyers per shop for the month from mv.shop_stats — columns shop, buyers, in shop order.

If you add up the distinct buyer counts per day, people who bought on several days get counted twice. -State stores an intermediate state that can be merged instead of a result, and at query time you merge with the -Merge of the same function. If you backfill twice, countMerge doubles.

A chained view that starts at a Null table

Create mv.raw, the columns of mv.orders plus status LowCardinality(String) after them, with ENGINE = Null, and create a view mv.raw_mv that sends only the seven source columns of rows with status = 'paid' to mv.orders. Then run /opt/lab/fixtures/mv/raw.sql once.

A table with the Null engine stores nothing but accepts INSERTs and wakes the views. The block raw_mv writes to mv.orders in turn wakes daily_sales_mv and shop_stats_mv — one INSERT reaches three tables. If you SELECT mv.raw, it is always 0 rows.

A JOIN view does not know changes to the right-hand table

Create mv.products (product_id UInt32, category LowCardinality(String)) (MergeTree, ORDER BY product_id), mv.cat_sales (category LowCardinality(String), orders UInt64) (SummingMergeTree, ORDER BY category), and a view mv.cat_sales_mv that sends the count() AS orders per category to mv.cat_sales from mv.orders AS o INNER JOIN mv.products AS p ON o.product_id = p.product_id. Then insert in the order products.sql → late.sql → products_new.sql in /opt/lab/fixtures/mv/, and write the number of late orders (order_id >= 2000000) as late_orders, the sum(orders) of mv.cat_sales as joined_orders, and the difference as lost_orders to /root/ch/mv/join.json.

In a view with a JOIN, the trigger is only INSERTs into the leftmost table (the source), and the right-hand table is only read, whole, as it is at that moment. At the moment the late orders come in, orders for products not in the product table are dropped by the INNER JOIN, and they do not come back to life even if the products are registered later.