clickhouse.guru
Tutorials· intermediate· Tested on ClickHouse 26.4

Build a game with materialized views

Build a guess-the-number game in pure ClickHouse SQL: a Null table for input, materialized views for logic, a one-row EmbeddedRocksDB table for state, and a SELECT for the screen.

This tutorial builds a small game, guess the number, using only ClickHouse SQL. You think of a number between 1 and 100; ClickHouse guesses; you answer up, down or yes. It is a toy, but it teaches four techniques that appear in real pipelines: a Null table as an input channel, materialized views as triggers with state, a one-row EmbeddedRocksDB table as a current-value store, and SQL user-defined functions to keep views readable. It is the hands-on companion of the talk ClickHouse SQL Games.

Every statement here was run on clickhouse local 26.4, as a single script in one process. The interactive version needs a server: one terminal plays by running INSERT statements while another re-runs the display query in a loop. Here we do the same thing with sequential INSERT and SELECT statements, which is enough to see the logic work. The full game, with a play.sh wrapper, is in the repository.

Prerequisites: you can read materialized view basics. Everything runs in the clickhouse local binary; start a session with clickhouse local and paste the statements, or put them in a file and run clickhouse local --multiquery < game.sql.

The design

INSERT INTO game.input           Null table: stores nothing, fires the views
        |   view: parse the text, read the current state, compute the move
        v
game.history                     MergeTree: every move of every game
        |   view
        v
game.state                       EmbeddedRocksDB, constant key: one row, always current

Three tables and three views. The order in which we create them matters, and a section near the end shows what goes wrong otherwise.

Step 1: the input channel

CREATE DATABASE game;
CREATE TABLE game.input (s String) ENGINE = Null;

INSERT INTO game.input VALUES ('hello');
SELECT count() FROM game.input;
0

The insert succeeds, and nothing is stored. A Null table discards its rows, but materialized views attached to it still see every inserted block. That makes it a pure event channel: the player’s text exists for exactly as long as it takes the views to read it.

Step 2: history and state

The history records every move of every game. The state holds only the current one.

CREATE TABLE game.history
(
    game_id UInt32,
    try     UInt16,
    lo      Int64,
    hi      Int64,
    done    Bool,
    said    String,
    message String
)
ENGINE = MergeTree
ORDER BY (game_id, try);

CREATE TABLE game.state
(
    key     UInt8 MATERIALIZED 1,
    game_id UInt32,
    try     UInt16,
    lo      Int64,
    hi      Int64,
    done    Bool
)
ENGINE = EmbeddedRocksDB
PRIMARY KEY key;

lo and hi are the inclusive bounds still possible for the secret number. The state table is the trick of the whole game. EmbeddedRocksDB is a key-value engine, and an insert with an existing key replaces the row. The primary key here is key, a MATERIALIZED column with the constant value 1, so every insert has the same key and overwrites the previous row. The table always holds exactly one row.

INSERT INTO game.state (game_id, try, lo, hi, done) VALUES (1, 0, 1, 100, false);
INSERT INTO game.state (game_id, try, lo, hi, done) VALUES (1, 1, 51, 100, false);
SELECT * FROM game.state;
SELECT count() FROM game.state;
1	1	51	100	false
1

Two inserts, one row, the newer one. Clear it again before we start the game:

TRUNCATE TABLE game.state;

Step 3: functions

SQL user-defined functions keep the views short. Note that they are server-global, not per database, which is why these carry a gn_ prefix: names like guess or message are not yours to take.

CREATE OR REPLACE FUNCTION gn_guess AS (lo, hi) -> intDiv(lo + hi, 2);

CREATE OR REPLACE FUNCTION gn_message AS (lo, hi, try, done) -> multiIf(
    done,     format('Got it in {0} guesses!', toString(try)),
    try = 0,  format('New game between {0} and {1}. Is it {2}?', toString(lo), toString(hi), toString(gn_guess(lo, hi))),
    lo > hi,  'You are cheating!',
    lo = hi,  format('It is definitely {0}!', toString(lo)),
              format('My guess is {0}. up, down or yes?', toString(gn_guess(lo, hi))));

CREATE OR REPLACE FUNCTION gn_state AS ()
    -> (SELECT (max(game_id), max(try), any(lo), any(hi), any(done)) FROM game.state);

gn_guess is bisection: the middle of the range. gn_message is the reply text. gn_state reads the current state row as a tuple. It uses aggregates (max, any) on purpose, for a reason that is easy to miss:

SELECT gn_state();
SELECT gn_guess(1, 100), gn_guess(51, 100), gn_message(51, 74, 2, false);
(0,0,0,0,false)
50	75	My guess is 62. up, down or yes?

On an empty state table the tuple is all zeros. Without the aggregates, a scalar subquery over an empty table returns NULL, and the very first move would break. That is how SELECT (SELECT game_id FROM s LIMIT 1) behaves on an empty table s: it prints \N, whereas SELECT (SELECT max(game_id) FROM s) prints 0.

Step 4: copy history into state

CREATE MATERIALIZED VIEW game.state_mv TO game.state AS
SELECT game_id, try, lo, hi, done FROM game.history;

Every row that lands in history is also written to state, replacing the old row. We create this view first so that the state is always current when a later view reads it.

Step 5: starting a game

CREATE MATERIALIZED VIEW game.new_game_mv TO game.history AS
SELECT
    tupleElement(gn_state(), 1) + 1  AS game_id,
    0                                AS try,
    1                                AS lo,
    100                              AS hi,
    false                            AS done,
    s                                AS said,
    gn_message(1, 100, 0, false)     AS message
FROM game.input
WHERE lower(s) = 'new game';

The game number is the current one plus one. Insert the phrase twice:

INSERT INTO game.input VALUES ('new game');
INSERT INTO game.input VALUES ('new game');
SELECT game_id, try, lo, hi, message FROM game.history ORDER BY game_id FORMAT PrettyCompactMonoBlock;
SELECT * FROM game.state;
   ┌─game_id─┬─try─┬─lo─┬──hi─┬─message───────────────────────────────┐
1. │       1 │   0 │  1 │ 100 │ New game between 1 and 100. Is it 50? │
2. │       2 │   0 │  1 │ 100 │ New game between 1 and 100. Is it 50? │
   └─────────┴─────┴────┴─────┴───────────────────────────────────────┘
2	0	1	100	false

Two games in the history, and the state points at game 2. The chain input to new_game_mv to history to state_mv to state fired within a single INSERT.

Step 6: moves

The move view is the real game logic. Read the WITH clause top to bottom; each line may use the ones above it.

CREATE MATERIALIZED VIEW game.move_mv TO game.history AS
WITH
    gn_state()           AS st,
    tupleElement(st, 1)  AS game_id_,
    tupleElement(st, 2)  AS try_,
    tupleElement(st, 3)  AS lo_,
    tupleElement(st, 4)  AS hi_,
    tupleElement(st, 5)  AS done_,
    lower(trim(s))       AS word,
    word = 'up'          AS is_up,
    word = 'down'        AS is_down,
    word = 'yes'         AS is_yes,
    gn_guess(lo_, hi_)   AS guess,
    if(is_up, guess + 1, lo_)   AS new_lo,
    if(is_down, guess - 1, hi_) AS new_hi
SELECT
    game_id_                                       AS game_id,
    try_ + 1                                       AS try,
    new_lo                                         AS lo,
    new_hi                                         AS hi,
    is_yes                                         AS done,
    s                                              AS said,
    gn_message(new_lo, new_hi, try_ + 1, is_yes)   AS message
FROM game.input
WHERE (is_up OR is_down OR is_yes) AND game_id_ > 0 AND NOT done_ AND lo_ <= hi_;

up means “my number is higher than your guess”, so the new lower bound is guess + 1; down moves the upper bound to guess - 1. The bounds are inclusive on both ends, which is what makes 1 and 100 guessable. The WHERE clause ignores words that are not moves, moves before any game exists, moves after the game ended, and moves after the range has run out.

Let’s play. Suppose the secret number is 59:

INSERT INTO game.input VALUES ('new game');
INSERT INTO game.input VALUES ('up');     -- 50 is too low
INSERT INTO game.input VALUES ('down');   -- 75 is too high
INSERT INTO game.input VALUES ('down');   -- 62 is too high
INSERT INTO game.input VALUES ('up');     -- 56 is too low
INSERT INTO game.input VALUES ('yes');    -- 59
SELECT game_id, try, lo, hi, said, message FROM game.history WHERE game_id = 3 ORDER BY try FORMAT PrettyCompactMonoBlock;
   ┌─game_id─┬─try─┬─lo─┬──hi─┬─said─────┬─message───────────────────────────────┐
1. │       3 │   0 │  1 │ 100 │ new game │ New game between 1 and 100. Is it 50? │
2. │       3 │   1 │ 51 │ 100 │ up       │ My guess is 75. up, down or yes?      │
3. │       3 │   2 │ 51 │  74 │ down     │ My guess is 62. up, down or yes?      │
4. │       3 │   3 │ 51 │  61 │ down     │ My guess is 56. up, down or yes?      │
5. │       3 │   4 │ 57 │  61 │ up       │ My guess is 59. up, down or yes?      │
6. │       3 │   5 │ 57 │  61 │ yes      │ Got it in 5 guesses!                  │
   └─────────┴─────┴────┴─────┴──────────┴───────────────────────────────────────┘

Each row’s lo/hi is the range after the answer, and each message is what ClickHouse says next. Inserting another move once the game is done changes nothing:

INSERT INTO game.input VALUES ('down');
SELECT count() FROM game.history WHERE game_id = 3;
6

The NOT done_ condition in the view’s WHERE filtered it out.

Step 7: the screen

The display is a plain query that renders the transcript of the current game. In the interactive version you run it in a loop in another terminal (watch -n1 clickhouse-client ..., or a while loop); here we run it once.

WITH (SELECT max(game_id) FROM game.state) AS current_game
SELECT line
FROM
(
    SELECT 0 AS ord, format('GUESS THE NUMBER  --  game #{0}', toString(current_game)) AS line
    UNION ALL
    SELECT 1, ''
    UNION ALL
    SELECT try * 2 + 1, format('  > {0}', said) FROM game.history WHERE game_id = current_game AND try > 0
    UNION ALL
    SELECT try * 2 + 2, format('  {0}', message) FROM game.history WHERE game_id = current_game
)
ORDER BY ord
FORMAT TSVRaw;
GUESS THE NUMBER  --  game #3

  New game between 1 and 100. Is it 50?
  > up
  My guess is 75. up, down or yes?
  > down
  My guess is 62. up, down or yes?
  > down
  My guess is 56. up, down or yes?
  > up
  My guess is 59. up, down or yes?
  > yes
  Got it in 5 guesses!

The ord column interleaves what you said (odd numbers) with what ClickHouse replied (even numbers); FORMAT TSVRaw prints the lines without escaping.

Why the order of creation matters

When I first wrote this I created the state_mv view after the first new game. The second new game then read an empty state, got game number 0 + 1, and produced two rows with game_id 1. The rule: a view that reads the state needs the view that writes the state to exist before it fires. Create state_mv first, as above.

The traps

  • You cannot chain a view off the state table. It is natural to write the log with a view on game.state (the table that holds the current row). On current ClickHouse such a view never fires for EmbeddedRocksDB sources: I inserted a row into a state table with a view attached and the target table stayed empty (0 rows) on 26.4. That is why history sits before state in this design, and why the 2022 version of the game had to be rewritten.
  • Scalar subqueries in views are evaluated when the view is created. Under the new analyzer, rand() % (SELECT count() FROM words) in a view over an empty words table fails with a division-by-zero error at CREATE MATERIALIZED VIEW, before a row is inserted. Keep scalar subqueries behind SQL functions that do not fold to constants, or select a row with ORDER BY rand() LIMIT 1.
  • Functions are global. Prefix them with your project name and remove them yourself when you drop the database: DROP DATABASE does not remove SQL functions.
  • Nothing here is atomic across tables. The views are triggers: each runs in the same insert, but if a later view in the chain fails the earlier writes remain. For a game this is fine. For a real pipeline, see adding a materialized view to an existing table.

Going further

  • Add new game 1 1000: extract two numbers with extractAll(s, '-?\\d+') and use them as the range (the repository does this).
  • Accept synonyms for up and down (higher, lower, …) with IN (...).
  • Run it against a server and play with two terminals, as intended; the repository contains watch.sh and play.sh helpers.
  • Read the Wordle implementation for the same architecture with an array-based scoring function.

materialized-viewsembeddedrocksdbnull-enginesql-functions

Discussion

Comments live in GitHub Discussions. Sign in with GitHub to join in.