メインコンテンツへスキップ
メインコンテンツへスキップ

ユーザー定義関数 (UDF)

Private preview in ClickHouse Cloud
注記

この機能は ClickHouse Cloud でプライベートプレビューとしてサポートされています。 利用を希望する場合は https://clickhouse.cloud/support から ClickHouse Support にお問い合わせください。

ClickHouse は、任意の外部実行可能プログラムやスクリプトを呼び出してデータを処理できます。

実行可能ユーザー定義関数の設定は、1つ以上の XML ファイルに配置できます。 設定ファイルへのパスは、パラメータ user_defined_executable_functions_config で指定します。

関数の設定には、次の項目が含まれます。

ParameterDescriptionRequiredDefault Value
name関数名Yes-
command実行するスクリプト名、または execute_direct が false の場合はコマンドYes-
argument引数の type と、任意の name を含む引数の説明。各引数は個別の設定で記述します。Native や JSONEachRow など、ユーザー定義関数のフォーマットで引数名がシリアライズの一部となる場合は、name の指定が必要ですYesc + argument_number
format引数をコマンドに受け渡す際に使用する format。コマンドの出力も同じフォーマットであることが想定されていますYes-
return_type戻り値の型Yes-
return_name戻り値の名前。Native や JSONEachRow など、ユーザー定義関数のフォーマットで戻り値の名前がシリアライズの一部となる場合は、戻り値の名前の指定が必要ですOptionalresult
type実行タイプ。type が executable に設定されている場合は単一のコマンドが起動されます。executable_pool に設定されている場合はコマンドのプールが作成されますYes-
max_command_execution_timeデータブロックを処理するための最大実行時間（秒）。この設定は executable_pool コマンドにのみ有効ですOptional10
command_termination_timeoutパイプがクローズされた後、コマンドが終了すべき時間（秒）。この時間を過ぎると、コマンドを実行しているプロセスに SIGTERM が送信されますOptional10
command_read_timeoutコマンドの stdout からデータを読み取る際のタイムアウト（ミリ秒）Optional10000
command_write_timeoutコマンドの stdin にデータを書き込む際のタイムアウト（ミリ秒）Optional10000
pool_sizeコマンドプールのサイズOptional16
send_chunk_headerデータのチャンクを処理に送る前に行数を送信するかどうかを制御しますOptionalfalse
execute_directexecute_direct = 1 の場合、command は user_scripts_path で指定された user_scripts フォルダ内から検索されます。追加のスクリプト引数は空白区切りで指定できます（例: script_name arg1 arg2）。execute_direct = 0 の場合、command は bin/sh -c への引数として渡されますOptional1
lifetime関数をリロードする間隔（秒）。0 に設定された場合、関数はリロードされませんOptional0
deterministic関数が決定的（同じ入力に対して常に同じ結果を返す）かどうかOptionalfalse

コマンドは STDIN から引数を読み込み、結果を STDOUT に出力しなければなりません。コマンドは引数を逐次的に処理する必要があります。つまり、あるチャンクの引数を処理した後、次のチャンクを待機しなければなりません。

実行可能なユーザー定義関数​

例​

インラインスクリプトを用いた UDF​

XML または YAML 設定を使用して、execute_direct を 0 に設定した test_function_sum を手動で作成します。

ファイル 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>


SELECT test_function_sum(2, 2);

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

Python スクリプトからの UDF​

この例では、STDIN から値を読み取り、それを文字列として返す UDF を作成します。

XML または YAML のいずれかの設定で test_function を作成します。

ファイル 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>

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

SELECT test_function_python(toUInt64(2));

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

STDIN から 2 つの値を読み取り、その合計を JSON オブジェクトとして返す​

XML または YAML の設定を使用して、名前付き引数を取り、フォーマットに JSONEachRow を指定した test_function_sum_json を作成します。

ファイル 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>

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

SELECT test_function_sum_json(2, 2);

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

command 設定でパラメータを使用する​

実行可能なユーザー定義関数は、command 設定で指定された定数パラメータを受け取ることができます（これは executable 型のユーザー定義関数でのみ動作します）。
また、シェルによる引数展開に起因する脆弱性を防ぐために、execute_direct オプションが必要です。

ファイル 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>

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

SELECT test_function_parameter_python(1)(2);

┌─test_function_parameter_python(1)(2)─┐
│ パラメータ1の値2                  │
└──────────────────────────────────────┘

シェルスクリプトを使った UDF​

この例では、各値を 2 倍にするシェルスクリプトを作成します。

ファイル 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>

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

SELECT test_shell(number) FROM numbers(10);

    ┌─test_shell(number)─┐
 1. │ 0                  │
 2. │ 2                  │
 3. │ 4                  │
 4. │ 6                  │
 5. │ 8                  │
 6. │ 10                 │
 7. │ 12                 │
 8. │ 14                 │
 9. │ 16                 │
10. │ 18                 │
    └────────────────────┘

エラー処理​

一部の関数は、データが無効な場合に例外をスローすることがあります。 この場合、クエリは中断され、エラーメッセージがクライアントに返されます。 分散処理では、いずれかのサーバーで例外が発生すると、他のサーバーもクエリの中断を試みます。

引数式の評価​

ほとんどのプログラミング言語では、特定の演算子において、引数の一方が評価されないことがあります。 通常、これは演算子 &&、||、および ?: に当てはまります。 ClickHouse では、関数（演算子）の引数は常に評価されます。 これは、行ごとに個別に計算するのではなく、列の一部をまとめて一度に評価するためです。

分散クエリ処理における関数の実行​

分散クエリ処理では、クエリ処理のできるだけ多くの段階をリモートサーバー上で実行し、残りの段階（中間結果のマージとそれ以降のすべて）はリクエスト元サーバーで実行します。

つまり、関数は異なるサーバー上で実行される場合があります。 例えば、次のクエリ SELECT f(sum(g(x))) FROM distributed_table GROUP BY h(y), では、

  • distributed_table に少なくとも 2 つのシャードがある場合、関数 g と h はリモートサーバー上で実行され、関数 f はリクエスト元サーバー上で実行されます。
  • distributed_table に 1 つのシャードしかない場合、関数 f、g、h はすべてこのシャードのサーバー上で実行されます。

関数の結果は通常、それがどのサーバーで実行されるかには依存しません。しかし、これが重要になる場合もあります。 例えば、辞書を扱う関数は、自身が実行されているサーバー上に存在する辞書を使用します。 別の例として hostName 関数は、SELECT クエリでサーバー単位の GROUP BY を行えるように、自身が実行されているサーバー名を返します。

クエリ内の関数がリクエスト元サーバーで実行されるようになっていても、それをリモートサーバー上で実行する必要がある場合は、その関数を any 集約関数でラップするか、GROUP BY 句のキーに追加することができます。

SQL ユーザー定義関数​

ラムダ式を用いてカスタム関数を作成するには、CREATE FUNCTION ステートメントを使用します。これらの関数を削除するには、DROP FUNCTION ステートメントを使用します。

関連コンテンツ​