Introduction
ClickHouse® comes with hundreds of built-in functions covering string manipulation, date/time
operations, mathematical calculations, aggregations, and more. For most use cases, these
built-in functions are more than sufficient.
However, there are situations where you need custom logic that no built-in function covers such
as a proprietary scoring algorithm, a domain-specific transformation, or a complex encoding
scheme. In these cases, ClickHouse® allows you to extend its functionality through
User-Defined Functions (UDFs).
While ClickHouse® supports SQL User-Defined Functions (UDFs) and Executable UDFs,
developers can also extend ClickHouse® by implementing custom C++ functions directly
within the ClickHouse source code. This approach delivers the highest performance because
the function becomes part of the ClickHouse execution engine.
In this blog, we'll focus on C++ UDFs - the most performant approach for extending
ClickHouse® with custom functions.
What is a UDF?
A User-Defined Function (UDF) is a custom function you write yourself and register with
ClickHouse® so it can be used in SQL queries - just like any built-in function.
sql
-- Using a built-in function
SELECT upper(name) FROM users;
-- Using a custom UDF
SELECT my_custom_function(name) FROM users;
Once registered, a UDF behaves exactly like a built-in function - it can be used in SELECT,
WHERE, GROUP BY, and any other SQL clause.
Ways to Extend ClickHouse®
ClickHouse® offers three approaches for adding custom functionality.
Method Language Performance Best For
SQL UDF SQL High Simple reusable expressions
Executable UDF Python, Shell,
etc.
Moderate External business logic
Custom C++ Function C++ Highest Performance-critical functionality
For most use cases, SQL UDFs or Executable UDFs are sufficient. Custom C++ functions are
typically reserved for advanced scenarios where maximum performance is required.
Why Use C++ UDFs?
C++ UDFs are the highest performance option because:
● They are compiled to native machine code — no interpreter overhead
● They run inside the ClickHouse® process — no inter-process communication
● They have direct access to ClickHouse® column types — no serialization overhead
● They can process entire columns at once using vectorized execution
This makes C++ UDFs suitable for performance-critical custom logic that needs to run over
billions of rows.
Prerequisites
Before writing a C++ UDF, make sure you have:
● ClickHouse® installed and running
● A C++ compiler (g++ or clang++)
● Basic familiarity with C++
● ClickHouse® source code (for header files)
● cmake installed
SQL-Based UDFs (Lambdas)
Before writing a C++ UDF, consider whether a SQL-based UDF (available from ClickHouse®
v21.11+) can solve your problem. These are much simpler to write and maintain.
Create a simple SQL UDF:
CREATE FUNCTION multiply_by_two AS (x) -> x * 2;
Use it in a query:
SELECT
amount
multiply_by_two(amount) AS doubled_amount
FROM default.orders;
Output:
| amount | doubled_amount |
|------------|------------------------|
| 1200.00 | 2400.00 |
| 450.00 | 900.00 |
| 890.00 | 1780.00 |
Use SQL UDFs first - they are simpler to write, easier to maintain, and perform well for
most use cases. Only reach for C++ UDFs when SQL expressions are not sufficient or
performance is critical.
Executable UDFs - External Scripts
Executable UDFs allow you to call external scripts written in any language( Python, Go, etc..,).
ClickHouse® communicates with the script via stdin/stdout.
Example: Python UDF
Step 1: Write the Python script (categorize_amount.py)
python
!/usr/bin/env python3
/usr/local/bin/categorize_amount.py
import sys
for line in sys.stdin:
amount = float(line.strip())
if amount >= 1000:
print("Premium")
elif amount >= 500:
print("Standard")
else:
print("Basic")
Make it executable:
bash
chmod +x /usr/local/bin/categorize_amount.py
Step 2: Register the UDF in ClickHouse®
Create a configuration file in /etc/clickhouse-server/:
xml
executable
categorize_amount
String
Float64
amount
TabSeparated
categorize_amount.py
Step 3: Restart ClickHouse®
bash
sudo systemctl restart clickhouse-server
Step 4: Use the UDF in a query
SELECT
order_id,
amount,
categorize_amount(amount) AS tier
FROM default.orders;
Output:
| order_id | amount | tier |
|---------- |--------- |----------|
| 1 | 1200.00 | Premium |
| 2 | 450.00 | Basic |
| 3 | 890.00 | Standard |
C++ UDFs - Custom Native Functions
When maximum performance is required, ClickHouse® can be extended by implementing
custom C++ functions directly in its source code. Unlike Executable UDFs, these functions
become part of the ClickHouse execution engine and run natively without external processes.
Note: ClickHouse® does not currently support loading arbitrary C++ UDFs as
standalone plugins. Instead, custom C++ functions are added to the ClickHouse
source code, registered with the function factory, and become available after
rebuilding the server.
How Custom C++ Functions Work
Write C++ Function
│
▼
Implement IFunction Interface
│
▼
Register with FunctionFactory
│
▼
Build ClickHouse
│
▼
Restart ClickHouse
│
▼
Use in SQL Queries
Step 1: Set Up the Build Environment
you must build ClickHouse from source to add a native function, clone the repository
along with its submodules:
Install the required development tools and clone the ClickHouse® source code.
Install required build tools
sudo apt install -y build-essential cmake git
Clone the ClickHouse codebase
git clone https://github.com/ClickHouse/ClickHouse.git
cd ClickHouse
git submodule update --init --recursive
Step 2: Implement the IFunction Interface
ClickHouse functions process data column-by-column using vectorized execution. You
must inherit from the IFunction interface and implement its core methods.
Create your function file (e.g., src/Functions/FunctionMultiplyByTwo.cpp):
cpp
include
include
include
include
namespace DB
{
class FunctionMultiplyByTwo : public IFunction
{
public:
static constexpr auto name = "multiplyByTwo";
String getName() const override
{
return name;
}
...
};
Inside the execution logic, process the input column and return the transformed result.
Step 3: Register the Function
Register the function with ClickHouse's FunctionFactory.
At the bottom of your implementation file, you must register your class with ClickHouse's
FunctionFactory so that the parser can map the SQL string to your execution logic.
REGISTER_FUNCTION(MultiplyByTwo)
{
factory.registerFunction();
}
Step 4: Compile and Run
Once your files are in place, compile the code base and spin up your newly
extended server.
Rebuild ClickHouse so the new function becomes part of the server.
Generate the build files via CMake
mkdir build && cd build
cmake .. -G Ninja
Compile the ClickHouse binary
ninja clickhouse
Start your custom compiled server
sudo systemctl restart clickhouse-server
Step 5: Execute via SQL
The function is natively embedded within the query engine and can be used
immediately without any prior registration or DLL loading statements:
SELECT multiplyByTwo(25);
Output
multiplyByTwo
50
Comparing UDF Types
| Feature | SQL UDF | Executable UDF | C++ UDF |
|----------------------|----------------|-----------------|-----------------|
| Ease of writing | Very easy | Easy | Complex |
| Performance | High | Moderate | Highest |
| Language | SQL | Any | C++ |
| Setup complexity | None | Low | High |
| Restart required | No | Yes | Yes |
| Best for | Simple logic | Prototyping | Max performance |
| Available since | v21.11 | v21.11 | Always |
When to Use Each UDF Type
Use SQL UDF when:
● The logic can be expressed in SQL
● You need quick iteration without restarting ClickHouse®
● The transformation is simple (case statements, arithmetic, string operations)
Use Executable UDF when:
● You need logic that SQL cannot express
● You want to use Python, R, or another language
● Performance is not the primary concern
● Prototyping a new function before optimizing
Use C++ UDF when:
● Maximum performance is required
● The function will run over billions of rows
● You need direct access to ClickHouse® internal column types
● The logic is complex and cannot be expressed in SQL or external scripts
Best Practices
● Start with SQL UDFs — they are the simplest and often sufficient.
● Test executable UDFs before investing in C++ implementation — validate the logic first.
● Always restart ClickHouse® after registering executable or C++ UDFs.
● Use meaningful function names — avoid names that conflict with built-in functions.
● Document your UDFs — add comments explaining what the function does, its inputs,
and outputs.
● Monitor performance — use system.query_log to compare query times before and
after adding a UDF.
● Version control your UDF code — treat UDF source files like application code.
Quick Reference
| Task | Command / Action |
|-----------------------------|-----------------------------------------------|
| Create SQL UDF | CREATE FUNCTION name AS (args) -> expression |
| Drop SQL UDF | DROP FUNCTION name |
| List all UDFs | SELECT * FROM system.functions |
| Register executable UDF | Add XML config to /etc/clickhouse-server/ |
| Register C++ UDF | Copy .so file + add XML config |
| Reload after config change | sudo systemctl restart clickhouse-server |
| UDF storage directory | /var/lib/clickhouse/user_defined/ |
Final Thoughts
ClickHouse® UDFs provide a powerful way to extend the database with custom logic when
built-in functions are not sufficient. For most use cases, SQL UDFs offer the best balance of
simplicity and performance. For more complex logic, executable UDFs allow you to use any
language. When maximum performance is required, C++ UDFs deliver native speed with direct
access to ClickHouse® internals.
Start simple — write a SQL UDF first. If performance becomes an issue or the logic is too
complex for SQL, move to executable UDFs or C++ as needed.
References
● ClickHouse® Documentation — User Defined Functions
● ClickHouse® Documentation — Executable UDFs
● ClickHouse® Documentation — CREATE FUNCTION
● ClickHouse® Documentation — system.functions
● ClickHouse® GitHub — Function Examples
Top comments (0)