UDFs User Defined Functions

The command must read arguments from `STDIN` and must output the result to `STDOUT`. The command must process arguments iteratively. That is after processing a chunk of arguments it must wait for the next chunk.

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

## Examples[​](#examples "Direct link to Examples")

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

Create `test_function_sum` manually specifying `execute_direct` to `0` using either XML or YAML configuration.

- XML- YAML

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

```
<functions>
    <function>
        <type>executable</type>
        <name>test_function_sum</name>
        <return_type>UInt64</return_type>
        <argument>
            <type>UInt64</type>
            <name>lhs</name>
        </argument>
        <argument>
            <type>UInt64</type>
            <name>rhs</name>
        </argument>
        <format>TabSeparated</format>
        <command>cd /; clickhouse-local --input-format TabSeparated --output-format TabSeparated --structure 'x UInt64, y UInt64' --query "SELECT x + y FROM table"</command>
        <execute_direct>0</execute_direct>
        <deterministic>true</deterministic>
    </function>
</functions>
```

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

```
functions:
  type: executable
  name: test_function_sum
  return_type: UInt64
  argument:
    - type: UInt64
      name: lhs
    - type: UInt64
      name: rhs
  format: TabSeparated
  command: 'cd /; clickhouse-local --input-format TabSeparated --output-format TabSeparated --structure ''x UInt64, y UInt64'' --query "SELECT x + y FROM table"'
  execute_direct: 0
  deterministic: true
```

  

```
SELECT test_function_sum(2, 2);
```

```
┌─test_function_sum(2, 2)─┐
│                       4 │
└─────────────────────────┘
```

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

In this example we create a UDF which reads a value from `STDIN` and returns it as a string.

Create `test_function` using either XML OR YAML configuration.

- XML- YAML

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

```
<functions>
    <function>
        <type>executable</type>
        <name>test_function_python</name>
        <return_type>String</return_type>
        <argument>
            <type>UInt64</type>
            <name>value</name>
        </argument>
        <format>TabSeparated</format>
        <command>test_function.py</command>
    </function>
</functions>
```

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

```
functions:
  type: executable
  name: test_function_python
  return_type: String
  argument:
    - type: UInt64
      name: value
  format: TabSeparated
  command: test_function.py
```

  

Create a script file `test_function.py` inside `user_scripts` folder (`/var/lib/clickhouse/user_scripts/test_function.py` with default path settings).

```
#!/usr/bin/python3

import sys

if __name__ == '__main__':
    for line in sys.stdin:
        print("Value " + line, end='')
        sys.stdout.flush()
```

```
SELECT test_function_python(toUInt64(2));
```

```
┌─test_function_python(2)─┐
│ Value 2                 │
└─────────────────────────┘
```

### Read two values from `STDIN` and return their sum as a JSON object[​](#udf-stdin "Direct link to udf-stdin")

Create `test_function_sum_json` with named arguments and format [JSONEachRow](/docs/interfaces/formats/JSONEachRow) using either XML or YAML configuration.

- XML- YAML

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

```
<functions>
    <function>
        <type>executable</type>
        <name>test_function_sum_json</name>
        <return_type>UInt64</return_type>
        <return_name>result_name</return_name>
        <argument>
            <type>UInt64</type>
            <name>argument_1</name>
        </argument>
        <argument>
            <type>UInt64</type>
            <name>argument_2</name>
        </argument>
        <format>JSONEachRow</format>
        <command>test_function_sum_json.py</command>
    </function>
</functions>
```

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

```
functions:
  type: executable
  name: test_function_sum_json
  return_type: UInt64
  return_name: result_name
  argument:
    - type: UInt64
      name: argument_1
    - type: UInt64
      name: argument_2
  format: JSONEachRow
  command: test_function_sum_json.py
```

  

Create script file `test_function_sum_json.py` inside the `user_scripts` folder (`/var/lib/clickhouse/user_scripts/test_function_sum_json.py` with default path settings).

```
#!/usr/bin/python3

import sys
import json

if __name__ == '__main__':
    for line in sys.stdin:
        value = json.loads(line)
        first_arg = int(value['argument_1'])
        second_arg = int(value['argument_2'])
        result = {'result_name': first_arg + second_arg}
        print(json.dumps(result), end='\n')
        sys.stdout.flush()
```

```
SELECT test_function_sum_json(2, 2);
```

```
┌─test_function_sum_json(2, 2)─┐
│                            4 │
└──────────────────────────────┘
```

### Use parameters in `command` setting[​](#udf-parameters-in-command "Direct link to udf-parameters-in-command")

Executable user defined functions can take constant parameters configured in `command` setting (this works only for user defined functions with `executable` type).
It also requires the `execute_direct` option to ensure no shell argument expansion vulnerability.

- XML- YAML

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

```
<functions>
    <function>
        <type>executable</type>
        <execute_direct>true</execute_direct>
        <name>test_function_parameter_python</name>
        <return_type>String</return_type>
        <argument>
            <type>UInt64</type>
        </argument>
        <format>TabSeparated</format>
        <command>test_function_parameter_python.py {test_parameter:UInt64}</command>
    </function>
</functions>
```

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

```
functions:
  type: executable
  execute_direct: true
  name: test_function_parameter_python
  return_type: String
  argument:
    - type: UInt64
  format: TabSeparated
  command: test_function_parameter_python.py {test_parameter:UInt64}
```

  

Create script file `test_function_parameter_python.py` inside the `user_scripts` folder (`/var/lib/clickhouse/user_scripts/test_function_parameter_python.py` with default path settings).

```
#!/usr/bin/python3

import sys

if __name__ == "__main__":
    for line in sys.stdin:
        print("Parameter " + str(sys.argv[1]) + " value " + str(line), end="")
        sys.stdout.flush()
```

```
SELECT test_function_parameter_python(1)(2);
```

```
┌─test_function_parameter_python(1)(2)─┐
│ Parameter 1 value 2                  │
└──────────────────────────────────────┘
```

### 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).