Day 62: Writing Custom ClickHouse® User-Defined Functions (UDFs) for Cleaner, Reusable SQL

Writing Custom ClickHouse® User-Defined Functions (UDFs): Build Reusable SQL Logic in ClickHouse®

Day 62 of the ClickHouse® Learning Series

As your ClickHouse® deployment grows, you'll likely notice the same SQL expressions appearing across dashboards, reports, ETL jobs, and analytical queries. Whether it's calculating profit margins, formatting customer names, normalizing email addresses, or classifying revenue tiers, repeatedly writing identical expressions makes queries harder to maintain and increases the risk of inconsistencies.

ClickHouse® User-Defined Functions (UDFs) solve this problem by allowing you to package commonly used logic into reusable functions. Instead of copying complex expressions into every query, you define the logic once and invoke it whenever needed.

In this guide, we'll explore what ClickHouse® UDFs are, why they're valuable, how to create them, their different types, practical examples, performance considerations, and best practices for using them in production environments.

---

What Are ClickHouse® User-Defined Functions?

A User-Defined Function (UDF) is a custom SQL function that you create inside ClickHouse®. After it has been registered, it behaves much like any built-in ClickHouse function.

Rather than writing the same expression repeatedly, you simply call the function by name.

Imagine you frequently calculate profit margins.

Instead of writing:

round((revenue - cost) / revenue * 100, 2)

throughout your queries, you can encapsulate that calculation into a reusable function.

CREATE FUNCTION profit_margin AS
(revenue, cost) ->
round((revenue - cost) / revenue * 100, 2);

Now your queries become much cleaner.

SELECT
    product_name,
    profit_margin(revenue, cost)
FROM sales;

The business logic remains identical, but your SQL becomes significantly easier to understand.

---

Why Should You Use UDFs?

User-defined functions are not just about reducing typing—they improve maintainability and consistency across an organization.

1. Cleaner SQL Queries

Complex expressions often dominate analytical queries.

Instead of seeing long mathematical formulas or nested string operations, readers immediately understand the purpose.

Compare these examples.

Without a UDF:

SELECT
    round((revenue - cost) / revenue * 100, 2)
FROM sales;

With a UDF:

SELECT
    profit_margin(revenue, cost)
FROM sales;

The second version is much more readable.

---

2. Reusable Business Logic

Organizations usually reuse the same calculations in multiple places.

Examples include:

• Profit margin calculations
• Tax computations
• Customer segmentation
• Currency conversions
• Product categorization
• Email normalization
• String formatting
• Geographic formatting

Instead of maintaining these expressions in dozens of reports, you maintain them in one place.

---

3. Easier Maintenance

Business rules evolve.

Perhaps your profit calculation changes or your customer segmentation receives new thresholds.

If you've copied expressions into hundreds of dashboards, every report requires updating.

With UDFs, you simply modify the function definition and every dependent query automatically uses the updated logic.

---

4. Improved Team Consistency

Different developers often implement the same calculation slightly differently.

For example:

(revenue-cost)/revenue

versus

(revenue-cost)/(revenue*1.0)

or

round((revenue-cost)/revenue*100,1)

Small inconsistencies create reporting discrepancies.

A shared UDF ensures everyone uses the exact same implementation.

---

Creating Your First ClickHouse® UDF

Creating a SQL UDF is straightforward.

Suppose you frequently standardize names.

CREATE FUNCTION format_name AS
(name) ->
initCap(lower(name));

Now you can simply call it.

SELECT
    format_name('CLICKHOUSE');

Output:

Clickhouse

---

Viewing Available Functions

You can list all registered functions.

SHOW FUNCTIONS;

You can also search the system catalog.

SELECT *
FROM system.functions
WHERE name='format_name';

This is particularly useful when managing large ClickHouse environments with numerous custom functions.

---

Practical Examples

Let's explore several real-world scenarios where UDFs make SQL cleaner.

---

Example 1: Normalizing Email Addresses

Email addresses often arrive with inconsistent capitalization.

Create a reusable function.

CREATE FUNCTION normalize_email AS
(email) ->
lower(email);

Usage:

SELECT
    normalize_email('[e-mail]');

Output:

[e-mail]

Instead of repeatedly calling lower(), developers can simply use the reusable function.

---

Example 2: Revenue Classification

Many organizations categorize customers according to annual revenue.

CREATE FUNCTION revenue_tier AS
(revenue) ->
multiIf(
    revenue >= 100000,'Enterprise',
    revenue >= 10000,'Growth',
    'Starter'
);

Now classification becomes simple.

SELECT
    customer_name,
    revenue,
    revenue_tier(revenue)
FROM customers;

Result:

Customer	Revenue	Tier
Alpha Ltd	180000	Enterprise
Beta Tech	32000	Growth
Startup X	6000	Starter

---

Example 3: Country Code Formatting

Suppose you frequently need ISO-like country abbreviations.

CREATE FUNCTION country_code AS
(country) ->
upper(left(country,2));

Usage:

SELECT
    country_code('india');

Output:

IN

---

Example 4: Profit Margin

Create a reusable financial metric.

CREATE FUNCTION profit_margin AS
(revenue,cost) ->
round((revenue-cost)/revenue*100,2);

Use it throughout reporting.

SELECT
    order_id,
    profit_margin(revenue,cost)
FROM sales;

This removes repetitive financial calculations from every report.

---

Example 5: Phone Number Cleanup

Customer data often contains spaces and punctuation.

CREATE FUNCTION clean_phone AS
(phone) ->
replaceRegexpAll(phone,'[^0-9]','');

Usage:

SELECT
clean_phone('[phone]');

Output:

919876543210

---

Types of ClickHouse® UDFs

ClickHouse supports multiple approaches depending on the complexity of your requirements.

---

1. SQL User-Defined Functions

These are the most commonly used UDFs.

They are created entirely with SQL expressions.

Ideal for:

• Mathematical formulas
• String manipulation
• Date calculations
• Data normalization
• Conditional logic

Advantages:

• Fast
• Easy to create
• No external dependencies
• Fully integrated into SQL

Limitations:

• No loops
• No procedural programming
• No external libraries
• Stateless by design

---

2. Executable UDFs

Some workloads require functionality beyond SQL.

ClickHouse supports Executable UDFs that invoke external scripts written in languages such as:

• Python
• JavaScript
• Bash
• Perl
• Ruby

ClickHouse streams data into the external process and reads the returned values.

Common use cases include:

• Machine learning inference
• Advanced text processing
• NLP tokenization
• Calling specialized libraries
• Complex business rules

Unlike SQL UDFs, executable functions can keep external resources loaded, making repeated executions more efficient.

---

3. WebAssembly (WASM) UDFs

For performance-critical workloads, ClickHouse also supports WebAssembly.

Developers can write functions in languages like:

• Rust
• C++
• C

compile them into WASM modules, and execute them safely within ClickHouse.

Benefits include:

• Near-native performance
• Sandboxed execution
• Access to advanced algorithms
• Better security compared to unrestricted external scripts

---

Choosing the Right UDF

A simple guideline is:

Requirement	Recommended Option
String formatting	SQL UDF
Mathematical calculations	SQL UDF
Date transformations	SQL UDF
Customer segmentation	SQL UDF
ML model inference	Executable UDF
NLP processing	Executable UDF
High-performance custom algorithms	WASM UDF

For most analytics workloads, SQL UDFs are more than sufficient.

---

Performance Considerations

One common question is whether UDFs slow down query execution.

Fortunately, SQL UDFs are lightweight.

ClickHouse expands the function during query execution, so performance is generally comparable to writing the expression directly.

Nevertheless, there are several recommendations.

Keep Functions Simple

Avoid excessive nesting.

Poor:

functionA(functionB(functionC(functionD())))

Simple functions are easier for both developers and the optimizer.

---

Prefer Built-In Functions

If ClickHouse already provides the functionality, use it directly instead of recreating it.

Native functions are highly optimized.

---

Benchmark Critical Queries

Before deploying new UDFs into production, benchmark large analytical workloads.

Measure:

• Execution time
• CPU usage
• Memory consumption
• Rows processed

---

Review Execution Plans

Use EXPLAIN to understand query execution.

EXPLAIN
SELECT
profit_margin(revenue,cost)
FROM sales;

This helps identify potential optimization opportunities.

---

Know the Scope of SQL UDFs

Standard SQL UDFs are scalar functions.

Each input row produces exactly one output value.

If your transformation changes the number of rows—for example, expanding arrays or generating multiple rows—you should use appropriate ClickHouse table functions instead.

---

Best Practices

Use Meaningful Names

Choose names that describe business intent.

Good examples:

profit_margin()
customer_lifetime_value()
normalize_email()
revenue_tier()
country_code()

Avoid vague names like:

func1()
calc()
helper()

---

Document Every Function

Include documentation describing:

• Purpose
• Parameters
• Return value
• Example usage
• Business assumptions

Good documentation saves future developers significant time.

---

Version Functions Carefully

When business logic changes significantly, consider creating a new version instead of replacing the old one.

Example:

profit_margin_v2()

This avoids unexpected reporting changes.

---

Audit Your Function Library

Over time, organizations accumulate many UDFs.

Regularly review them for:

• Duplicate implementations
• Obsolete logic
• Unused functions
• Opportunities for consolidation

A smaller function catalog is much easier to maintain.

---

Common Use Cases

ClickHouse UDFs are particularly useful for:

• Financial calculations
• Customer segmentation
• Product categorization
• Revenue analysis
• Currency conversion
• Name formatting
• Email normalization
• Phone number cleanup
• Geographic formatting
• Data validation
• KPI calculations
• Dashboard standardization

---

Final Thoughts

ClickHouse® User-Defined Functions are a simple yet powerful feature for improving SQL quality. By encapsulating frequently used expressions into reusable functions, you can write cleaner queries, enforce consistent business rules, and reduce duplication across analytics projects.

For most reporting and data engineering tasks, SQL UDFs provide everything needed to standardize calculations and simplify maintenance. When requirements extend beyond SQL, Executable UDFs and WebAssembly (WASM) UDFs offer additional flexibility for integrating advanced logic and external languages.

As your ClickHouse environment grows, investing in a well-organized library of reusable functions can significantly improve developer productivity, reduce maintenance effort, and ensure that business logic remains consistent across dashboards, ETL pipelines, and analytical applications.

Whether you're building internal reporting systems, customer analytics, or enterprise-scale data platforms, mastering User-Defined Functions is an important step toward writing cleaner, more maintainable, and scalable ClickHouse SQL.

Read more... https://www.quantrail-data.com/writing-custom-clickhouse-user-defined-functions-udfs