Clickhouse 使用者自定義外部函式

山治先生發表於2022-03-31
函式

寫在前面

　　Clickhouse 從 21.11 版本開始，除了提供類似SqlServer、MySQL CREATE FUNCTION 的自定義函式之外，還有一個使用者自定義函式（UDF），與其說是“使用者自定義函式”，為了避免混淆，稱之為”使用者自定義外部函式“更為準確。官方對此功能的解釋：

ClickHouse can call any external executable program or script to process data. 
譯文：ClickHouse可以呼叫任何外部可執行程式或指令碼來處理資料。

可以呼叫外部程式或指令碼來處理資料，這對於資料建模、資料分析等等來說，無疑是殺手鐗的存在。

開始

　　示例情景：呼叫python指令碼實現向量點積運算。

　　環境：Docker、Clickhouse 21.11.4.14 、Ubuntu 20.04、Python3

1.  在config.xml裡內增加

<user_defined_executable_functions_config>*_function.xml</user_defined_executable_functions_config>

 

2.  增加custom_function.xml自定義函式的宣告檔案

　　新建custom_function.xml檔案，與config.xml、users.xml檔案是同級目錄下的，如圖

 

3. 宣告方法

　　開啟custom_function.xml檔案，編寫檔案內容如下：

<functions>
    <function>
        <type>executable</type>
        <name>custom_dotProduct</name>
        <return_type>Float32</return_type>
        <return_name>result</return_name>
        <argument>
            <type>Array(Float32)</type>
            <name>v1</name>
        </argument>
        <argument>
            <type>Array(Float32)</type>
            <name>v2</name>
        </argument>
        <format>JSONEachRow</format>
        <execute_direct>0</execute_direct>
        <command>python3 /var/lib/clickhouse/user_scripts/custom_dotProduct.py</command>
    </function>
</functions>

　　execute_direct=0，預設是1，1表示將在clickhouse的/data/user_scripts資料夾內搜尋指令碼，0表是按照使用者配置的命令搜尋指令碼路徑，建議設定為0，避免找不到執行的指令碼檔案。其他引數可以參考文件：Introduction | ClickHouse Documentation

 

4. 編寫python指令碼

#!/usr/bin/python3
import sys
import json

if __name__ == '__main__':
    for line in sys.stdin:
        dict = json.loads(line)
        ls = []
        for v in dict.values():
            ls.insert(1, list(v))
        vector1 = tuple(ls[0])
        vector2 = tuple(ls[1])
        v = sum(p * q for p, q in zip(vector1, vector2))
        data = {'result': str(v)}
        print(json.dumps(data), end='\n')
        sys.stdout.flush()

　　儲存指令碼並命名為 custom_dotProduct.py ，再放到 /var/lib/clickhouse/user_scripts 資料夾內。

　　特別需要注意是指令碼執行環境和存放路徑問題，Clickhouse如果是放到docker裡面，則需要在docker內配置python可執行的環境，其他C++、java也是如此，最起碼能保證手動執行指令碼的時候能執行。 在 custom_function.xml 宣告方法的時候，編寫的xml檔案中的command命令是容器裡面的路徑，而不是宿主機的路徑。

 

5. 至此已經完成，進行方法測試

--重新載入方法
SYSTEM RELOAD FUNCTIONS;

--檢視方法是否載入成功
SELECT * FROM system.functions WHERE name = 'custom_dotProduct';

 執行方法：

select custom_dotProduct([1,2,3],[4,5,6]);

 

最後

　　還需特別注意的是Clickhouse版本問題，在示例的python指令碼中和官網文件中的示例python指令碼取值方法不太一樣，

官方示例：

first_arg = int(value['argument_1'])
second_arg = int(value['argument_2'])

它是通過自定義配置的name獲取值：

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

而我是通過遍歷出來的：

for v in dict.values():
            ls.insert(1, list(v))

原因是Clickhouse這種取值方式必須要求在 22.3 版本以上才支援，若低於 22.3的版本用官方的取值方式是永遠報錯的（巨坑之一）。具體可以看我之前提的Issue： UDFs: JSON Bug ? · Issue #35562 · ClickHouse/ClickHouse (github.com)

　　另外，從2022年1月後，Clickhouse的Docker映象將停止 yandex/clickhouse-server 的迭代，使用新的映象地址 clickhouse/clickhouse-server  。

 

如繼續使用 yandex/clickhouse-server的映象，最新的版本號停留在 22.1.3.7 （巨坑之二）。

好了，下班！不不不，等下下班！

 

相關文章