clickhouse.guru
Blog· Tested on ClickHouse 26.4

ClickHouse SQL Games

Two games written in nothing but ClickHouse SQL: INSERT is the input, SELECT is the screen, materialized views are the game engine. The 2022 talk, and what broke on modern ClickHouse.

Source: github.com

I gave this talk at the Istanbul ClickHouse Meetup on 21 October 2022, organised by Altinity. The question on the first real slide was: ClickHouse runs on every architecture, scales to thousands of nodes, runs AI models and wins benchmarks, so how about playing games?

The code in this article was run on clickhouse local 26.4 against the current version of the repository.

The idea

A game is a loop: read what the player did, compute the new state, draw the screen. ClickHouse has all three pieces if you squint.

  • Input is an INSERT. The player types INSERT INTO game.input VALUES ('up'). The input table uses the Null engine: it stores nothing, and exists only to fire the materialized views attached to it.
  • Logic is a materialized view. A materialized view runs its SELECT on every block inserted into its source table. That SELECT can look at the current state, decide what a move means, and write the result into another table.
  • State is one row. A table on the EmbeddedRocksDB engine with a constant primary key holds exactly one row. Every insert overwrites it. That is an upsert in one line of DDL: key UInt8 MATERIALIZED 1, PRIMARY KEY key.
  • The screen is a SELECT. Nothing is pushed to the player. A shell loop re-runs a query that renders the board.

The shape, as it is today:

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

It is a ridiculous way to write a game, which is the point. It is also a good way to learn what a materialized view is: a trigger that fires on insert, with a query for a body.

Game one: Guess the number

You think of a number. ClickHouse guesses; you reply up, down or yes. It finds the number by bisection, so at most seven answers for 1 to 100.

The state is the range still possible, lo and hi, an inclusive pair, a try counter and a done flag. A SQL function wraps the “read the state” scalar subquery, and another computes the guess:

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

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

The move logic is one materialized view. It reads the state, decides whether the word is up, down or yes, narrows the range around the previous guess, and writes a row to history:

CREATE MATERIALIZED VIEW game.move_mv TO game.history AS
WITH
    gn_state()           AS st,
    tupleElement(st, 2)  AS try_,
    tupleElement(st, 3)  AS lo_,
    tupleElement(st, 4)  AS hi_,
    lower(trim(s))       AS word,
    word = 'up'          AS is_up,
    word = 'down'        AS is_down,
    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, try_ + 1, new_lo, new_hi, done, said, message */
FROM game.input
WHERE word IN ('up', 'down', 'yes');

(That is an abridged copy; the complete, running version is in the tutorial.) The point to notice is how much the WITH clause does: it is where the “program” lives, a list of named expressions that build on each other, and ClickHouse lets each one use the ones before it.

A second view, state_mv, copies every history row into the one-row state table. The result of a game, played by sequential inserts (the run below is on clickhouse local):

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!

Game two: Wordle

Wordle has the same architecture with a richer function layer. The dictionary is a table of 5,757 five-letter words (Donald Knuth’s Stanford GraphBase list). A new game input draws a random target; any five letters is a guess; a materialized view writes the move; another overwrites the state.

The interesting code is the scoring function. A guess is scored per position as 2 (green, right place), 1 (yellow, in the word elsewhere) or 0 (gray). The version I presented in 2022 was one line:

CREATE FUNCTION compare AS (targ, inp) ->
    arrayMap((i, c) -> multiIf(c = (targ[i]), 2, has(targ, c), 1, 0), [1,2,3,4,5], inp);

It is wrong for repeated letters. If the target is ABIDE and you guess EERIE, the real game colours only the last E green and the other two gray (the target has a single E), but the one-liner marks them yellow. I ran both versions on 26.4:

CREATE OR REPLACE FUNCTION old_compare AS (targ, inp) ->
    arrayMap((i, c) -> multiIf(c = (targ[i]), 2, has(targ, c), 1, 0), [1,2,3,4,5], inp);

CREATE OR REPLACE FUNCTION wordle_compare AS (targ, inp) ->
    arrayMap(i -> multiIf(
        inp[i] = targ[i], 2,
        countEqual(targ, inp[i])
            - arrayCount(j -> inp[j] = inp[i] AND inp[j]  = targ[j], [1, 2, 3, 4, 5])
            - arrayCount(k -> k < i AND inp[k] = inp[i] AND inp[k] != targ[k], [1, 2, 3, 4, 5]) > 0, 1,
        0), [1, 2, 3, 4, 5]);

WITH splitByString('', 'ABIDE') AS t, splitByString('', 'EERIE') AS g
SELECT old_compare(t, g) AS old, wordle_compare(t, g) AS fixed;
[1,1,0,1,2]	[0,0,0,1,2]

The fixed function says: a letter is yellow only while the target still has an unclaimed copy of it. Copies matched green anywhere, or already marked yellow further left, are used up first. That is countEqual for the total, minus an arrayCount of green matches, minus an arrayCount of earlier yellows, over the positions array.

Playing the actual game from the repository on clickhouse local gave this history, with the target in the third column (the target is random, so yours will differ):

1	0	CROPS		[0,0,0,0,0]
1	1	CROPS	CRANE	[2,2,0,0,0]
1	2	CROPS	SLATE	[1,0,0,0,0]

A non-guess such as abcdefg is ignored by the view’s WHERE, because it must match ^[A-Za-z]{5}$. Guesses are not checked against the dictionary, so any five letters count.

Bonus: the Wordle solver

The last demo was a cheater. You give it the gray letters (blacklist) and the yellow and green letters (whitelist) and it lists every word that fits. In 2022 it ran on ClickHouse Fiddle against the word list on GitHub. Today it is a standalone query for clickhouse local, with an optional green pattern where _ means unknown:

WITH
    lower({black:String}) AS blacklist,
    lower({white:String}) AS whitelist,
    if({pattern:String} = '', '.....', replaceAll(lower({pattern:String}), '_', '.')) AS pattern
SELECT
    rowNumberInAllBlocks() + 1 AS num,
    upper(word) AS word
FROM
(
    SELECT word
    FROM file({words:String}, 'LineAsString', 'word String')
    WHERE length(word) = 5
      AND NOT arrayExists(c -> position(word, c) > 0, splitByString('', blacklist))
      AND arrayAll(c -> position(word, c) > 0, splitByString('', whitelist))
      AND match(word, '^' || pattern || '$')
    ORDER BY word
);

Run with --param_black=dur --param_white=ae --param_pattern=_a__e --param_words=sgb-words.txt, it returned 37 words, from BAIZE and BASTE down to WASTE. And --param_black=adieu --param_white=rst found FORTS, FROST, MORTS, PORTS, ROOST, ROOTS, SHORT and so on.

The mechanism is the array toolbox from the first talk: splitByString('', x) turns a string into an array of characters, arrayExists asks whether any blacklisted letter occurs in the word, arrayAll asks whether every whitelisted letter does, and match takes the green pattern.

What broke on modern ClickHouse

The 2022 SQL does not run on current releases. Four things changed, all of them informative.

LIVE VIEW was removed. The guess-the-number game used a live view for its display: a view that you subscribe to and that pushes new rows. On 26.4 CREATE LIVE VIEW is a syntax error and the LiveView engine is not in system.table_engines. Both games now render with a plain SELECT that a shell loop polls once a second. It is less elegant and just as playable.

A materialized view no longer fires off an EmbeddedRocksDB source. The 2022 flow was input -> state -> history: the state table was the source of the view that wrote the log. On current releases the view never fires. I reproduced it on 26.4:

CREATE TABLE st (key UInt8 MATERIALIZED 1, v UInt32) ENGINE = EmbeddedRocksDB PRIMARY KEY key;
CREATE TABLE out (v UInt32) ENGINE = Memory;
CREATE MATERIALIZED VIEW mv TO out AS SELECT v FROM st;
INSERT INTO st (v) VALUES (1);
SELECT 'state', * FROM st;
SELECT 'out rows', count() FROM out;
state	1
out rows	0

The data reaches st and the view never sees it. So the flow was reversed to input -> history -> state: the append-only MergeTree is the source of everything, and the one-row state is a derived copy. That is the better design anyway, since now you also keep the full history of every move.

The analyzer folds scalar subqueries at DDL time. The 2022 Wordle picked a random word with rand() % (SELECT count() FROM words) inside the view. With the new analyzer (on by default, enable_analyzer = 1), the scalar subquery is evaluated while the view is being created, when the words table is still empty, and the result is a division by zero. I reproduced it:

Code: 153. DB::Exception: Division by zero: while executing function modulo on arguments rand() UInt32 ...

The view is rejected at CREATE MATERIALIZED VIEW, before a single row is inserted. The fix is to move the random pick behind an ORDER BY rand() LIMIT 1 inside a SQL function, so there is no scalar count() for the analyzer to fold. A related trap: a scalar subquery over an empty table returns NULL, not a default, which would make the first insert fail. The state accessor aggregates (max, any) so that an empty table yields zeros:

SELECT (SELECT game_id FROM s LIMIT 1), (SELECT max(game_id) FROM s);
\N	0

Names are global. SQL user-defined functions belong to the server, not a database. My functions were called compare, colored, clear, names that are not mine to take. They became wordle_compare, gn_guess and so on, and each game ships a drop-game.sql that removes its functions as well as its tables.

Two bugs that were mine, not ClickHouse’s, got fixed on the way: the original Guess-the-number could never guess exactly 1 or exactly 100 because its bounds were exclusive (the new range is inclusive), and the Wordle scoring was wrong for repeated letters, as shown above.

Why bother

It is easy to treat this as a toy, and it is one. But the toy rests on three things worth remembering. A Null table plus a materialized view is a stateless trigger, useful for any pipeline that wants to react to inserts without storing the raw input. A one-row EmbeddedRocksDB table is a cheap, atomic “current value” store. And the WITH clause plus SQL functions give you more of a programming language than most people think. All three show up in real systems; the games just have no boilerplate hiding them.

The last slide was an invitation. If you write a game in ClickHouse SQL, send a pull request to the repository: keep it in its own directory with a create-game.sql, a drop-game.sql and a watch.sql, and the install script picks it up. The slide’s “what next” poll offered a chatbot, ordering pizza, and playing Doom. I am still waiting for the Doom one.

materialized-viewstalksfun

Discussion

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