User-defined functions and executable scripts

ClickHouse has two kinds of user-defined function, and a family of “scriptable” table objects that reuse the same machinery. Pick the lightest one that solves your problem.

1. SQL UDFs: a named expression

A SQL UDF is a lambda stored in the server. It is pure substitution, nothing more.

CREATE FUNCTION linear AS (x, a, b) -> a * x + b;

SELECT linear(3, 2, 1);                      -- 7

SELECT create_query, origin
FROM system.functions WHERE name = 'linear';

CREATE FUNCTION linear AS (x, a, b) -> ((a * x) + b)    SQLUserDefined

Use them for repeated expressions, unit conversions, small business rules. Remove with DROP FUNCTION, and in a cluster add ON CLUSTER. Creating or dropping needs the global CREATE FUNCTION / DROP FUNCTION privileges, but there is no privilege for calling one: see What ClickHouse UDFs should be able to do.

2. Executable UDFs: any program as a function

When the logic is not expressible in SQL (string similarity, a library call, a model), an executable UDF starts a process and feeds it rows. The process reads rows from stdin in the declared format and writes one output row per input row to stdout.

Two pieces: a config file and a script.

The script goes in the directory named by user_scripts_path (default /var/lib/clickhouse/user_scripts) and must be executable (chmod a+x). This one prefixes every value with its row number:

#!/usr/bin/python3
import sys

for i, line in enumerate(sys.stdin):
    print(f"{i}:{line.rstrip()}")
    sys.stdout.flush()

The function definition is XML, in a file whose name matches the server’s user_defined_executable_functions_config pattern (the default is *_function.*ml, in the config directory, e.g. /etc/clickhouse-server/add_index_function.xml):

<functions>
    <function>
        <type>executable</type>
        <name>add_index</name>
        <return_type>String</return_type>
        <argument><type>String</type></argument>
        <format>TabSeparated</format>
        <command>add_index.py</command>
        <execute_direct>1</execute_direct>
    </function>
</functions>

SELECT add_index(toString(number)) FROM numbers(3);

0:0
1:1
2:2

Things worth knowing:

• Flush after every row in the script. If you buffer output, the server waits for results that are stuck in your process.
• execute_direct = 1 runs the command directly from user_scripts_path without a shell. With 0 the command goes through /bin/sh, which allows arguments in <command> but is a bigger attack surface.
• The function runs once per block, not once per row: the process receives a whole block of rows on stdin, so the script must loop until stdin closes.
• Return types can be composite (Map(String, UInt64), arrays, tuples) as long as the output format can express them; check the text of the value your script prints against the declared format.

Several arguments

Each declared <argument> becomes a column on stdin; with TabSeparated, split the line on tabs. A string-similarity function that takes two strings:

#!/usr/bin/python3
import sys
from difflib import SequenceMatcher

for line in sys.stdin:
    a, b = line.rstrip("\n").split("\t")
    print(round(SequenceMatcher(None, a, b).ratio(), 3))
    sys.stdout.flush()

<functions>
    <function>
        <type>executable_pool</type>
        <pool_size>4</pool_size>
        <name>similarity</name>
        <return_type>Float32</return_type>
        <argument><type>String</type></argument>
        <argument><type>String</type></argument>
        <format>TabSeparated</format>
        <command>similarity.py</command>
        <execute_direct>1</execute_direct>
    </function>
</functions>

SELECT similarity('kitten', 'sitting'), similarity('abc', 'abc');
-- 0.615    1

executable_pool keeps pool_size warm processes and reuses them across queries, which matters when the script has an expensive start-up (imports, loading a model). Plain executable starts a fresh process per block.

3. Scripts as tables

The same “run a program, speak a format over pipes” mechanism exists for tables. All three forms below were run with a script directory configured as above.

Generate or import data: the script prints rows; there is no input.

#!/usr/bin/python3
for i in range(3):
    print(f"{i}\tvalue-{i}")

SELECT * FROM executable('gen.py', 'TabSeparated', 'id UInt32, v String');

CREATE TABLE scripted (id UInt32, v String) ENGINE = Executable('gen.py', TabSeparated);
SELECT * FROM scripted;

Both return 0 value-0, 1 value-1, 2 value-2. The table engine re-runs the script on every query.

Transform data: pass a subquery and the script receives its rows on stdin.

SELECT *
FROM executable('add_index.py', 'TabSeparated', 'line String',
                (SELECT toString(number) FROM numbers(3)));

Custom join logic: pass several subqueries. The first arrives on stdin; the additional ones arrive on extra file descriptors (3, 4, …) that the script opens itself. Rarely needed, but it lets a script implement a join ClickHouse does not have (a fuzzy join, say).

There is also ExecutablePool for the table engine, mirroring executable_pool.

Which one should I use?

Need	Use
Rename or shorten an expression	SQL UDF
Logic that needs a library or loop	Executable UDF (executable_pool if start-up is slow)
Pull data from an API or a file format ClickHouse lacks	executable() table function
Post-process a query result with a script	executable() with a subquery

Remember the trade-off: every executable UDF is a process on your database server running code that you have to deploy, version and secure on every node. For heavy or untrusted logic, keep it outside ClickHouse.