Перейти к основному содержанию
Перейти к основному содержанию

Пользовательская функция (UDF)

Private preview in ClickHouse Cloud
Примечание

Эта возможность поддерживается в закрытом предварительном просмотре в ClickHouse Cloud. Пожалуйста, свяжитесь со службой поддержки ClickHouse по адресу https://clickhouse.cloud/support для получения доступа.

ClickHouse может вызывать любую внешнюю исполняемую программу или скрипт для обработки данных.

Конфигурация исполняемых пользовательских функций может находиться в одном или нескольких XML-файлах. Путь к конфигурации указывается в параметре user_defined_executable_functions_config.

Конфигурация функции содержит следующие настройки:

ParameterDescriptionRequiredDefault Value
nameИмя функцииYes-
commandИмя скрипта для выполнения или команда, если execute_direct имеет значение falseYes-
argumentОписание аргумента с type и необязательным name аргумента. Каждый аргумент описывается в отдельной настройке. Указание имени требуется, если имена аргументов являются частью сериализации для формата пользовательской функции, такого как Native или JSONEachRowYesc + argument_number
formatФормат, в котором аргументы передаются команде. Ожидается, что вывод команды также будет использовать тот же форматYes-
return_typeТип возвращаемого значенияYes-
return_nameИмя возвращаемого значения. Указание имени возвращаемого значения требуется, если оно является частью сериализации для формата пользовательской функции, такого как Native или JSONEachRowOptionalresult
typeТип исполняемой сущности. Если type имеет значение executable, то запускается одна команда. Если задано executable_pool, то создаётся пул командYes-
max_command_execution_timeМаксимальное время выполнения в секундах для обработки блока данных. Эта настройка применяется только для команд executable_poolOptional10
command_termination_timeoutВремя в секундах, в течение которого команда должна завершить работу после закрытия её канала. По истечении этого времени процессу, выполняющему команду, отправляется сигнал SIGTERMOptional10
command_read_timeoutТаймаут чтения данных из stdout команды в миллисекундахOptional10000
command_write_timeoutТаймаут записи данных в stdin команды в миллисекундахOptional10000
pool_sizeРазмер пула командOptional16
send_chunk_headerОпределяет, нужно ли отправлять количество строк перед отправкой блока данных на обработкуOptionalfalse
execute_directЕсли execute_direct = 1, то command будет искаться в папке user_scripts, заданной параметром user_scripts_path. Дополнительные аргументы скрипта можно указать, используя пробел в качестве разделителя. Пример: script_name arg1 arg2. Если execute_direct = 0, command передаётся как аргумент для bin/sh -cOptional1
lifetimeИнтервал перезагрузки функции в секундах. Если установлено значение 0, функция не перезагружаетсяOptional0
deterministicОпределяет, является ли функция детерминированной (возвращает одинаковый результат для одинаковых входных данных)Optionalfalse

Команда должна читать аргументы из STDIN и выводить результат в STDOUT. Команда должна обрабатывать аргументы итеративно, то есть после обработки блока аргументов она должна ожидать следующий блок.

Выполнимые пользовательские функции​

Примеры​

UDF из inline-скрипта​

Создайте test_function_sum, вручную установив execute_direct в 0, используя конфигурацию в формате XML или YAML.

Файл 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 │
└─────────────────────────┘

UDF из скрипта на Python​

В этом примере мы создаём UDF, который читает значение из STDIN и возвращает его в виде строки.

Создайте test_function, используя конфигурацию в формате XML или YAML.

Файл 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.py в каталоге user_scripts (/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 и вернуть их сумму в виде JSON-объекта​

Создайте test_function_sum_json с именованными аргументами и форматом JSONEachRow, используя конфигурацию в формате XML или YAML.

Файл 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_sum_json.py в каталоге user_scripts (/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>

Создайте файл скрипта test_function_parameter_python.py в каталоге user_scripts (/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 на основе shell-скрипта​

В этом примере мы создаём shell-скрипт, который умножает каждое значение на 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>

Создайте файл скрипта test_shell.sh в каталоге user_scripts (/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 содержит как минимум два шарда, функции g и h выполняются на удалённых серверах, а функция f выполняется на сервере, инициировавшем запрос;
  • если distributed_table содержит только один шард, все функции f, g и h выполняются на сервере этого шарда.

Результат выполнения функции обычно не зависит от того, на каком сервере она выполняется. Однако иногда это важно. Например, функции, работающие со словарями, используют словарь, размещённый на том сервере, на котором они выполняются. Другой пример — функция hostName, которая возвращает имя сервера, на котором она выполняется, чтобы можно было сделать GROUP BY по серверам в запросе SELECT.

Если функция в запросе выполняется на сервере, инициировавшем запрос, но вам нужно выполнить её на удалённых серверах, вы можете обернуть её в агрегатную функцию any или добавить её в ключ группировки в GROUP BY.

Определяемые пользователем SQL-функции​

Пользовательские функции на основе лямбда-выражений можно создавать с помощью оператора CREATE FUNCTION. Чтобы удалить эти функции, используйте оператор DROP FUNCTION.

Связанные материалы​