UDFs 用户自定义函数

ClickHouse 支持多种类型的用户自定义函数 (UDFs) ：

可执行 UDFs 会启动外部程序或脚本 (Python、Bash 等) ，并通过 STDIN / STDOUT 以流式方式向其传输数据块。可用于在无需重新编译 ClickHouse 的情况下集成现有代码或工具。与进程内方案相比，它们的单次调用开销更高，因此更适合较重的逻辑，或需要不同运行时的场景。
SQL UDFs 使用 CREATE FUNCTION 通过纯 SQL 定义。它们会被内联/展开到查询计划中 (不存在进程边界) ，因此开销较低，非常适合复用表达式逻辑或简化复杂的计算列。
Experimental WebAssembly UDFs 会在服务器进程内的沙箱中运行编译为 WebAssembly 的代码。与外部可执行程序相比，它们的单次调用开销更低；与原生扩展相比，又具备更好的隔离性，因此适合用可编译为 WASM 的语言 (如 C/C++/Rust) 编写自定义算法。

可执行用户自定义函数

此功能目前在 ClickHouse Cloud 中处于私有预览阶段。如需开通，请通过 https://clickhouse.cloud/support 联系 ClickHouse 支持团队。

ClickHouse 可以调用任意外部可执行程序或脚本来处理数据。可执行用户自定义函数的配置可位于一个或多个 XML 文件中。配置路径由 user_defined_executable_functions_config 参数指定。函数配置包含以下设置：

Parameter	Description	Required	Default Value
`name`	函数名称	是	-
`command`	要执行的脚本名称；如果 `execute_direct` 为 false，则为要执行的命令	是	-
`argument`	参数描述，包括参数的 `type` 以及可选的 `name`。每个参数都在单独的配置项中描述。如果参数名称是用户自定义函数序列化格式的一部分 (例如 Native 或 JSONEachRow) ，则必须指定名称	是	`c` + argument_number
`format`	向命令传递参数时使用的格式。命令输出也应使用相同的格式	是	-
`return_type`	返回值的类型	是	-
`return_name`	返回值名称。如果返回值名称是用户自定义函数序列化格式的一部分 (例如 Native 或 JSONEachRow) ，则必须指定返回值名称	可选	`result`
`type`	可执行类型。如果 `type` 设置为 `executable`，则会启动单个命令；如果设置为 `executable_pool`，则会创建命令池	是	-
`max_command_execution_time`	处理数据块的最大执行时间 (秒) 。此设置仅对 `executable_pool` 命令有效	可选	`10`
`command_termination_timeout`	管道关闭后，命令应在多少秒内结束执行。超过该时间后，会向执行该命令的进程发送 `SIGTERM`	可选	`10`
`command_read_timeout`	从命令的 stdout 读取数据的超时时间 (毫秒)	可选	`10000`
`command_write_timeout`	向命令的 stdin 写入数据的超时时间 (毫秒)	可选	`10000`
`pool_size`	命令池大小	可选	`16`
`send_chunk_header`	控制在向进程发送一块数据之前，是否先发送行数	可选	`false`
`execute_direct`	如果 `execute_direct` = `1`，则会在 user_scripts_path 指定的 user_scripts 文件夹中查找 `command`。可使用空白字符分隔符指定额外的脚本参数。例如：`script_name arg1 arg2`。如果 `execute_direct` = `0`，则 `command` 会作为参数传递给 `bin/sh -c`	可选	`1`
`lifetime`	函数的重载间隔 (秒) 。如果设置为 `0`，则不会重新加载该函数	可选	`0`
`deterministic`	函数是否为确定性的 (即对相同输入返回相同结果)	可选	`false`
`stderr_reaction`	如何处理命令的 stderr 输出。取值：`none` (忽略) 、`log` (立即记录所有 stderr) 、`log_first` (退出后记录前 4 KiB) 、`log_last` (退出后记录后 4 KiB) 、`throw` (一旦有任何 stderr 输出就立即抛出异常) 。当使用 `log_first` 或 `log_last` 且退出码非零时，stderr 内容会包含在异常消息中	可选	`log_last`
`check_exit_code`	如果为 true，ClickHouse 会检查命令的退出码。非零退出码会导致异常	可选	`true`

命令必须从 STDIN 读取参数，并将结果输出到 STDOUT。命令必须以迭代方式处理参数。也就是说，处理完一批参数后，它必须等待下一批参数。

可执行用户自定义函数

示例

来自内联脚本的 UDF

通过 XML 或 YAML 配置手动创建 test_function_sum，并将 execute_direct 指定为 0。

XML
YAML

文件 test_function.xml (默认路径设置下为 /etc/clickhouse-server/test_function.xml) 。

/etc/clickhouse-server/test_function.xml

<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>

文件 test_function.yaml (默认路径设置下为 /etc/clickhouse-server/test_function.yaml) 。

/etc/clickhouse-server/test_function.yaml

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

Query

SELECT test_function_sum(2, 2);

Result

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

基于 Python 脚本的 UDF

在此示例中，我们将创建一个 UDF，它从 STDIN 读取一个值，并将其作为字符串返回。使用 XML 或 YAML 配置创建 test_function。

XML
YAML

文件 test_function.xml (默认路径设置下为 /etc/clickhouse-server/test_function.xml) 。

/etc/clickhouse-server/test_function.xml

<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>

文件 test_function.yaml (默认路径设置下为 /etc/clickhouse-server/test_function.yaml) 。

/etc/clickhouse-server/test_function.yaml

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

在 user_scripts 文件夹中创建脚本文件 test_function.py (默认路径设置下为 /var/lib/clickhouse/user_scripts/test_function.py) 。

#!/usr/bin/python3

import sys

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

Query

SELECT test_function_python(toUInt64(2));

Result

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

从 `STDIN` 读取两个值，并将它们的和作为 JSON 对象返回

使用 XML 或 YAML 配置，以命名参数和 JSONEachRow 格式创建 test_function_sum_json。

XML
YAML

文件 test_function.xml (默认路径设置下为 /etc/clickhouse-server/test_function.xml) 。

/etc/clickhouse-server/test_function.xml

<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>

文件 test_function.yaml (默认路径设置下为 /etc/clickhouse-server/test_function.yaml) 。

/etc/clickhouse-server/test_function.yaml

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

在 user_scripts 文件夹中创建脚本文件 test_function_sum_json.py (默认路径设置下为 /var/lib/clickhouse/user_scripts/test_function_sum_json.py) 。

#!/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()

Query

SELECT test_function_sum_json(2, 2);

Result

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

在 `command` 设置中使用参数

可执行用户自定义函数可以接收在 command 设置中配置的常量参数 (仅适用于 executable 类型的用户自定义函数) 。此外，还需要启用 execute_direct 选项，以避免 shell 参数展开漏洞。

XML
YAML

文件 test_function_parameter_python.xml (默认路径设置下为 /etc/clickhouse-server/test_function_parameter_python.xml) 。

/etc/clickhouse-server/test_function_parameter_python.xml

<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>

文件 test_function_parameter_python.yaml (默认路径设置下为 /etc/clickhouse-server/test_function_parameter_python.yaml) 。

/etc/clickhouse-server/test_function_parameter_python.yaml

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}

在 user_scripts 文件夹中创建脚本文件 test_function_parameter_python.py (默认路径设置下为 /var/lib/clickhouse/user_scripts/test_function_parameter_python.py) 。

#!/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()

Query

SELECT test_function_parameter_python(1)(2);

Result

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

通过 shell 脚本创建 UDF

在本示例中，我们将创建一个 shell 脚本，把每个值乘以 2。

XML
YAML

文件 test_function_shell.xml (默认路径配置下为 /etc/clickhouse-server/test_function_shell.xml) 。

/etc/clickhouse-server/test_function_shell.xml

<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>

文件 test_function_shell.yaml (默认路径配置下为 /etc/clickhouse-server/test_function_shell.yaml) 。

/etc/clickhouse-server/test_function_shell.yaml

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

在 user_scripts 文件夹中创建脚本文件 test_shell.sh (默认路径配置下为 /var/lib/clickhouse/user_scripts/test_shell.sh) 。

/var/lib/clickhouse/user_scripts/test_shell.sh

#!/bin/bash

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

Query

SELECT test_shell(number) FROM numbers(10);

Result

    ┌─test_shell(number)─┐
│ 0                  │
│ 2                  │
│ 4                  │
│ 6                  │
│ 8                  │
│ 10                 │
│ 12                 │
│ 14                 │
│ 16                 │
│ 18                 │
    └────────────────────┘

错误处理

如果数据无效，某些函数可能会抛出异常。此时，查询会被取消，并向客户端返回错误信息。对于分布式处理，当其中一台服务器上发生异常时，其他服务器也会尝试中止该查询。

参数表达式的求值

在几乎所有编程语言中，对于某些运算符，某个参数可能不会被求值。通常是运算符 &&、|| 和 ?:。在 ClickHouse 中，函数 (运算符) 的参数始终都会被求值。这是因为系统会一次性对整块列数据进行求值，而不是逐行分别计算。

分布式查询处理中函数的执行

在分布式查询处理中，会尽可能多地在远程服务器上完成查询处理的各个阶段，其余阶段 (合并中间结果及之后的所有操作) 则在请求方服务器上完成。这意味着，函数可能会在不同的服务器上执行。例如，在查询 SELECT f(sum(g(x))) FROM distributed_table GROUP BY h(y), 中：

如果 distributed_table 至少有两个分片，则函数 ‘g’ 和 ‘h’ 在远程服务器上执行，而函数 ‘f’ 在请求方服务器上执行。
如果 distributed_table 只有一个分片，则 ‘f’、‘g’ 和 ‘h’ 这几个函数都会在该分片所在的服务器上执行。

函数的结果通常与它在哪台服务器上执行无关。但有时这一点很重要。例如，使用字典的函数会使用其运行所在服务器上的字典。另一个例子是 hostName 函数，它返回其运行所在服务器的名称，以便在 SELECT 查询中按服务器进行 GROUP BY。如果查询中的某个函数是在请求方服务器上执行的，但你需要它在远程服务器上执行，可以将其包裹在 ‘any’ 聚合函数中，或者将其添加到 GROUP BY 键中。

SQL 用户自定义函数

可使用 CREATE FUNCTION 语句基于 Lambda 表达式创建自定义函数。要删除这些函数，请使用 DROP FUNCTION 语句。

WebAssembly 用户自定义函数

WebAssembly 用户自定义函数 (WASM UDF) 允许你在 ClickHouse 服务器进程中运行编译为 WebAssembly 的自定义代码。

快速入门

在 ClickHouse 配置中启用 Experimental WebAssembly 支持：

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

将编译好的 WASM 模块插入系统表：

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

使用你的 WASM 模块创建函数：

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

在查询中使用此函数：

SELECT my_function(10, 20);

SQL 参考文档

用户自定义函数（UDFs）

UDFs 用户自定义函数

可执行用户自定义函数

可执行用户自定义函数

示例

来自内联脚本的 UDF

基于 Python 脚本的 UDF

从 `STDIN` 读取两个值，并将它们的和作为 JSON 对象返回

在 `command` 设置中使用参数

通过 shell 脚本创建 UDF

错误处理

参数表达式的求值

分布式查询处理中函数的执行

SQL 用户自定义函数

WebAssembly 用户自定义函数

快速入门

更多信息

​UDFs 用户自定义函数

​可执行用户自定义函数

​可执行用户自定义函数

​示例

​来自内联脚本的 UDF

​基于 Python 脚本的 UDF

​从 STDIN 读取两个值，并将它们的和作为 JSON 对象返回

​在 command 设置中使用参数

​通过 shell 脚本创建 UDF

​错误处理

​参数表达式的求值

​分布式查询处理中函数的执行

​SQL 用户自定义函数

​WebAssembly 用户自定义函数

​快速入门

​更多信息

​相关文章

UDFs 用户自定义函数

可执行用户自定义函数

可执行用户自定义函数

示例

来自内联脚本的 UDF

基于 Python 脚本的 UDF

从 `STDIN` 读取两个值，并将它们的和作为 JSON 对象返回

在 `command` 设置中使用参数

通过 shell 脚本创建 UDF

错误处理

参数表达式的求值

分布式查询处理中函数的执行

SQL 用户自定义函数

WebAssembly 用户自定义函数

快速入门

更多信息

相关文章