How to Create UDFs in ClickHouse

How do I create and use user-defined functions (UDFs) in ClickHouse?

CREATE FUNCTION lets you register reusable SQL expressions as user-defined functions (UDFs) directly inside ClickHouse.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is a ClickHouse UDF?

A ClickHouse user-defined function (UDF) is a named SQL expression stored in the system that you can call like any built-in function. UDFs encapsulate business logic, reduce query duplication, and improve readability.

When should I use CREATE FUNCTION?

Create a UDF when you repeatedly apply the same calculation—such as currency conversion, tax computation, or data cleaning—across many queries. Keeping logic in one place simplifies maintenance.

How do I define parameters and return types?

List parameters inside parentheses with optional types.If types are omitted, ClickHouse infers them from usage. The “->” arrow follows the parameters, pointing to the expression that returns the result.

Can I alter or drop a UDF?

ClickHouse does not support ALTER FUNCTION yet. To change a UDF, DROP it and CREATE it again with the new logic.

What permissions are required?

You need the CREATE FUNCTION privilege in the target database.Always restrict this right to trusted roles to avoid accidental or malicious changes.

Best practices for production use

Prefix UDF names with the team or project (e.g., ecom_calc_tax). Document parameters in comments. Write deterministic expressions to keep queries cache-friendly.

Example: Calculating order tax

Define an ecom_calc_tax UDF once and call it from multiple dashboards that analyze the Orders table.

Why How to Create UDFs in ClickHouse is important

How to Create UDFs in ClickHouse Example Usage


-- Use UDF to calculate discounted totals per order item
SELECT
    oi.order_id,
    ecom_calc_discount(oi.quantity * p.price) AS discount_value,
    ecom_order_total(p.price, oi.quantity)      AS total_with_tax
FROM OrderItems oi
JOIN Products p ON p.id = oi.product_id;

How to Create UDFs in ClickHouse Syntax


CREATE [OR REPLACE] FUNCTION [db.]function_name
AS ([param1 Type1], [param2 Type2], ...)
    -> expression

-- Parameters
--  db:      Target database (optional; defaults to current)
--  paramN:  Name of an input parameter
--  TypeN:   SQL data type (optional; inferred if omitted)
--  expression: Any valid ClickHouse scalar expression that returns a single value

-- Ecommerce example
CREATE FUNCTION ecom_calc_discount AS (subtotal Decimal(10,2))
    -> subtotal * 0.10;

CREATE FUNCTION ecom_order_total AS (
    price  Decimal(10,2),
    qty    UInt32,
    taxPct Float32 DEFAULT 0.08
) -> (price * qty) * (1 + taxPct);

Common Mistakes

Using non-deterministic expressions (e.g., now()) in UDFs. Such functions break query caching and can yield inconsistent results. Stick to deterministic logic.
Forgetting to qualify function names with the database when calling from another DB, causing "function not found" errors. Always prefix with db name or switch context.