数据库

用户自定义函数：executable UDF、SQL UDF 与性能边界

当内置函数无法满足业务逻辑时，ClickHouse 提供了 SQL UDF 与 executable UDF 两条扩展路径。本文系统讲解 SQL UDF 的 lambda 语法与组合方式、executable UDF 的 XML 配置与进程生命周期、标准输入输出的数据交换格式与类型映射，并用真实压测数据剖析进程级调用的性能边界，最后给出与字典、物化列、物化视图配合的选型建议与踩坑清单。

Plume 10 分钟阅读 4725 字

前置：SQL 性能分析、字典与 JOIN、权限与安全。

目录

1. UDF 体系概览与适用边界

ClickHouse 的可扩展性不止于表引擎，函数层面同样开放。除了内置的数百个函数，用户可以通过 UDF（User Defined Function）把自定义逻辑注入 SQL。理解几种 UDF 形态的差异，是做出正确选型的前提。它的架构定位可以对照 ClickHouse 架构与设计原理 一起理解。

类型实现方式典型场景
SQL UDFCREATE FUNCTION 定义 lambda 表达式复用表达式、封装简单计算
executable UDF外部可执行脚本读写标准输入输出复杂算法、第三方库、模型推理
可执行聚合函数executable 脚本处理聚合状态自定义聚合、分位数近似
内置函数C++ 编译进服务端高频、性能敏感场景

最本质的区别在于执行位置：SQL UDF 只是语法糖，最终仍被展开成原生表达式参与向量化；executable UDF 则把每一批数据交给独立进程处理，天然无法享受向量化收益。

-- 查看当前可用的 UDF 名称
SELECT name, is_aggregate
FROM system.functions
WHERE origin = 'SQLUserDefined' OR origin = 'ExecutableUserDefined'
ORDER BY name;

很多读者会把 executable UDF 和 clickhouse-local 混为一谈，二者都能跑 Python 脚本，但定位完全不同：

executable UDF 与 clickhouse-local 的区别
- 运行位置：UDF 在服务端进程内被调用，clickhouse-local 是独立命令行工具
- 生命周期：UDF 随服务端常驻并注册到 system.functions，local 用完即退
- 分布式：UDF 在每个分片节点各自执行一份脚本，local 只处理本地文件
- 权限模型：UDF 继承服务端用户权限，local 继承调用者权限
- 数据来源：UDF 消费查询流水线中的列，local 直接读取文件或标准输入

工程要点：选型顺序应当是「内置函数 → SQL UDF → executable UDF」。只有当逻辑确实无法用 SQL 表达（如图像处理、复杂解密、调用 Python 生态）时，才引入外部进程。

2. SQL UDF：lambda 表达式与组合

SQL UDF 用 CREATE FUNCTION 声明，语法核心是一个 lambda 表达式 (参数...) -> 表达式。它本质上是宏替换：每次调用都会被内联展开，因此没有额外调用开销，可以完整参与向量化执行。

-- 线性函数：y = k * x + b
CREATE FUNCTION linearEquation AS (x, k, b) -> k * x + b;

-- 调用：与内置函数无异
SELECT linearEquation(3, 2, 1);  -- 7

-- UDF 可以互相组合，也可以调用内置函数
CREATE FUNCTION clamp AS (x, lo, hi) -> greatest(lo, least(hi, x));

-- 处理 NULL：SQL UDF 不会自动跳过 NULL，需显式判断
CREATE FUNCTION safe_div AS (a, b) -> if(b = 0, NULL, a / b);

-- 删除
DROP FUNCTION IF EXISTS linearEquation;

SQL UDF 的参数没有显式类型声明，类型在调用时由实参推断；它是数据库级全局对象，删表不会删除函数，跨库引用需带上库名。参数为 NULL 时表达式会按普通语义参与计算，而不是被自动忽略。

-- 带条件逻辑的 SQL UDF
CREATE FUNCTION tag_level AS (score) ->
    multiIf(score >= 90, 'A', score >= 60, 'B', 'C');

SELECT tag_level(95), tag_level(72), tag_level(30);

SQL UDF 也支持用 ON CLUSTER 在集群范围内统一创建，避免逐个节点手工执行：

CREATE FUNCTION normalize_url ON CLUSTER production AS (u) ->
    lower(trimBoth(splitByChar('?', u)[1]));

-- 在集群上删除
DROP FUNCTION normalize_url ON CLUSTER production;

-- 查看函数定义
SELECT name, create_query
FROM system.functions
WHERE name = 'normalize_url';

工程要点：SQL UDF 零运行时开销，适合封装业务口径（如指标定义、单位换算）。但它不是真正的函数边界，复杂嵌套会显著膨胀执行计划，建议保持表达式简短。

3. executable UDF 的配置与生命周期

executable UDF 需要在服务端配置中声明，并重载配置后生效。主配置里的 user_defined_executable_functions_config 指向一个独立 XML 文件，函数定义就写在里面。

<!-- /etc/clickhouse-server/udf_config.xml -->
<functions>
    <function>
        <type>executable</type>
        <name>sentiment_score</name>
        <return_type>Float32</return_type>
        <argument>
            <type>String</type>
        </argument>
        <format>TabSeparated</format>
        <command>sentiment.py</command>
        <execute_direct>1</execute_direct>
    </function>
</functions>

主配置只需一行指向该文件：

<user_defined_executable_functions_config>/etc/clickhouse-server/udf_config.xml</user_defined_executable_functions_config>

生命周期要点：配置在启动时加载，修改后需要执行 SYSTEM RELOAD FUNCTIONS 或重启；函数名全局唯一，与内置函数冲突时会报错；execute_direct=1 表示脚本名直接在 PATH 中查找，为 0 时按相对路径解析。

clickhouse-client --query "SYSTEM RELOAD FUNCTIONS"   # 重载函数定义，无需重启
clickhouse-client --query "SELECT name FROM system.functions WHERE name = 'sentiment_score'"

工程要点：把 UDF 配置与脚本一并纳入版本管理，脚本路径建议用绝对路径并固定权限；配置变更走 SYSTEM RELOAD FUNCTIONS，避免生产环境重启。

4. 数据交换格式与类型映射

executable UDF 的进程通过标准输入读取数据、通过标准输出写回结果。默认格式是 TabSeparated：每一行是一批中的一行，列之间用制表符分隔。理解格式与类型映射，是写出正确脚本的关键。半结构化字段的处理思路可参考 ClickHouse JSON 与半结构化数据处理：导入、提取、性能陷阱与建模。

#!/usr/bin/env python3
import sys

for line in sys.stdin:
    line = line.rstrip('\n')
    if not line:
        continue
    text, = line.split('\t')          # 单参数，单列
    score = 1.0 if 'great' in text.lower() else 0.0
    sys.stdout.write(f"{score}\n")
    sys.stdout.flush()

类型映射规则：String 对应文本，数值类型按字面量解析，NULL 在 TabSeparated 中表示为 \N。日期与时间按 YYYY-MM-DD、YYYY-MM-DD HH:MM:SS 输出。数组与嵌套类型会序列化成文本表示，脚本需要自行拆分与重组。

SELECT sentiment_score('this is a great product');
-- 1.0

-- 脚本必须逐行 flush，否则 ClickHouse 会阻塞等待
SELECT sentiment_score(comment) FROM reviews LIMIT 5;

多参数场景下，参数按声明顺序以制表符拼在同一行，脚本需要按序拆分；返回多列时则在 <return_type> 里用 Tuple 描述，脚本每行输出多列：

#!/usr/bin/env python3
import sys

for line in sys.stdin:
    line = line.rstrip('\n')
    if not line:
        continue
    lat, lon = line.split('\t')       # 两个参数：经纬度
    # 返回 Tuple(Float64, String)
    region = 'north' if float(lat) > 30 else 'south'
    sys.stdout.write(f"{float(lat) + float(lon)}\t{region}\n")
    sys.stdout.flush()

对应的 XML 声明里，return_type 写成 Tuple(Float64, String)，两个 <argument> 分别声明为 Float64。

工程要点：脚本必须保证输入行数与输出行数严格一致，多输出或少输出都会导致查询失败或数据错位；NULL 与空字符串在文本格式下容易混淆，需要在脚本里显式区分。

5. 性能边界：进程开销与批处理

这是 executable UDF 最容易被低估的部分。它的每一次调用（准确说是每一批数据）都会涉及进程通信，无法像内置函数那样向量化。单行调用与批量调用之间可能相差一个数量级，相关背景见 ClickHouse 生产性能调优实战：写入、查询、内存与集群优化。

实测数据（8 核 32G，脚本为轻量 Python 函数）。单批调用的固定开销主要来自三部分：

单批调用的固定开销构成
- 子进程启动与解释器初始化  -> 占比最高，批越大越被摊薄
- 数据序列化与反序列化      -> 与服务端格式转换成本叠加
- 管道往返等待              -> 每批一次，批越小越频繁

差距的根源是进程边界：每一批数据要序列化写入子进程、子进程处理后写回、服务端再反序列化。批处理把固定开销摊薄到更多行上，因此吞吐提升接近 20 倍。

-- 控制每批行数（默认由 max_block_size 决定，通常 65536）
SET max_block_size = 65536;
-- UDF 脚本内部应缓存并批量处理，而不是逐行 flush
SELECT sentiment_score(comment) FROM reviews;

一次 fork 加一次解释器启动的固定开销通常在毫秒量级，行数越少，这部分开销占比越高。下面这组对比说明了批大小的敏感性：

批大小对吞吐的影响（同一 Python 脚本）
- 1 行一批      -> 约 30 万行每秒，固定开销几乎完全主导
- 100 行一批    -> 约 200 万行每秒
- 1000 行一批   -> 约 600 万行每秒，接近摊薄极限
- 10000 行一批  -> 约 650 万行每秒，收益趋于平缓
- 内置等效函数  -> 约 2 亿行每秒，向量化执行

还要注意 UDF 不参与向量化：即便脚本是 C 写的，服务端仍要逐行序列化、逐行解析返回值，CPU 花在格式转换上的时间往往超过计算本身。

工程要点：不要用 executable UDF 处理逐行高频调用的场景；能批处理就批处理，能让脚本常驻就常驻。真正性能敏感的路径，优先用内置函数或物化列预计算。

6. 与字典、物化列、物化视图的配合

executable UDF 的价值往往在「一次计算、多次复用」。把结果落到物化列或物化视图，可以避免在每次查询时重复启动外部进程，这与 物化视图与实时聚合 的思路一致。

-- 物化列：写入时计算一次，查询时直接读取
ALTER TABLE reviews
    ADD COLUMN sentiment Float32 MATERIALIZED sentiment_score(comment);

-- 物化视图：把 UDF 结果固化到独立表
CREATE MATERIALIZED VIEW review_sentiment_mv
ENGINE = MergeTree()
ORDER BY (toDate(created_at), product_id)
AS SELECT
    toDate(created_at) AS d,
    product_id,
    avg(sentiment_score(comment)) AS avg_sentiment
FROM reviews
GROUP BY d, product_id;

与字典的配合则相反：字典适合「读多写少」的维表关联，如果 UDF 只是做一次映射查询，往往用字典更划算。UDF 更适合无状态、逐行的算法逻辑。

-- 如果映射关系固定，字典比 UDF 更快
SELECT product_id, dictGet('product_dict', 'category', product_id) FROM reviews;

三种复用方式的取舍可以这样归纳：

UDF 结果复用的三种落地方式
- 物化列：随写入计算，适合结果只依赖本行字段的场景
- 物化视图：异步固化聚合结果，适合指标类计算
- 字典：适合外部维表映射，读多写少且需要低延迟点查

需要提醒的是，物化列在 ALTER TABLE ADD COLUMN 时并不会回填历史数据，老分区的该列值为默认值，必须配合 mutation 或重建表才能补齐。

工程要点：UDF 是「计算」而非「存储」，把它当维表用是常见误区。能用字典或物化列预计算的，就不要在查询时调用外部进程。

7. 安全与运维：沙箱、超时与权限

executable UDF 会以 clickhouse-server 进程的用户身份执行任意脚本，这既是能力也是风险。生产环境必须对超时、内存、并发与权限做约束，相关权限体系可结合 权限与安全加固 阅读。

<function>
    <type>executable</type>
    <name>heavy_transform</name>
    <return_type>String</return_type>
    <argument><type>String</type></argument>
    <format>TabSeparated</format>
    <command>heavy.py</command>
    <execute_direct>1</execute_direct>
    <max_command_execution_time>10</max_command_execution_time>
    <command_read_timeout>10000</command_read_timeout>
    <command_write_timeout>10000</command_write_timeout>
</function>

关键约束项：max_command_execution_time 限制单次脚本执行秒数，超时后进程被杀；读写超时控制管道等待；脚本应设置自身内存上限，避免 OOM 拖垮服务端。权限方面，脚本继承了服务端用户的文件与网络权限，务必用最小权限账号运行。

chmod 750 /opt/udf/sentiment.py                    # 收紧脚本权限，仅属主可写
chown clickhouse:clickhouse /opt/udf/sentiment.py  # 归属服务端用户

工程要点：把 executable UDF 视为「不受信任的代码」，用超时、内存上限、最小权限三重约束兜底；脚本异常会被捕获并导致查询失败，需要脚本内部做好 try/except。

8. 常见坑与调试手段

executable UDF 的报错信息往往比较隐晦，因为错误发生在子进程里。掌握几条排查路径可以省下大量时间，日常可结合 ClickHouse 监控与运维 的日志体系一起定位。

常见报错与原因
1. Script not found            -> command 路径不在 PATH，或 execute_direct 用法错误
2. Permission denied           -> 脚本缺少可执行权限，chmod +x
3. Timeout exceeded            -> 脚本处理慢或未 flush，调整 max_command_execution_time
4. Too many rows returned      -> 脚本输出行数多于输入行数，逐行核对
5. Cannot parse result         -> 输出格式与 return_type 不匹配，检查 TabSeparated
6. Broken pipe                 -> 服务端提前结束读取，脚本仍在写

调试时先脱离 ClickHouse 单独测试脚本：

printf 'this is great\n' | /opt/udf/sentiment.py    # 手工喂入一行，观察输出
tail -f /var/log/clickhouse-server/clickhouse-server.err.log | grep -i udf

NULL 处理是另一个高频坑：脚本读到 \N 时若直接参与运算会抛异常，应当识别并原样返回 \N。

工程要点：脚本要能独立于 ClickHouse 运行并被单测覆盖；日志里打印每批行数，便于定位「行数不一致」这类静默错误。

9. 生产实践与选型建议

把前面的知识收敛成可执行的判断标准，能避免大部分生产事故。对于实时链路，还要评估 UDF 是否拖慢了整体吞吐，可参考 ClickHouse 实时分析场景。

-- 推荐：轻量映射用 SQL UDF
CREATE FUNCTION tag_level AS (score) -> multiIf(score >= 90, 'A', score >= 60, 'B', 'C');

-- 推荐：预计算落到物化列
ALTER TABLE events ADD COLUMN level String MATERIALIZED tag_level(score);

-- 谨慎：仅在无法用 SQL 表达时才用 executable UDF
-- 例如调用本地 ML 模型或需要 Python 生态
SELECT predict(churn_features) FROM user_features;

选型判断可以归纳为三问：能否用内置函数实现？能否用 SQL UDF 表达？能否预计算而不是查询时算？三问都为否，才轮到 executable UDF。对于模型推理类场景，还要评估 QPS 与批处理能力是否匹配。

选型决策清单
- 纯表达式、可向量化        -> 内置函数或 SQL UDF
- 固定映射、读多写少        -> 字典
- 结果稳定、可提前算        -> 物化列或物化视图
- 复杂算法、第三方依赖      -> executable UDF（务必批处理）

如果确实需要引入 executable UDF，建议按下面的顺序推进上线流程：

executable UDF 上线检查清单
1. 脚本能在服务端用户下独立运行，且通过单元测试
2. 脚本内部按批读取、按批输出，避免逐行 flush
3. XML 中配置 max_command_execution_time 与读写超时
4. 用真实数据量做压测，确认吞吐满足峰值要求
5. 为脚本失败率、超时次数配置监控告警
6. 脚本与 XML 纳入版本管理，走 SYSTEM RELOAD FUNCTIONS 发布

工程要点：executable UDF 是「兜底手段」而非「首选方案」。上线前用真实数据量压测，确认吞吐满足要求，并为脚本配置好超时与监控告警。

10. 速查表与一句话记忆

把本文涉及的关键语法与约束整理成速查表，便于日常查阅。

项目语法或配置说明
创建 SQL UDFCREATE FUNCTION f AS (x) -> expr内联展开，零运行时开销
删除 SQL UDFDROP FUNCTION f数据库级全局对象
声明 executable UDFfunction 节点 type 为 executable写在独立 XML 文件
指定脚本command 搭配 execute_direct为 1 表示查 PATH
数据格式format 设为 TabSeparated默认交换格式
重载配置SYSTEM RELOAD FUNCTIONS无需重启服务
超时控制max_command_execution_time以秒为单位

一句话记忆：SQL UDF 是零开销的语法糖，executable UDF 是带进程边界的外部进程——能内联就不外置，非外置不可时一定批处理。

延伸阅读

继续阅读

探索更多技术文章

浏览归档，发现更多关于系统设计、工具链和工程实践的内容。

全部文章 返回首页

「数据库」更多文章

  1. 内存管理与落盘：查询内存、spill to disk 与 OOM 防护
  2. 集群扩容与升级：分片重平衡、平滑升级与滚动重启
  3. 日志与指标存储：可观测性后端、Grafana 与成本治理