Lesser-known ClickHouse functions and features

None of these deserves its own article. All of them have saved someone an afternoon.

format(): placeholders in strings

format substitutes {} placeholders with its arguments. Placeholders can be numbered, repeated and reordered; {{ and }} produce literal braces.

SELECT
    format('{}-{}', 'a', 'b'),
    format('{1}{0}{1}', 'x', 'y'),
    format('{{literal}} {0}', 5);

a-b   yxy   {literal} 5

It is the readable alternative to a chain of concat() calls when you build messages or URLs. Do not confuse it with the format table function (SELECT * FROM format(JSONEachRow, ...)), which parses text as table data and is unrelated.

$$ ... $$: strings without escaping

Dollar-quoted strings, as in PostgreSQL, take everything literally between the markers. No quote or backslash needs escaping:

SELECT $$a'b"c\d$$ AS s, length($$a'b"c\d$$) AS len;

a'b"c\d   7

(The client may print the value with escapes added for display; the stored length is 7: backslash included.) They are excellent for embedding JSON, regular expressions and multi-line text in queries, and for passing a whole block of data to the format table function. They combine with format, too: format($${0}x{1}$$, 'a', 'b') returns axb.

Short-circuit evaluation

By default, if, multiIf, and and or do not evaluate a branch for rows that do not need it. This is what makes the usual guard pattern safe:

SELECT if(x = 0, 0, intDiv(10, x))
FROM (SELECT arrayJoin([0, 2, 5]) AS x);

With the default setting this returns 0, 5, 2. Turn the optimisation off and the division is computed for every row, including x = 0:

SELECT if(x = 0, 0, intDiv(10, x))
FROM (SELECT arrayJoin([0, 2, 5]) AS x)
SETTINGS short_circuit_function_evaluation = 'disable';
-- DB::Exception: Division by zero (ILLEGAL_DIVISION)

The setting short_circuit_function_evaluation takes enable (default; short-circuit only functions where it pays off), force_enable (all functions) and disable. Beyond preventing exceptions, it is a real optimisation: an expensive function in a rarely taken branch only runs for the rows that need it.

throwIf: fail on purpose

throwIf(condition, message) raises an exception when the condition is non-zero for any row. Use it for data-quality assertions in a pipeline query and for testing error handling:

SELECT throwIf(number = 3, 'Too many') FROM numbers(10);
-- Code: 395. DB::Exception: Too many (FUNCTION_THROW_IF_VALUE_IS_NON_ZERO)

With allow_custom_error_code_in_throwif = 1 a third argument, an Int8/Int16/Int32, chooses the error code, so a calling program can react to a specific failure:

SELECT throwIf(number = 3, 'Too many', toInt32(241)) FROM numbers(10)
SETTINGS allow_custom_error_code_in_throwif = 1;
-- Code: 241 (MEMORY_LIMIT_EXCEEDED)

Code 241 is MEMORY_LIMIT_EXCEEDED, the real code a memory-limited query throws. Faking it lets you test how your loader reacts (cleaning up a half-written target table, raising an alert) without having to provoke a real out-of-memory condition.

Functions that change the number of rows or columns

Two functions break the rule that a function maps a row to a value:

• arrayJoin(arr) multiplies rows: one input row becomes one output row per array element.
• untuple(t) changes the column count: the tuple expands into one column per element.

SELECT arrayJoin([1, 2, 3]) AS x, 'k' AS k;     -- three rows
SELECT untuple((1, 'a'));                       -- two columns

Both are handy and both surprise people the first time they sit inside a bigger expression, because the rest of the row is repeated for every generated row.

The .null subcolumn

A Nullable column is stored as two files: the values and a null map. You can read just the null map, as a UInt8 (1 = NULL), with the .null subcolumn:

SELECT n.null FROM (SELECT CAST(NULL AS Nullable(UInt8)) AS n);    -- 1

When optimize_functions_to_subcolumns is on, ClickHouse rewrites isNull(n) into this subcolumn read on its own, which avoids reading the values file. Writing n.null explicitly has the same effect.

The running* functions, and what replaced them

Older ClickHouse had runningDifference, runningAccumulate, neighbor and friends (the running* family). They worked on the block the server happened to process, so the result depended on how the data was split across threads and blocks. On 26.4, runningDifference and neighbor refuse to run unless you set allow_deprecated_error_prone_window_functions:

Function runningDifference is deprecated since its usage is error-prone (see docs).
Please use proper window function ... (DEPRECATED_FUNCTION)

Write a window function instead; with an explicit ORDER BY the result is well defined:

SELECT number, sum(number) OVER (ORDER BY number) AS running_sum FROM numbers(5);
-- 0 0 / 1 1 / 2 3 / 3 6 / 4 10

The full recipe for neighbor and runningDifference is in Moving to the new analyzer.