$ lexprog.com

// notes from an old coder -- php, databases, and the occasional rant

[November 01, 2026] ClickHouse

ClickHouse User-Defined Functions

ClickHouse User-Defined Functions

────────────────────────────────────────────────────────

ClickHouse User-Defined Functions

Tip: CREATE FUNCTION

CREATE FUNCTION double_it AS (x) -> x * 2;
SELECT double_it(5);

Gotcha: UDFs Are Session-Scoped

Functions created with CREATE FUNCTION exist only in the current session.

Tip: Lambda Expressions

SELECT arrayMap(x -> x * 2, [1, 2, 3]);

Inline functions for array operations.

Gotcha: Executable UDFs

External scripts can be called as UDFs. But they add latency for each row.

Tip: Higher-Order Functions

SELECT arrayFilter(x -> x > 10, [5, 15, 3, 20]);

arrayMap, arrayFilter, arraySort all accept lambda functions.

Gotcha: UDF Performance

UDFs run row-by-row. For bulk operations, prefer built-in functions.

Tip: Order of Columns in ORDER BY Matters Massively

ClickHouse's primary key is defined by ORDER BY. Put high-cardinality columns first for better data skipping. ORDER BY (timestamp, user_id) is very different from ORDER BY (user_id, timestamp) in query performance.

Tip: Use LowCardinality for Enum-Like Strings

Strings like status, country, browser benefit from LowCardinality(String) — it's stored as a dictionary internally, reducing storage 10x and speeding up scans.

Gotcha: Mutations Are Heavy

ALTER TABLE ... UPDATE and DELETE in ClickHouse create new parts instead of modifying in place. A single mutation on a large table can take hours and block merges. Design for append-only from day one.

Senior Insight

User-defined functions in ClickHouse (executable UDFs) let you extend ClickHouse with custom logic written in any language. I've used Python UDFs for complex string transformations and regex operations that would be painful in SQL. The performance consideration: each UDF call spawns a subprocess, so they're slow for row-by-row operations. Write UDFs to process batches of input data (table-function style) rather than individual values.

Source: ClickHouse Blog (https://clickhouse.com/blog), Altinity Blog (https://altinity.com/blog), Altinity Knowledge Base (https://kb.altinity.com/)

────────────────────────────────────────────────────────
<-- back to posts