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

ClickHouse поддерживает несколько типов пользовательских функций (UDF):

• исполняемые UDF запускают внешнюю программу или скрипт (Python, Bash и т. д.) и потоково передают им блоки данных через STDIN / STDOUT. Используйте их для интеграции существующего кода или инструментов без перекомпиляции ClickHouse. По сравнению с внутрипроцессными вариантами у них выше накладные расходы на каждый вызов, поэтому они лучше подходят для более сложной логики или случаев, когда нужна другая среда выполнения.
• пользовательские функции SQL определяются с помощью CREATE FUNCTION исключительно на SQL. Они подставляются/разворачиваются в план запроса (без отдельного процесса), что делает их лёгкими и идеально подходящими для повторного использования логики выражений или упрощения сложных вычисляемых столбцов.
• Экспериментальные пользовательские функции WebAssembly выполняют код, скомпилированный в WebAssembly, внутри песочницы в процессе сервера. Они обеспечивают меньшие накладные расходы на каждый вызов, чем внешние исполняемые файлы, и лучшую изоляцию, чем нативные расширения, что делает их подходящими для пользовательских алгоритмов, написанных на языках, которые можно компилировать в WASM (например, C/C++/Rust).
• Экспериментальные исполняемые UDF на базе драйверов позволяют предоставляемому оператором "драйверу" преобразовывать фрагмент кода, указанный в CREATE FUNCTION ... ENGINE = DriverName(...) AS '...', в исполняемый UDF при создании функции (например, путём компиляции). Они основаны на исполняемых UDF и требуют серверной конфигурации драйвера.

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

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

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

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

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

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

functions:
  type: executable
  name: test_function_sum
  return_type: UInt64
  argument:
    - type: UInt64
      name: lhs
    - type: UInt64
      name: rhs
  format: TabSeparated
  command: 'cd /; clickhouse-local --input-format TabSeparated --output-format TabSeparated --structure ''x UInt64, y UInt64'' --query "SELECT x + y FROM table"'
  execute_direct: 0
  deterministic: true

SELECT test_function_sum(2, 2);

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

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

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

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

functions:
  type: executable
  name: test_function_python
  return_type: String
  argument:
    - type: UInt64
      name: value
  format: TabSeparated
  command: test_function.py

Создайте файл скрипта 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)─┐
│ Value 2                 │
└─────────────────────────┘

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

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

functions:
  type: executable
  name: test_function_sum_json
  return_type: UInt64
  return_name: result_name
  argument:
    - type: UInt64
      name: argument_1
    - type: UInt64
      name: argument_2
  format: JSONEachRow
  command: test_function_sum_json.py

Создайте файл скрипта 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 │
└──────────────────────────────┘

Пользовательские функции типа executable могут принимать константные параметры, заданные в настройке command (это работает только для пользовательских функций типа executable). Также требуется параметр execute_direct, чтобы исключить уязвимость, связанную с подстановкой аргументов командной оболочкой.

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

functions:
  type: executable
  execute_direct: true
  name: test_function_parameter_python
  return_type: String
  argument:
    - type: UInt64
  format: TabSeparated
  command: test_function_parameter_python.py {test_parameter:UInt64}

Создайте файл скрипта 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)─┐
│ Parameter 1 value 2                  │
└──────────────────────────────────────┘

В этом примере мы создаём shell-скрипт, который умножает каждое значение на 2.

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

functions:
  type: executable
  name: test_shell
  return_type: String
  argument:
    - type: UInt8
      name: value
  format: TabSeparated
  command: test_shell.sh

Создайте файл скрипта 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.

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

Пользовательские функции WebAssembly (WASM UDF) позволяют выполнять пользовательский код, скомпилированный в WebAssembly, внутри процесса сервера ClickHouse.

Включите экспериментальную поддержку WebAssembly в конфигурации ClickHouse:

<clickhouse>
    <allow_experimental_webassembly_udf>true</allow_experimental_webassembly_udf>
</clickhouse>

Вставьте скомпилированный модуль WASM в системную таблицу:

INSERT INTO system.webassembly_modules (name, code)
SELECT 'my_module', base64Decode('AGFzbQEAAAA...');

Создайте функцию с помощью модуля WASM:

CREATE FUNCTION my_function
LANGUAGE WASM
ABI ROW_DIRECT
FROM 'my_module'
ARGUMENTS (x UInt32, y UInt32)
RETURNS UInt32;

Используйте FUNCTION в своих запросах:

SELECT my_function(10, 20);

Подробнее см. в документации Пользовательские функции WebAssembly.

Драйвер — это предоставляемый оператором адаптер, который превращает фрагмент пользовательского кода в исполняемый UDF. Когда функция создаётся с ENGINE = DriverName(...), ClickHouse запускает create_command драйвера, передавая ему сигнатуру функции и тело кода; драйвер компилирует тело или иным образом его обрабатывает и выводит конфигурацию исполняемого UDF, которую ClickHouse затем сохраняет и загружает.

Это позволяет администраторам дать пользователям безопасный и строго ограниченный способ определять функции на произвольном языке (например, на C, компилируемом внутри изолированного контейнера), не предоставляя им доступ к конфигурационным файлам или файловой системе сервера. Набор доступных драйверов полностью контролируется оператором.

Исполняемые UDF на базе драйверов по умолчанию отключены. Чтобы их включить:

1. Установите экспериментальный флаг в конфигурации сервера:

   <clickhouse>
       <allow_experimental_executable_udf_drivers>true</allow_experimental_executable_udf_drivers>
   </clickhouse>

2. Укажите в user_defined_executable_function_drivers_config один или несколько файлов конфигурации драйверов (поддерживаются glob-шаблоны) и при необходимости задайте dynamic_user_defined_executable_functions_path — каталог, где хранятся сгенерированные конфигурации исполняемых UDF:

   <clickhouse>
       <user_defined_executable_function_drivers_config>user_defined_executable_function_drivers_config.d/*_driver.xml</user_defined_executable_function_drivers_config>
       <dynamic_user_defined_executable_functions_path>/var/lib/clickhouse/dynamic_user_defined_executable_functions/</dynamic_user_defined_executable_functions_path>
   </clickhouse>

Реестр драйверов загружается при запуске сервера и обновляется командой SYSTEM RELOAD CONFIG, поэтому драйверы можно добавлять, изменять или удалять без перезапуска сервера.

Драйвер описывается XML- или YAML-файлом с корневым элементом <driver>. Поддерживаются следующие поля:

Пример конфигурации драйвера:

<clickhouse>
    <driver>
        <name>DockerC</name>
        <create_command>../user_defined_executable_function_drivers/docker_c_create.sh</create_command>
        <drop_command>../user_defined_executable_function_drivers/docker_c_drop.sh</drop_command>
        <engine_arguments>
            <opt_level><required>false</required></opt_level>
        </engine_arguments>
        <env>
            <CLICKHOUSE_C_DRIVER_MEMORY>256m</CLICKHOUSE_C_DRIVER_MEMORY>
            <CLICKHOUSE_C_DRIVER_CPUS>1.0</CLICKHOUSE_C_DRIVER_CPUS>
        </env>
    </driver>
</clickhouse>

При выполнении CREATE FUNCTION вызывается create_command с заданными переменными env и следующими аргументами:

• --name <function_name>
• --return <return_type> (если указана секция RETURNS)
• --args <signature> (если указана секция ARGUMENTS), где signature — это объявленный список аргументов, например x UInt8, y DateTime
• --<key> <value> для каждого объявленного аргумента движка, указанного в ENGINE = DriverName(key = value)

Тело пользовательского кода (текст после AS) передаётся на стандартный ввод команды. Команда должна вывести конфигурацию исполняемой UDF в стандартный вывод. Формат определяется автоматически: вывод, начинающийся с <, обрабатывается как XML, иначе — как YAML. Имя функции, заданное в сгенерированной конфигурации, должно совпадать с именем создаваемой функции. Если create_command завершается с ненулевым кодом выхода, оператор завершается ошибкой с исключением, включающим код выхода и содержимое стандартного потока ошибок драйвера.

drop_command, если он указан, вызывается таким же образом (без тела кода в stdin) при удалении функции.

CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS] name [ON CLUSTER cluster]
    ARGUMENTS (a UInt8, b String) RETURNS UInt64
    ENGINE = DriverName(key1 = 'value1', key2 = 42)
    AS '...code body...'

ClickHouse выполняет create_command драйвера, записывает сгенерированную конфигурацию в dynamic_user_defined_executable_functions_path, после чего её подхватывает существующий загрузчик исполняемых UDF. После этого функцию можно вызывать как и любую другую функцию.

DROP FUNCTION [IF EXISTS] name [ON CLUSTER cluster]

DROP FUNCTION вызывает drop_command драйвера (если она задана), удаляет сгенерированную динамическую конфигурацию и отдельный рабочий каталог функции, перезагружает загрузчик исполняемых UDF и удаляет сохранённый запрос.

Исходный запрос сохраняется в виде оператора ATTACH FUNCTION ... в каталоге пользовательских SQL-объектов, поэтому функция сохраняется после перезапуска сервера. При запуске сгенерированные конфигурации из dynamic_user_defined_executable_functions_path загружаются напрямую, без повторного запуска драйвера. Если для сохранённого ATTACH FUNCTION нет соответствующей сгенерированной конфигурации (например, если динамический каталог был утерян), драйвер запускается повторно, чтобы создать её заново.

• Эта возможность экспериментальная и доступна только при включении allow_experimental_executable_udf_drivers.
• Исполняемые UDF на базе драйверов не поддерживаются с реплицируемым хранилищем пользовательских функций (ON CLUSTER и <user_defined_zookeeper_path>), поскольку реплицируется только исходный запрос, а не сгенерированные артефакты.
• RESTORE исполняемого UDF на базе драйверов из резервной копии сохраняет запрос, но не запускает драйвер повторно; сгенерированная конфигурация материализуется позже в процессе восстановления при перезапуске.

В дереве исходного кода есть демонстрационные драйверы в programs/server/user_defined_executable_function_drivers_config.d/, которые компилируют и выполняют тело функции на C. Это лишь примеры, и пакеты их не устанавливают:

• DockerC - компилирует и выполняет код внутри изолированных контейнеров Docker (--network=none --read-only --cap-drop=ALL --security-opt=no-new-privileges, а также с ограничениями по памяти/CPU/PID), создавая UDF executable_pool.
• GVisorC - вариант, запускающий скомпилированный бинарный файл в среде выполнения runsc от gVisor.
• UnsafeC - компилирует и выполняет код напрямую на хосте, без песочницы. Как следует из названия, он не обеспечивает никакой изоляции и предназначен только для доверенных окружений и тестирования.

Эти драйверы-примеры служат отправной точкой; прежде чем открывать к ним доступ недоверенным пользователям, проверьте и при необходимости усильте изоляцию для вашей среды.

• Пользовательские функции в ClickHouse Cloud