Перейти к основному содержимому
Версия: 4.6.X

ClickHouse ETL: разбор `events.custom` в пары атрибут–значение

Задача​

В Komrad SIEM поле komrad_events.events.custom хранит “плоский” JSON‑словарь пользовательских атрибутов (key → value), где значение может быть строкой, числом, булевым, массивом/объектом или строкой, внутри которой лежит JSON (string‑marshalled JSON).

Для OLAP‑аналитики (агрегации, фильтры, корреляции, BI‑дашборды) часто удобнее хранить custom в нормализованном виде: одна строка на один атрибут:

(event_key, tenant_id, attr, value_raw, value/typed_value…).

Этот пакет даёт два варианта:

  1. Ad‑hoc SELECT: “взрываем” custom в пары прямо запросом.
  2. Витрина (таблица + Materialized View): складываем пары в отдельную MergeTree‑таблицу для быстрых OLAP‑запросов.

Исходные данные​

Таблица событий: komrad_events.events (движок MergeTree).

Идентификатор события в Komrad — композиция (key_time, key_roller, key_stream):

  • key_time Int64 — Unix timestamp (seconds)
  • key_roller UInt32
  • key_stream UInt32

Рекомендуется всегда переносить эти три поля (и tenant_id) в витрину с парами custom, чтобы в дальнейшем можно было:

  • восстанавливать событие (JOIN обратно к events);
  • группировать/агрегировать пары в рамках одного события;
  • строить “wide” представления и индексы под конкретные use‑case’ы.

Особый случай: Fingerprint.UserAgentData​

custom['Fingerprint.UserAgentData'] — это JSON‑строка, внутри которой лежит JSON‑объект (пример: строка вида "{\"brands\":[...],\"mobile\":false,...}").

Мы поддерживаем два режима:

  • flat: сохраняем Fingerprint.UserAgentData как один атрибут (строковое значение).
  • expanded: раскрываем внутренний объект в пары Fingerprint.UserAgentData.<inner_key>.
примечание

По умолчанию мы раскрываем только Fingerprint.UserAgentData. Это сделано намеренно: “универсальное” рекурсивное раскрытие всех строк‑JSON часто приводит к непредсказуемому росту объёма данных и стоимости записи.

1) Ad‑hoc запросы (без витрин)​

  • Плоское разложение custom в пары: sql/01_select_custom_pairs_flat.sql
  • Плоское разложение + раскрытие Fingerprint.UserAgentData: sql/02_select_custom_pairs_expand_fingerprint_useragentdata.sql

Выходные колонки:

  • attr — имя атрибута (ключ JSON)
  • value_raw — JSON‑литерал (например: "abc", 123, true, {"k":"v"}, [1,2])
  • value — нормализованная строка (JSON‑строки разэкраниваются, остальные типы остаются как есть)

2) Витрины (таблицы + MV) для OLAP​

Доступно 4 варианта хранения (flat/expanded × single/typed):

ВариантЧто даётКогда выбирать
flat + single-valueминимум CPU/диска, простая модельстартовый вариант для high‑EPS
flat + typedтипизированные колонки (string/bool/int/float)когда важны быстрые числовые агрегации/фильтры
expanded + single-valueраскрывает Fingerprint.UserAgentData.<k>когда UA‑аналитика важна и объём приемлем
expanded + typedраскрытие + типизациямаксимально удобно для OLAP, но дороже по ресурсам

DDL таблиц:

  • sql/10_ddl_kv_flat_single.sql
  • sql/11_ddl_kv_flat_typed.sql
  • sql/12_ddl_kv_expanded_single.sql
  • sql/13_ddl_kv_expanded_typed.sql

Безопасное включение MV без POPULATE (рекомендуется)​

Для SIEM‑нагрузок (особенно 10k–100k EPS) использовать POPULATE не рекомендуется: он создаёт пиковую нагрузку и может негативно повлиять на ingestion/latency.

Безопасная схема включения:

1) Зафиксируйте "границу" включения:

SELECT now() AS mv_start;

2) Создайте целевую таблицу (один из sql/10..13_ddl_*.sql).

3) Создайте Materialized View с отсечкой только для новых данных:

  • sql/20_mv_kv_flat_single_safe.sql
  • sql/21_mv_kv_flat_typed_safe.sql
  • sql/22_mv_kv_expanded_single_safe.sql
  • sql/23_mv_kv_expanded_typed_safe.sql

В каждом файле нужно заменить дефолт:

WITH toDateTime('2099-01-01 00:00:00') AS mv_start

на фактическое значение mv_start, полученное на шаге 1.

4) Заполните "исторические" данные backfill'ом для w_time < mv_start.

Backfill скрипт (партиционно, с возобновлением, throttling)​

Скрипт: scripts/backfill_custom_kv.sh

Ключевые свойства:

  • идёт по партициям исходной таблицы (system.parts);
  • пишет прогресс в state‑файл (можно перезапускать);
  • умеет ограничивать диапазон партиций (--from-partition/--to-partition);
  • регулируется по нагрузке (--max-threads, --max-insert-threads, --sleep).

Пример:

./scripts/backfill_custom_kv.sh \
  --database komrad_events \
  --source-table events \
  --mode flat_single \
  --cutoff-wtime '2025-12-25 12:34:56' \
  --max-threads 4 \
  --max-insert-threads 2 \
  --sleep 1
предупреждение

Важно, чтобы "окно" MV и backfill не пересекались:

  • MV: w_time >= mv_start
  • backfill: w_time < mv_start

Если поменять условия или использовать POPULATE одновременно с backfill, возможны дубли.

Диагностика и оценка стоимости​

Перед включением витрин полезно оценить размер/ширину custom:

  • sql/00_profile_custom.sql

Практическая оценка объёма витрины:

  • количество строк ≈ events × avg(keys(custom))
  • expanded может добавлять строки для Fingerprint.UserAgentData.<k>
  • typed добавляет несколько колонок, но может сильно ускорить OLAP‑фильтры/агрегации

Точки расширения​

Если потребуется:

  • раскрывать дополнительные ключи со строкой‑JSON (по whitelist);
  • нормализовать ключи (attr) или ограничивать набор атрибутов;
  • сделать отдельные витрины под конкретные домены (Auth/Network/EDR) и агрегаты;

лучше делать это осознанно (по требованиям запросов и бюджету ресурсов), так как стоимость записи и объём данных при SIEM‑нагрузках растут очень быстро.

Последнее обновление