User Defined Functions (UDF)

ClickHouse comes shipped with tons of useful functions that can make the lives easier for the users. ClickHouse also allows users to create and register their own custom functions, which are also known as user defined functions (UDF). This could come in handy in cases where a particular function is required which are not already available in ClickHouse.

We can create a new UDF using the CREATE statement.

Syntax

CREATE FUNCTION function_name AS (input_parameter_1, input_parameter_2, ...) -> function_expression

The function_name must be unique and must not be already available in ClickHouse. The expression can have inbuilt ClickHouse function without being recursive.

Example

Let us create a function to convert the temperature units from Celsius to Fahrenheit.

chistadata :) CREATE FUNCTION fahrenheit AS (celsius) -> plus(multiply(celsius, 1.8), 32);
              

CREATE FUNCTION fahrenheit AS celsius -> ((celsius * 1.8) + 32)

Query id: cf82f93b-32af-4997-bd58-7cc6958afd91

Ok.

0 rows in set. Elapsed: 0.002 sec. 

Let us test this function.

chistadata :) SELECT fahrenheit(35);

SELECT fahrenheit(35)

Query id: dd827c61-4bab-4035-b010-9aaca73c7594

┌─plus(multiply(35, 1.8), 32)─┐
│                          95 │
└─────────────────────────────┘

1 row in set. Elapsed: 0.001 sec. 

To remove the function, we can use the DROP statement.

chistadata :) DROP FUNCTION fahrenheit;
              

DROP FUNCTION fahrenheit

Query id: 17f7846a-86eb-4436-85be-8b93c069f700

Ok.

0 rows in set. Elapsed: 0.001 sec.

chistadata :) SELECT fahrenheit(35);

SELECT fahrenheit(35)

Query id: 258087bf-cc6f-4b20-b5b2-7936ff97b014


0 rows in set. Elapsed: 0.003 sec. 

Received exception from server (version 22.12.1):
Code: 46. DB::Exception: Received from localhost:9000. DB::Exception: Unknown function fahrenheit: While processing fahrenheit(35). (UNKNOWN_FUNCTION)

References

https://clickhouse.com/docs/en/sql-reference/statements/create/function/