ClickHouse UDF编写示例

ClickHouse UDF编写

在ClickHouse 22版本以后提供了UDF的功能,能够通过编写python，go 等脚本的方式实现自己的业务逻辑，除了提供类似SqlServer、MySQL CREATE FUNCTION 的自定义函数之外，还有一个用户自定义函数（UDF），与其说是“用户自定义函数”，为了避免混淆，称之为”用户自定义外部函数“更为准确。官方对此功能的解释：

ClickHouse can call any external executable program or script to process data.
译文：ClickHouse可以调用任何外部可执行程序或脚本来处理数据。
可以调用外部程序或脚本来处理数据，这对于数据建模、数据分析等等来说，无疑是杀手锏的存在。

在之前的版本中如果想增加UDF功能,只能通过修改源码的方式,下面介绍下UDF的写法

1. SQL方式的UDF

计算公式
如，我们想要实现下面的公式

result=number*(k+b)

编写语句

#创建函数
 CREATE FUNCTION num_formula AS (x, k, b) -> k*(x+b);
# 执行函数查看数据
SELECT number,num_formula(number, 2, 1) FROM numbers(3);

可以看到ch能够按照UDF的函数自己解析

编写python脚本

环境准备

需要安装python3的环境,这步自行安装

config.xml中添加内容

<user_defined_executable_functions_config>*_function.xml</user_defined_executable_functions_config>

添加函数的xml文件

在 /etc/clickhouse-server 下添加 getsum_function.xml

<functions>
    <function>
        <type>executable_pool</type>
        <name>getsum_function</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>getsum.py</command>
    </function>

python执行文件

在 $clickhousePath/use{r}_{s}cripts($clickhousePath是ch的安装目录)中添加python文件

cat getsum.py

#!/usr/bin/python3

import sys
import json

def _sum(arr,n):
     
    # 使用内置的 sum 函数计算
    return(sum(arr))
    
if __name__ == '__main__':
    for line in sys.stdin:
        dict = json.loads(line)
        arr = []
        for v in dict.values():
            arr.append(v)
        n = len(arr)
        ans = _sum(arr,n)
        result={'result_name': ans}
        print(json.dumps(result), end='\n')
        sys.stdout.flush()

权限

需要确认

• 1. /etc/clickhouse-server 目录为755
• 2. python文件需要执行权限
• 3. /etc/clickhouse-server 下的xml文件所有者为clickhouse ,方便起见,直接对 /etc/clickhouse-server 级联设置所有者即可

执行函数

先写到这,之后有空再补充