UDF: SQL, Executable и композитные агрегаты

ClickHouse предоставляет три уровня расширяемости для пользовательской логики: SQL lambda UDF для простых выражений, Executable UDF для интеграции с внешними процессами (Python, ML-модели), и composable aggregate patterns через комбинаторы -State/-Merge для кастомной агрегации без C++.

SQL UDF: CREATE FUNCTION

SQL UDF — чистый SQL, без компиляции и перезапуска сервера. Функция определяется как lambda-выражение и может использоваться в любом SELECT.

Синтаксис:

-- Базовый синтаксис SQL UDF
CREATE FUNCTION name AS (param0, param1, ...) -> expression;

Практические примеры:

-- Маскировка email: [email protected] -> us***@example.com
CREATE FUNCTION mask_email AS (email) ->
    concat(
        substring(email, 1, 2),
        '***',
        substring(email, position(email, '@'))
    );

-- Использование
SELECT
    user_id,
    mask_email(email) AS masked_email
FROM users
LIMIT 5;

-- Форматирование даты на русском языке
CREATE FUNCTION format_date_ru AS (dt) ->
    concat(
        toString(toDayOfMonth(dt)),
        ' ',
        ['января','февраля','марта','апреля','мая','июня',
         'июля','августа','сентября','октября','ноября','декабря'][toMonth(dt)],
        ' ',
        toString(toYear(dt))
    );

-- Использование
SELECT format_date_ru(event_time) AS date_ru
FROM events
LIMIT 3;

-- Вычисление BMI категории (composable примитив)
CREATE FUNCTION bmi_category AS (weight_kg, height_m) ->
    multiIf(
        weight_kg / (height_m * height_m) < 18.5, 'underweight',
        weight_kg / (height_m * height_m) < 25.0, 'normal',
        weight_kg / (height_m * height_m) < 30.0, 'overweight',
        'obese'
    );

Ограничения SQL UDF:

• Нет рекурсии (ClickHouse запрещает самовызов)
• Нет состояния между вызовами (stateless)
• Нет доступа к другим таблицам (только выражения над переданными аргументами)
• Хранятся в памяти сервера; при перезапуске требуют повторного создания (или через CREATE OR REPLACE FUNCTION)

-- Удаление функции
DROP FUNCTION mask_email;

-- Пересоздание с обновлённой логикой
CREATE OR REPLACE FUNCTION mask_email AS (email) ->
    concat(
        substring(email, 1, 3),
        '***',
        substring(email, position(email, '@'))
    );

Executable UDF: внешний процесс

Executable UDF позволяет вызывать внешний процесс (Python-скрипт, ML-модель, любой бинарный файл) из ClickHouse запроса. Данные передаются через stdin/stdout.

Конфигурация через XML (на сервере):

<!-- /etc/clickhouse-server/user_defined_functions/ml_scoring.xml -->
<functions>
    <function>
        <type>executable</type>
        <name>ml_score</name>
        <return_type>Float64</return_type>
        <argument>
            <type>Float64</type>
            <name>feature1</name>
        </argument>
        <argument>
            <type>Float64</type>
            <name>feature2</name>
        </argument>
        <format>TabSeparated</format>
        <command>python3 /opt/udf/ml_scoring.py</command>
        <execute_direct>true</execute_direct>
    </function>
</functions>

Python-скрипт (/opt/udf/ml_scoring.py):

#!/usr/bin/env python3
import sys

for line in sys.stdin:
    # Парсинг входных данных (TabSeparated)
    parts = line.strip().split('\t')
    feature1 = float(parts[0])
    feature2 = float(parts[1])

    # Простая модель (заглушка: реальная модель загружается здесь)
    score = feature1 * 0.7 + feature2 * 0.3

    # Вывод результата (один столбец, одна строка на строку ввода)
    print(score, flush=True)

Использование Executable UDF в запросе:

-- После регистрации XML-файла и перезагрузки (SYSTEM RELOAD FUNCTIONS)
SELECT
    user_id,
    feature1,
    feature2,
    ml_score(feature1, feature2) AS churn_probability
FROM user_features
WHERE ml_score(feature1, feature2) > 0.8
ORDER BY churn_probability DESC
LIMIT 100;

Use cases Executable UDF:

• ML scoring (scikit-learn, PyTorch inference)
• Вызов внешних API (geocoding, currency rates)
• Сложные регулярные выражения через внешние библиотеки
• Обработка данных в форматах без нативной поддержки в ClickHouse

Composable aggregate patterns: -State/-Merge

-State и -Merge — комбинаторы, позволяющие сохранять промежуточное состояние агрегатной функции в колонке типа AggregateFunction. Это основа для materialized views с постепенным накоплением агрегатов — эффективнее полного перерасчёта.

Паттерн: AggregatingMergeTree + materializedView

-- Целевая таблица для хранения промежуточных агрегатов
CREATE TABLE daily_stats
(
    day         Date,
    country     LowCardinality(String),
    -- Промежуточное состояние агрегата (не готовый результат)
    uniq_users  AggregateFunction(uniq, UInt32),
    total_rev   AggregateFunction(sum, Decimal(18, 2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (day, country);

-- Materialized view: пишет промежуточные состояния при каждом INSERT в events
CREATE MATERIALIZED VIEW daily_stats_mv
TO daily_stats
AS SELECT
    toDate(event_time)          AS day,
    country,
    uniqState(user_id)          AS uniq_users,   -- -State суффикс
    sumState(revenue)           AS total_rev      -- -State суффикс
FROM events
GROUP BY day, country;

-- Запрос результата: -Merge для финализации агрегата
SELECT
    day,
    country,
    uniqMerge(uniq_users)       AS unique_users,  -- -Merge финализирует
    sumMerge(total_rev)         AS total_revenue
FROM daily_stats
WHERE day >= today() - 7
GROUP BY day, country
ORDER BY day, total_revenue DESC;

Паттерн: -If комбинатор для условной агрегации

-- Подсчёт конверсий (viewed + purchased) за один проход
SELECT
    campaign_id,
    countIf(event_type = 'view')     AS views,
    countIf(event_type = 'purchase') AS purchases,
    sumIf(revenue, event_type = 'purchase') AS total_revenue,
    round(
        countIf(event_type = 'purchase') * 100.0 / countIf(event_type = 'view'),
        2
    ) AS conversion_rate
FROM campaign_events
GROUP BY campaign_id;

Подробный разбор всех доступных комбинаторов (-Array, -ForEach, -OrDefault, -OrNull и других) — в Модуле 08 урок 01.

Ключевые выводы

1. SQL UDF (CREATE FUNCTION name AS (p) -> expr) — lambda-based, stateless, без рекурсии. Идеален для инкапсуляции повторяющихся выражений: маскировка данных, форматирование, условная логика.

2. Executable UDF — интеграция с внешними процессами через stdin/stdout. Незаменим для ML scoring и сложных вычислений, которые невозможно выразить в SQL. Требует XML-конфигурации на сервере.

3. CREATE AGGREGATE FUNCTION через SQL не существует в ClickHouse. Для кастомных агрегаций используется паттерн комбинаторов: -State сохраняет промежуточный результат в AggregateFunction колонку, -Merge финализирует при чтении.

4. AggregatingMergeTree + materialized view — стандартный production паттерн для инкрементальной агрегации. Обновление без полного пересчёта: каждый INSERT пишет только -State суффикс агрегата.

5. -If комбинаторы (countIf, sumIf, avgIf) обеспечивают условную агрегацию за один проход — без дополнительных подзапросов или CASE WHEN.