UDFs User Defined Functions

### UDF from shell script[​](#udf-shell-script "Direct link to UDF from shell script")

In this example, we create a shell script that multiplies each value by 2.

- XML- YAML

File `test_function_shell.xml` (`/etc/clickhouse-server/test_function_shell.xml` with default path settings).

```
<functions>
    <function>
        <type>executable</type>
        <name>test_shell</name>
        <return_type>String</return_type>
        <argument>
            <type>UInt8</type>
            <name>value</name>
        </argument>
        <format>TabSeparated</format>
        <command>test_shell.sh</command>
    </function>
</functions>
```

File `test_function_shell.yaml` (`/etc/clickhouse-server/test_function_shell.yaml` with default path settings).

```
functions:
  type: executable
  name: test_shell
  return_type: String
  argument:
    - type: UInt8
      name: value
  format: TabSeparated
  command: test_shell.sh
```

  

Create a script file `test_shell.sh` inside the `user_scripts` folder (`/var/lib/clickhouse/user_scripts/test_shell.sh` with default path settings).

```
#!/bin/bash

while read read_data;
    do printf "$(expr $read_data \* 2)\n";
done
```

```
SELECT test_shell(number) FROM numbers(10);
```

```
    ┌─test_shell(number)─┐
 1. │ 0                  │
 2. │ 2                  │
 3. │ 4                  │
 4. │ 6                  │
 5. │ 8                  │
 6. │ 10                 │
 7. │ 12                 │
 8. │ 14                 │
 9. │ 16                 │
10. │ 18                 │
    └────────────────────┘
```

## Error Handling[​](#error-handling "Direct link to Error Handling")

Some functions might throw an exception if the data is invalid.
In this case, the query is canceled and an error text is returned to the client.
For distributed processing, when an exception occurs on one of the servers, the other servers also attempt to abort the query.

## Evaluation of Argument Expressions[​](#evaluation-of-argument-expressions "Direct link to Evaluation of Argument Expressions")

In almost all programming languages, one of the arguments might not be evaluated for certain operators.
This is usually the operators `&&`, `||`, and `?:`.
In ClickHouse, arguments of functions (operators) are always evaluated.
This is because entire parts of columns are evaluated at once, instead of calculating each row separately.

## Performing Functions for Distributed Query Processing[​](#performing-functions-for-distributed-query-processing "Direct link to Performing Functions for Distributed Query Processing")

For distributed query processing, as many stages of query processing as possible are performed on remote servers, and the rest of the stages (merging intermediate results and everything after that) are performed on the requestor server.

This means that functions can be performed on different servers.
For example, in the query `SELECT f(sum(g(x))) FROM distributed_table GROUP BY h(y),`

- if a `distributed_table` has at least two shards, the functions 'g' and 'h' are performed on remote servers, and the function 'f' is performed on the requestor server.
- if a `distributed_table` has only one shard, all the 'f', 'g', and 'h' functions are performed on this shard's server.

The result of a function usually does not depend on which server it is performed on. However, sometimes this is important.
For example, functions that work with dictionaries use the dictionary that exists on the server they are running on.
Another example is the `hostName` function, which returns the name of the server it is running on in order to make `GROUP BY` by servers in a `SELECT` query.

If a function in a query is performed on the requestor server, but you need to perform it on remote servers, you can wrap it in an 'any' aggregate function or add it to a key in `GROUP BY`.

## SQL User Defined Functions[​](#sql-user-defined-functions "Direct link to SQL User Defined Functions")

Custom functions from lambda expressions can be created using the [CREATE FUNCTION](/docs/sql-reference/statements/create/function) statement. To delete these functions use the [DROP FUNCTION](/docs/sql-reference/statements/drop#drop-function) statement.

## WebAssembly User Defined Functions[​](#webassembly-user-defined-functions "Direct link to WebAssembly User Defined Functions")

Not supported in ClickHouse Cloud

Experimental feature. [Learn more.](/docs/beta-and-experimental-features#experimental-features)

WebAssembly User Defined Functions (WASM UDFs) allow you to run custom code compiled to WebAssembly inside the ClickHouse server process.

### Quick Start[​](#quick-start "Direct link to Quick Start")

Enable experimental WebAssembly support in your ClickHouse configuration:

```
<clickhouse>
    <allow_experimental_webassembly_udf>true</allow_experimental_webassembly_udf>
</clickhouse>
```

Insert your compiled WASM module into the system table:

```
INSERT INTO system.webassembly_modules (name, code)
SELECT 'my_module', base64Decode('AGFzbQEAAAA...');
```

Create a function using your WASM module:

```
CREATE FUNCTION my_function
LANGUAGE WASM
ABI ROW_DIRECT
FROM 'my_module'
ARGUMENTS (x UInt32, y UInt32)
RETURNS UInt32;
```

Use the function in your queries:

```
SELECT my_function(10, 20);
```

### More Information[​](#more-information "Direct link to More Information")

Refer to the documentation on [WebAssembly User Defined Functions](/docs/sql-reference/functions/wasm_udf) for more details.

## Related Content[​](#related-content "Direct link to Related Content")

- [User-defined functions in ClickHouse Cloud](https://clickhouse.com/blog/user-defined-functions-clickhouse-udfs)

- [Executable User Defined Functions](#executable-user-defined-functions)- [Executable User Defined Functions](#executable-user-defined-functions)- [Examples](#examples)
      - [UDF from inline script](#udf-inline)- [UDF from Python script](#udf-python)- [Read two values from `STDIN` and return their sum as a JSON object](#udf-stdin)- [Use parameters in `command` setting](#udf-parameters-in-command)- [UDF from shell script](#udf-shell-script)- [Error Handling](#error-handling)- [Evaluation of Argument Expressions](#evaluation-of-argument-expressions)- [Performing Functions for Distributed Query Processing](#performing-functions-for-distributed-query-processing)- [SQL User Defined Functions](#sql-user-defined-functions)- [WebAssembly User Defined Functions](#webassembly-user-defined-functions)
                - [Quick Start](#quick-start)- [More Information](#more-information)- [Related Content](#related-content)

Was this page helpful?