Runbook: client output, formats and SQL tricks

This is the drawer of small tools that do not belong to any one subsystem: how to read a 60-column system table, how to keep a copy of a result while you watch it, how to reproduce a bug report with three lines of sample data, how to get the newest row per key out of a ReplacingMergeTree. Each section is a question, a working answer, and the one gotcha that makes the answer wrong in practice.

Everything on this page ran on ClickHouse 26.4 with clickhouse local against the demo events table (and a 3-row profiles table where noted). The single exception is the interactive-client \G shortcut, which is a client feature and is shown as text. Row counts quoted in prose are from those runs on a small copy of the data; yours will differ.

How do I print wide rows vertically?
How do I write results to a file and the screen at once?
How do I query inline data with the format table function?
How do I sample rows randomly with a probability function?
How do I get the exact row count before LIMIT?
How do I test several columns with one condition?
How do I get the latest row per key from a ReplacingMergeTree?
How do I keep server-wide constants in a view?
How do I make a query use a lot of memory on purpose?

How do I print wide rows vertically?

System tables such as system.parts, system.merges or system.query_log have dozens of columns, which wrap into unreadable lines in a table layout. Vertical format prints each row as column: value lines. Add FORMAT Vertical to the query:

SELECT number, toString(number) AS str, range(number) AS arr
FROM numbers(2)
FORMAT Vertical;

Row 1:
──────
number: 0
str:    0
arr:    []

Row 2:
──────
number: 1
str:    1
arr:    [0]

For a whole session or script use the flag -E (long form --vertical), which also worked in clickhouse local:

clickhouse-client -E --query "SELECT * FROM system.merges"

In the interactive client, end the statement with \G instead of ; to get vertical output for that one query:

SELECT * FROM system.settings WHERE name = 'max_threads' \G

\G is handled by the client, not the server, so it does not work over the HTTP interface or in other tools. clickhouse local -q "SELECT 1 \G" is a syntax error. When in doubt use FORMAT Vertical or -E, which work everywhere. Put a LIMIT 1 or a precise WHERE in front of vertical output on big tables; the aligned column names make diffing two rows by eye easy.

How do I write results to a file and the screen at once?

SELECT ... INTO OUTFILE normally sends the result only to the file. Add AND STDOUT and the client also prints it:

SELECT number, toString(number) AS s
FROM numbers(3)
INTO OUTFILE 'result.csv' AND STDOUT
FORMAT CSV;

0,"0"
1,"1"
2,"2"

result.csv then holds the same three lines. The clause is processed by the client, so the file is created on the machine where clickhouse-client (or clickhouse-local) runs, not on the server.

The client refuses to overwrite an existing file: FILE_ALREADY_EXISTS (“consider using APPEND or TRUNCATE”). Add TRUNCATE to replace it or APPEND to add to it: INTO OUTFILE 'result.csv' TRUNCATE AND STDOUT.
Add COMPRESSION 'gzip' (or zstd, …) after AND STDOUT to compress the file; the screen output stays plain text. The gzip file decompressed to the expected rows.
The FORMAT applies to both outputs. For a pretty screen and a machine-readable file, run it twice, or use tee in the shell.

How do I query inline data with the format table function?

The format table function parses a string as if it were a file in a given format. It is the quickest way to try a query on a few sample rows, reproduce a bug report, or join against a small literal list.

SELECT *
FROM format(JSONEachRow, $$
{"a": "Hello", "b": 111}
{"a": "World", "b": 123}
{"a": "Hello", "b": 112}
$$);

Hello  111
World  123
Hello  112

Use $$...$$ quoting so the data needs no escaping. See the inferred schema with DESCRIBE:

DESCRIBE format(JSONEachRow, $${"a": "Hello", "b": 111}$$);

a  Nullable(String)
b  Nullable(Int64)

Inferred types are Nullable and numbers default to the widest type. When you need exact types, pass a structure as the second argument; the same row then described as a String, b UInt16:

SELECT * FROM format(JSONEachRow, 'a String, b UInt16', $${"a": "Hello", "b": 111}$$);

Not to be confused with the scalar function format('{0}x{1}', 'a', 'b'), which fills string placeholders (it returned axb). They share a name and nothing else.

How do I sample rows randomly with a probability function?

A one-line SQL UDF gives you “keep each row with probability p” for any query:

CREATE FUNCTION probably AS (p) -> rand() / 4294967295 <= p;

rand() is uniform over 0 .. 2^32-1, so dividing by 2^32-1 gives a number in [0, 1]. Use it as a filter:

SELECT count() FROM events WHERE probably(0.1);   -- about 10% of the rows

Check that it behaves, on a million rows:

SELECT
    countIf(probably(0.1)) / 1e6 AS p_10,
    countIf(probably(0))   / 1e6 AS p_0,
    countIf(probably(1))   / 1e6 AS p_100
FROM numbers(1000000);

0.0997   0   1

The first number varies per run around 0.1; the boundaries are exact. When this is the wrong tool:

rand() gives a different sample every run. For a repeatable sample of entities, hash the key: WHERE cityHash64(user_id) % 100 < 10 always picks the same 10% of users and keeps all rows of a chosen user together.
For big tables prefer the built-in SAMPLE 0.1 clause when the table was created with a SAMPLE BY expression; it reads fewer granules instead of filtering after reading.
A filter on rand() still reads every row. It reduces the output, not the work.

How do I get the exact row count before LIMIT?

JSON-style output formats (JSON, JSONCompact, …) include a rows_before_limit_at_least field so a UI can show “page 1 of N”. As the name says it is a lower bound: once the LIMIT is satisfied, ClickHouse stops reading early and reports however many rows it happened to see.

SELECT number FROM numbers_mt(10000000) WHERE number % 7 = 0 LIMIT 5
FORMAT JSONCompact;

"rows_before_limit_at_least": 9345

Set exact_rows_before_limit = 1 and it keeps reading until it has the true total:

SELECT number FROM numbers_mt(10000000) WHERE number % 7 = 0 LIMIT 5
SETTINGS exact_rows_before_limit = 1
FORMAT JSONCompact;

"rows_before_limit_at_least": 1428572

The first figure depends on thread and block timing and differs from run to run (9344 and 9345 on two runs); the second is exactly the count of numbers below 10,000,000 divisible by 7. The price is that the query can no longer stop early, so it does the work of a full count. With an ORDER BY or GROUP BY the whole input has been read anyway, so the number is typically exact for free. For pagination over huge tables, consider a separate count() query that you cache. Mind the spelling: the setting is exact_rows_before_limit.

How do I test several columns with one condition?

Filtering rows where any of several columns satisfies the same test usually means a long chain of OR. An array of the columns and arrayExists says it once:

SELECT url, country, event_type
FROM events
WHERE arrayExists(x -> position(x, 'shoes') > 0, [url, country, event_type]);

This keeps rows where shoes appears in at least one of the three columns; on the demo table it matched the same rows as the three-way OR (10,001 on the test copy). The columns must share a type (here all strings) because they go into one array. Variations:

-- all columns match
WHERE arrayAll(x -> x != '', [url, country, event_type])

-- how many columns match
SELECT arrayCount(x -> position(x, 'shoes') > 0, [url, country, event_type]) AS matches FROM events

-- the first non-empty one
SELECT arrayFirst(x -> x != '', [url, country, event_type]) FROM events

The older formulation arraySum(arrayMap(x -> position(x, '.'), cols)) > 0 returns the same rows but is harder to read; arrayExists is the direct form. More on arrays: Arrays, ARRAY JOIN and lambda functions.

How do I get the latest row per key from a ReplacingMergeTree?

ReplacingMergeTree keeps one row per sorting key, but only after a background merge. Until parts are merged, old versions are still there and a plain SELECT returns them. To read current state at any moment, ask for the newest row explicitly.

CREATE TABLE profiles (user_id UInt64, updated_at DateTime, plan String)
ENGINE = ReplacingMergeTree(updated_at) ORDER BY user_id;

INSERT INTO profiles VALUES (1, '2026-01-01 00:00:00', 'free');
INSERT INTO profiles VALUES (1, '2026-02-01 00:00:00', 'pro');
INSERT INTO profiles VALUES (2, '2026-01-15 00:00:00', 'free');

SELECT count() FROM profiles;   -- 3, not 2: user 1 is still stored twice

1. argMax with GROUP BY

SELECT user_id,
       argMax(plan, updated_at) AS plan,
       max(updated_at)          AS latest
FROM profiles
GROUP BY user_id;

1  pro   2026-02-01 00:00:00
2  free  2026-01-15 00:00:00

argMax(x, v) returns the x from the row where v is maximal. It is the most flexible form: it works on any table with any version column and lets you pick different “winning” columns. Use argMin for the oldest.

2. FINAL

SELECT * FROM profiles FINAL;

FINAL makes the read merge duplicates on the fly, returning the same two rows. It is the shortest form and handles deletion markers and other engine semantics. It costs extra CPU at query time, usually fine for selective queries and less so for scans of the whole table. Recent versions made FINAL much cheaper than it used to be.

3. LIMIT 1 BY

SELECT * FROM profiles ORDER BY updated_at DESC LIMIT 1 BY user_id;

Sorts and keeps the first row per key: all columns of the newest row, without listing them. All three returned the same two rows.

Need	Use
quick correct read, engine-aware	`FINAL`
aggregate over latest values, custom version column	`argMax ... GROUP BY`
the whole newest row, no column list	`LIMIT 1 BY`

Do not run OPTIMIZE ... FINAL on a big table just to get deduplicated reads; merges are not instant or guaranteed, and the query forms above are always correct. See merges, OPTIMIZE and FINAL.

How do I keep server-wide constants in a view?

You have a value used in many queries (a bucket size, a home market, a threshold) and do not want to repeat it or edit twenty dashboards when it changes. ClickHouse has no SET GLOBAL for your own variables, but a view does the job:

CREATE DATABASE cfg;

CREATE VIEW cfg.params AS
SELECT 3600 AS bucket_seconds, 'US' AS home_country;

Read the values in any query with a scalar subquery:

WITH (SELECT bucket_seconds FROM cfg.params) AS bucket
SELECT toStartOfInterval(event_time, INTERVAL bucket SECOND) AS t, count()
FROM events
GROUP BY t
ORDER BY t
LIMIT 3;

2026-01-01 00:00:00  800
2026-01-01 01:00:00  800
2026-01-01 02:00:00  800

SELECT countIf(country = (SELECT home_country FROM cfg.params)) FROM events;

Changing a value is CREATE OR REPLACE VIEW cfg.params AS SELECT ... (add ON CLUSTER where relevant), and every query picks it up on its next run. In the test the same countIf returned a different count after the view was replaced with another country, with no change to the query.

Why a view instead of a table:

A view of constants reads no data, so it costs nothing at query time. A table (say a one-row ReplacingMergeTree) pays for a read and for merge semantics you do not need.
The subquery result is a constant for the query, so it can be used where a literal is expected, such as in an INTERVAL.

Limits: the view is part of the schema, so version it with the schema; and it is a constant per query, not per session. For values that must differ per user, use settings in a profile (custom settings with a registered prefix) or query parameters ({name:Type}).

How do I make a query use a lot of memory on purpose?

To test a memory alert, a max_memory_usage limit or what your cluster does under pressure, you need a query that reliably consumes memory and does nothing else. Sorting wide rows does it: ORDER BY has to hold the whole input before it can emit the first row. Use it to verify that a profile limit actually stops a query, to trigger an alert in a non-production environment, or to see how neighbours behave when memory is tight.

Each row carries a string and an array of 1000 UInt64 values (about 8 KB), so memory grows linearly with the row count. FORMAT Null discards the output so the client does not become the bottleneck.

SELECT
    number,
    toString(number) AS str,
    arrayMap(x -> x * number, range(1000)) AS arr
FROM numbers(100000)
ORDER BY number DESC
FORMAT Null;

With 100,000 rows the peak was 1,193,868,320 bytes (about 1.1 GiB); scale numbers(...) up or down to hit the amount you want. Ten million rows would need on the order of a hundred gigabytes, so start small. Watch the peak with clickhouse-client --memory-usage --query "<the query above>", which prints the peak memory to stderr when the query ends (the value above came from that flag). After the fact, the peak is the memory_usage column of system.query_log.

Trip a limit: add one lower than the need and the query is killed with a memory error instead of taking the host down.

SELECT
    number,
    arrayMap(x -> x * number, range(1000)) AS arr
FROM numbers(100000)
ORDER BY number DESC
FORMAT Null
SETTINGS max_memory_usage = 500000000;

Code: 241. DB::Exception: Query memory limit exceeded: would use 625.56 MiB (attempt to allocate chunk of 499.53 MiB), maximum: 476.84 MiB (MEMORY_LIMIT_EXCEEDED)

Notes:

For sorts, max_bytes_before_external_sort lets the server spill to disk instead of failing. When set, it caps the memory this test uses; leave it at 0 for the test.
Never run this against a production node without a limit. Test it with a settings profile that sets max_memory_usage first (profile editing).