Проектирование схемы для обсервабилити

Мы рекомендуем пользователям всегда создавать собственную схему для журналов и трассировок по следующим причинам:

Выбор первичного ключа - В схемах по умолчанию используется ORDER BY, оптимизированный под определённые паттерны доступа. Маловероятно, что ваши паттерны доступа будут им соответствовать.
Извлечение структуры - Возможно, вы захотите извлечь новые столбцы из существующих, например из столбца Body. Это можно сделать с помощью материализованных столбцов (а в более сложных случаях — с помощью materialized view). Для этого требуется изменить схему.
Оптимизация Maps - В схемах по умолчанию для хранения атрибутов используется тип Map. Эти столбцы позволяют хранить произвольные метаданные. Хотя это важная возможность, поскольку метаданные событий часто заранее не определены и потому иначе не могут быть сохранены в строго типизированной базе данных, такой как ClickHouse, доступ к ключам Map и их значениям менее эффективен, чем доступ к обычному столбцу. Мы решаем это, изменяя схему и вынося наиболее часто используемые ключи Map в столбцы верхнего уровня — см. “Извлечение структуры с помощью SQL”. Для этого требуется изменить схему.
Упрощение доступа к ключам Map - Доступ к ключам в Map требует более многословного синтаксиса. Это можно упростить с помощью псевдонимов. См. “Использование псевдонимов”, чтобы упростить запросы.
Вторичные индексы - В схеме по умолчанию используются вторичные индексы для ускорения доступа к Maps и текстовых запросов. Обычно они не требуются и занимают дополнительное место на диске. Их можно использовать, но следует проверить, действительно ли они нужны. См. “Вторичные / индексы пропуска данных”.
Использование кодеков - Возможно, вы захотите настроить кодеки для столбцов, если понимаете характер ожидаемых данных и у вас есть подтверждение, что это улучшает сжатие.

Ниже мы подробно описываем каждый из перечисленных выше сценариев использования. Важно: Хотя пользователям рекомендуется расширять и изменять свою схему для достижения оптимального сжатия и производительности запросов, им следует по возможности придерживаться именования схемы OTel для основных столбцов. Плагин ClickHouse Grafana предполагает наличие некоторых базовых столбцов OTel для упрощения построения запросов, например Timestamp и SeverityText. Требуемые столбцы для журналов и трассировок задокументированы здесь [1][2] и здесь соответственно. При желании вы можете изменить имена этих столбцов, переопределив значения по умолчанию в конфигурации плагина.

ClickStack поставляется с оптимизированной схемой по умолчаниюClickStack предоставляет готовые схемы для журналов, трассировок и метрик, в которых используются новейшие возможности ClickHouse (текстовые индексы для полнотекстового поиска и поиска по ключам Map, материализованные столбцы и массивы ALIAS для фильтрации с прямым чтением, поиск строк по номеру блока) и которые по результатам бенчмарков обеспечивают высокую производительность «из коробки» для рабочих нагрузок журналирования и трассировки. Используйте их как ориентир при разработке собственной схемы.

Канонический DDL: Таблицы и схемы, используемые ClickStack.
Рецепты оптимизации: Настройка производительности ClickStack. Многие рекомендации на этой странице (материализованные столбцы, индекс пропуска данных, выбор первичного ключа, проекции, materialized view) напрямую применимы и к самостоятельной настройке.

Извлечение структуры с помощью SQL

При приёме структурированных и неструктурированных журналов пользователям часто требуется возможность:

Извлекать столбцы из строковых блобов. Запросы к ним будут выполняться быстрее, чем использование строковых операций во время выполнения запроса.
Извлекать ключи из Map. Схема по умолчанию помещает произвольные атрибуты в столбцы типа Map. Этот тип позволяет работать без схемы, и поэтому пользователям не нужно заранее определять столбцы для атрибутов при описании журналов и трассировок — а это часто невозможно при сборе журналов из Kubernetes, если нужно сохранить labels подов для последующего поиска. Доступ к ключам Map и их значениям медленнее, чем выполнение запросов по обычным столбцам ClickHouse. Поэтому часто имеет смысл извлекать ключи из Map в столбцы корневой таблицы.

Рассмотрим следующие запросы: Предположим, мы хотим посчитать, какие URL-пути получают больше всего POST-запросов, используя структурированные журналы. JSON-блоб хранится в столбце Body как String. Кроме того, он также может храниться в столбце LogAttributes как Map(String, String), если пользователь включил json_parser в коллекторе.

SELECT LogAttributes
FROM otel_logs
LIMIT 1
FORMAT Vertical

Row 1:
──────
Body:           {"remote_addr":"54.36.149.41","remote_user":"-","run_time":"0","time_local":"2019-01-22 00:26:14.000","request_type":"GET","request_path":"\/filter\/27|13 ,27|  5 ,p53","request_protocol":"HTTP\/1.1","status":"200","size":"30577","referer":"-","user_agent":"Mozilla\/5.0 (compatible; AhrefsBot\/6.1; +http:\/\/ahrefs.com\/robot\/)"}
LogAttributes: {'status':'200','log.file.name':'access-structured.log','request_protocol':'HTTP/1.1','run_time':'0','time_local':'2019-01-22 00:26:14.000','size':'30577','user_agent':'Mozilla/5.0 (compatible; AhrefsBot/6.1; +http://ahrefs.com/robot/)','referer':'-','remote_user':'-','request_type':'GET','request_path':'/filter/27|13 ,27|  5 ,p53','remote_addr':'54.36.149.41'}

Предполагая, что LogAttributes доступен, вот запрос, который позволяет подсчитать, какие URL-пути сайта получают больше всего POST-запросов:

SELECT path(LogAttributes['request_path']) AS path, count() AS c
FROM otel_logs
WHERE ((LogAttributes['request_type']) = 'POST')
GROUP BY path
ORDER BY c DESC
LIMIT 5

┌─path─────────────────────┬─────c─┐
│ /m/updateVariation       │ 12182 │
│ /site/productCard        │ 11080 │
│ /site/productPrice       │ 10876 │
│ /site/productModelImages │ 10866 │
│ /site/productAdditives   │ 10866 │
└──────────────────────────┴───────┘

5 строк в наборе. Затрачено: 0.735 сек. Обработано 10.36 миллиона строк, 4.65 ГБ (14.10 миллиона строк/с., 6.32 ГБ/с.)
Пиковое использование памяти: 153.71 МиБ.

Обратите внимание на использование здесь синтаксиса Map, например LogAttributes['request_path'], и функции path для удаления параметров запроса из URL. Если пользователь не включил парсинг JSON в коллекторе, LogAttributes будет пустым, поэтому нам придется использовать JSON-функции, чтобы извлечь столбцы из String Body.

Предпочитайте парсинг в ClickHouseКак правило, мы рекомендуем пользователям выполнять парсинг JSON структурированных журналов в ClickHouse. Мы уверены, что ClickHouse — самая быстрая реализация парсинга JSON. Однако мы понимаем, что вы можете захотеть отправлять журналы в другие источники и не выносить эту логику в SQL.

SELECT path(JSONExtractString(Body, 'request_path')) AS path, count() AS c
FROM otel_logs
WHERE JSONExtractString(Body, 'request_type') = 'POST'
GROUP BY path
ORDER BY c DESC
LIMIT 5

┌─path─────────────────────┬─────c─┐
│ /m/updateVariation       │ 12182 │
│ /site/productCard        │ 11080 │
│ /site/productPrice       │ 10876 │
│ /site/productAdditives   │ 10866 │
│ /site/productModelImages │ 10866 │
└──────────────────────────┴───────┘

5 rows in set. Elapsed: 0.668 sec. Processed 10.37 million rows, 5.13 GB (15.52 million rows/s., 7.68 GB/s.)
Peak memory usage: 172.30 MiB.

Теперь рассмотрим то же для неструктурированных журналов:

SELECT Body, LogAttributes
FROM otel_logs
LIMIT 1
FORMAT Vertical

Row 1:
──────
Body:           151.233.185.144 - - [22/Jan/2019:19:08:54 +0330] "GET /image/105/brand HTTP/1.1" 200 2653 "https://www.zanbil.ir/filter/b43,p56" "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36" "-"
LogAttributes: {'log.file.name':'access-unstructured.log'}

В случае неструктурированных журналов для аналогичного запроса требуется использовать регулярные выражения с помощью функции extractAllGroupsVertical.

SELECT
        path((groups[1])[2]) AS path,
        count() AS c
FROM
(
        SELECT extractAllGroupsVertical(Body, '(\\w+)\\s([^\\s]+)\\sHTTP/\\d\\.\\d') AS groups
        FROM otel_logs
        WHERE ((groups[1])[1]) = 'POST'
)
GROUP BY path
ORDER BY c DESC
LIMIT 5

┌─path─────────────────────┬─────c─┐
│ /m/updateVariation       │ 12182 │
│ /site/productCard        │ 11080 │
│ /site/productPrice       │ 10876 │
│ /site/productModelImages │ 10866 │
│ /site/productAdditives   │ 10866 │
└──────────────────────────┴───────┘

5 rows in set. Elapsed: 1.953 sec. Processed 10.37 million rows, 3.59 GB (5.31 million rows/s., 1.84 GB/s.)

Повышенная сложность и стоимость запросов для разбора неструктурированных журналов (обратите внимание на разницу в производительности) — вот почему мы рекомендуем по возможности всегда использовать структурированные журналы.

Рассмотрите DictionariesПриведённый выше запрос можно оптимизировать, используя словари регулярных выражений. Подробнее см. в разделе Using Dictionaries.

Оба этих сценария можно реализовать в ClickHouse, перенеся приведённую выше логику запроса на этап вставки. Ниже мы рассмотрим несколько подходов и покажем, в каких случаях каждый из них уместен.

OTel или ClickHouse для обработки?Вы также можете выполнять обработку с помощью процессоров и операторов OTel коллектор, как описано здесь. В большинстве случаев ClickHouse будет значительно эффективнее по ресурсам и быстрее, чем процессоры коллектор. Основной недостаток выполнения всей обработки событий в SQL — привязка вашего решения к ClickHouse. Например, вы можете захотеть отправлять обработанные журналы из OTel коллектор в другие пункты назначения, например в S3.

Материализованные столбцы

Материализованные столбцы — это самый простой способ извлекать структуру из других столбцов. Значения таких столбцов всегда вычисляются во время вставки и не могут быть указаны в запросах INSERT.

Накладные расходыМатериализованные столбцы требуют дополнительного места в хранилище, поскольку при вставке их значения извлекаются в новые столбцы на диске.

Материализованные столбцы поддерживают любые выражения ClickHouse и позволяют использовать любые аналитические функции для обработки строк (включая регулярные выражения и поиск) и URL, выполнять преобразования типов, извлекать значения из JSON и математические операции. Мы рекомендуем материализованные столбцы для базовой обработки. Они особенно полезны для извлечения значений из Map, выноса их в столбцы верхнего уровня и преобразования типов. Чаще всего они наиболее полезны в очень простых схемах или в сочетании с materialized view. Рассмотрим следующую схему для журналов, в которой JSON был извлечён коллектором в столбец LogAttributes:

CREATE TABLE otel_logs
(
        `Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
        `TraceId` String CODEC(ZSTD(1)),
        `SpanId` String CODEC(ZSTD(1)),
        `TraceFlags` UInt32 CODEC(ZSTD(1)),
        `SeverityText` LowCardinality(String) CODEC(ZSTD(1)),
        `SeverityNumber` Int32 CODEC(ZSTD(1)),
        `ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
        `Body` String CODEC(ZSTD(1)),
        `ResourceSchemaUrl` String CODEC(ZSTD(1)),
        `ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
        `ScopeSchemaUrl` String CODEC(ZSTD(1)),
        `ScopeName` String CODEC(ZSTD(1)),
        `ScopeVersion` String CODEC(ZSTD(1)),
        `ScopeAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
        `LogAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
        `RequestPage` String MATERIALIZED path(LogAttributes['request_path']),
        `RequestType` LowCardinality(String) MATERIALIZED LogAttributes['request_type'],
        `RefererDomain` String MATERIALIZED domain(LogAttributes['referer'])
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SeverityText, toUnixTimestamp(Timestamp), TraceId)

Эквивалентную схему для извлечения данных из Body типа String с помощью JSON-функций можно найти здесь. Наши три материализованных столбца извлекают страницу запроса, тип запроса и домен реферера. Они обращаются к ключам в map и применяют функции к их значениям. В результате следующий запрос выполняется значительно быстрее:

SELECT RequestPage AS path, count() AS c
FROM otel_logs
WHERE RequestType = 'POST'
GROUP BY path
ORDER BY c DESC
LIMIT 5

┌─path─────────────────────┬─────c─┐
│ /m/updateVariation       │ 12182 │
│ /site/productCard        │ 11080 │
│ /site/productPrice       │ 10876 │
│ /site/productAdditives   │ 10866 │
│ /site/productModelImages │ 10866 │
└──────────────────────────┴───────┘

5 строк в наборе. Прошло: 0.173 сек. Обработано 10.37 миллиона строк, 418.03 МБ (60.07 миллиона строк/с., 2.42 ГБ/с.)
Пиковое использование памяти: 3.16 МиБ.

Материализованные столбцы по умолчанию не включаются в результат SELECT *. Это сделано для сохранения инварианта: результат SELECT * всегда можно вставить обратно в таблицу с помощью INSERT. Это поведение можно отключить, установив asterisk_include_materialized_columns=1; его также можно включить в Grafana (см. Additional Settings -> Custom Settings в конфигурации источника данных).

Materialized views

Materialized views дают более широкие возможности для применения SQL-фильтрации и преобразований к журналам и трассировкам. Materialized Views позволяют перенести вычислительные затраты с этапа выполнения запроса на этап вставки. Materialized view в ClickHouse — это просто триггер, который запускает запрос на блоках данных по мере их вставки в таблицу. Результаты этого запроса вставляются во вторую, «целевую», таблицу.

Обновления в реальном времениMaterialized views в ClickHouse обновляются в реальном времени по мере поступления данных в таблицу, на которой они основаны, и работают скорее как постоянно обновляемые индексы. В отличие от этого, в других базах данных materialized views обычно представляют собой статические снимки запроса, которые нужно обновлять (аналогично ClickHouse Refreshable Materialized Views).

Теоретически запрос, связанный с materialized view, может быть любым, включая агрегацию, хотя для JOIN существуют ограничения. Для задач преобразования и фильтрации, необходимых для журналов и трассировок, можно считать, что возможен любой оператор SELECT. Следует помнить, что запрос — это всего лишь триггер, который выполняется над строками, вставляемыми в таблицу (исходную таблицу), а результаты отправляются в новую таблицу (целевую таблицу). Чтобы не сохранять данные дважды (в исходной и целевой таблицах), мы можем изменить движок исходной таблицы на Null table engine, сохранив исходную схему. Наши OTel коллекторы продолжат отправлять данные в эту таблицу. Например, для журналов таблица otel_logs будет выглядеть так:

CREATE TABLE otel_logs
(
        `Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
        `TraceId` String CODEC(ZSTD(1)),
        `SpanId` String CODEC(ZSTD(1)),
        `TraceFlags` UInt32 CODEC(ZSTD(1)),
        `SeverityText` LowCardinality(String) CODEC(ZSTD(1)),
        `SeverityNumber` Int32 CODEC(ZSTD(1)),
        `ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
        `Body` String CODEC(ZSTD(1)),
        `ResourceSchemaUrl` String CODEC(ZSTD(1)),
        `ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
        `ScopeSchemaUrl` String CODEC(ZSTD(1)),
        `ScopeName` String CODEC(ZSTD(1)),
        `ScopeVersion` String CODEC(ZSTD(1)),
        `ScopeAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
        `LogAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1))
) ENGINE = Null

Движок таблицы Null — это мощная оптимизация: считайте, что это /dev/null. Эта таблица не хранит данные, но все подключённые materialized view по-прежнему будут выполняться для вставляемых строк, прежде чем те будут отброшены. Рассмотрим следующий запрос. Он преобразует наши строки в формат, который мы хотим сохранить, извлекая все столбцы из LogAttributes (предполагается, что это поле задаётся коллектором с помощью оператора json_parser), устанавливая SeverityText и SeverityNumber (на основе нескольких простых условий и определения этих столбцов). В этом случае мы также выбираем только те столбцы, которые, как мы знаем, будут заполнены, игнорируя такие столбцы, как TraceId, SpanId и TraceFlags.

SELECT
        Body, 
        Timestamp::DateTime AS Timestamp,
        ServiceName,
        LogAttributes['status'] AS Status,
        LogAttributes['request_protocol'] AS RequestProtocol,
        LogAttributes['run_time'] AS RunTime,
        LogAttributes['size'] AS Size,
        LogAttributes['user_agent'] AS UserAgent,
        LogAttributes['referer'] AS Referer,
        LogAttributes['remote_user'] AS RemoteUser,
        LogAttributes['request_type'] AS RequestType,
        LogAttributes['request_path'] AS RequestPath,
        LogAttributes['remote_addr'] AS RemoteAddr,
        domain(LogAttributes['referer']) AS RefererDomain,
        path(LogAttributes['request_path']) AS RequestPage,
        multiIf(Status::UInt64 > 500, 'CRITICAL', Status::UInt64 > 400, 'ERROR', Status::UInt64 > 300, 'WARNING', 'INFO') AS SeverityText,
        multiIf(Status::UInt64 > 500, 20, Status::UInt64 > 400, 17, Status::UInt64 > 300, 13, 9) AS SeverityNumber
FROM otel_logs
LIMIT 1
FORMAT Vertical

Row 1:
──────
Body:           {"remote_addr":"54.36.149.41","remote_user":"-","run_time":"0","time_local":"2019-01-22 00:26:14.000","request_type":"GET","request_path":"\/filter\/27|13 ,27|  5 ,p53","request_protocol":"HTTP\/1.1","status":"200","size":"30577","referer":"-","user_agent":"Mozilla\/5.0 (compatible; AhrefsBot\/6.1; +http:\/\/ahrefs.com\/robot\/)"}
Timestamp:      2019-01-22 00:26:14
ServiceName:
Status:         200
RequestProtocol: HTTP/1.1
RunTime:        0
Size:           30577
UserAgent:      Mozilla/5.0 (compatible; AhrefsBot/6.1; +http://ahrefs.com/robot/)
Referer:        -
RemoteUser:     -
RequestType:    GET
RequestPath:    /filter/27|13 ,27|  5 ,p53
RemoteAddr:     54.36.149.41
RefererDomain:
RequestPage:    /filter/27|13 ,27|  5 ,p53
SeverityText:   INFO
SeverityNumber:  9

1 row in set. Elapsed: 0.027 sec.

Мы также извлекаем столбец Body — на случай, если позднее будут добавлены дополнительные атрибуты, не охваченные нашим SQL. Этот столбец хорошо сжимается в ClickHouse и будет запрашиваться редко, поэтому не влияет на производительность запросов. Наконец, мы приводим Timestamp к типу DateTime (для экономии места — см. «Оптимизация типов») с помощью приведения типа.

Условные выраженияОбратите внимание на использование условных выражений выше для извлечения SeverityText и SeverityNumber. Они чрезвычайно полезны для построения сложных условий и проверки того, заданы ли значения в maps — здесь мы наивно предполагаем, что все ключи существуют в LogAttributes. Рекомендуем хорошо с ними ознакомиться: при парсинге логов они очень выручают, наряду с функциями для обработки NULL-значений!

Нам потребуется таблица для хранения этих результатов. Приведённая ниже целевая таблица соответствует указанному выше запросу:

CREATE TABLE otel_logs_v2
(
        `Body` String,
        `Timestamp` DateTime,
        `ServiceName` LowCardinality(String),
        `Status` UInt16,
        `RequestProtocol` LowCardinality(String),
        `RunTime` UInt32,
        `Size` UInt32,
        `UserAgent` String,
        `Referer` String,
        `RemoteUser` String,
        `RequestType` LowCardinality(String),
        `RequestPath` String,
        `RemoteAddress` IPv4,
        `RefererDomain` String,
        `RequestPage` String,
        `SeverityText` LowCardinality(String),
        `SeverityNumber` UInt8
)
ENGINE = MergeTree
ORDER BY (ServiceName, Timestamp)

Выбранные здесь типы основаны на оптимизациях, описанных в разделе «Оптимизация типов».

Обратите внимание, насколько сильно мы изменили схему. На практике у вас, скорее всего, будут и столбцы Trace, которые тоже стоит сохранить, а также столбец ResourceAttributes (обычно в нём содержатся метаданные Kubernetes). Grafana может использовать столбцы Trace, чтобы связывать журналы и трейсы — см. “Использование Grafana”.

Ниже мы создаём materialized view otel_logs_mv, которое выполняет приведённый выше запрос SELECT для таблицы otel_logs и отправляет результаты в otel_logs_v2.

CREATE MATERIALIZED VIEW otel_logs_mv TO otel_logs_v2 AS
SELECT
        Body, 
        Timestamp::DateTime AS Timestamp,
        ServiceName,
        LogAttributes['status']::UInt16 AS Status,
        LogAttributes['request_protocol'] AS RequestProtocol,
        LogAttributes['run_time'] AS RunTime,
        LogAttributes['size'] AS Size,
        LogAttributes['user_agent'] AS UserAgent,
        LogAttributes['referer'] AS Referer,
        LogAttributes['remote_user'] AS RemoteUser,
        LogAttributes['request_type'] AS RequestType,
        LogAttributes['request_path'] AS RequestPath,
        LogAttributes['remote_addr'] AS RemoteAddress,
        domain(LogAttributes['referer']) AS RefererDomain,
        path(LogAttributes['request_path']) AS RequestPage,
        multiIf(Status::UInt64 > 500, 'CRITICAL', Status::UInt64 > 400, 'ERROR', Status::UInt64 > 300, 'WARNING', 'INFO') AS SeverityText,
        multiIf(Status::UInt64 > 500, 20, Status::UInt64 > 400, 17, Status::UInt64 > 300, 13, 9) AS SeverityNumber
FROM otel_logs

Это показано ниже: Если теперь перезапустить конфигурацию коллектор, использованную в “Экспорт в ClickHouse”, данные появятся в otel_logs_v2 в нужном нам формате. Обратите внимание на использование типизированных функций извлечения из JSON.

SELECT *
FROM otel_logs_v2
LIMIT 1
FORMAT Vertical

Row 1:
──────
Body:           {"remote_addr":"54.36.149.41","remote_user":"-","run_time":"0","time_local":"2019-01-22 00:26:14.000","request_type":"GET","request_path":"\/filter\/27|13 ,27|  5 ,p53","request_protocol":"HTTP\/1.1","status":"200","size":"30577","referer":"-","user_agent":"Mozilla\/5.0 (compatible; AhrefsBot\/6.1; +http:\/\/ahrefs.com\/robot\/)"}
Timestamp:      2019-01-22 00:26:14
ServiceName:
Status:         200
RequestProtocol: HTTP/1.1
RunTime:        0
Size:           30577
UserAgent:      Mozilla/5.0 (compatible; AhrefsBot/6.1; +http://ahrefs.com/robot/)
Referer:        -
RemoteUser:     -
RequestType:    GET
RequestPath:    /filter/27|13 ,27|  5 ,p53
RemoteAddress:  54.36.149.41
RefererDomain:
RequestPage:    /filter/27|13 ,27|  5 ,p53
SeverityText:   INFO
SeverityNumber:  9

1 row in set. Elapsed: 0.010 sec.

Ниже показано эквивалентное materialized view, в котором столбцы извлекаются из столбца Body с помощью JSON-функций:

CREATE MATERIALIZED VIEW otel_logs_mv TO otel_logs_v2 AS
SELECT  Body, 
        Timestamp::DateTime AS Timestamp,
        ServiceName,
        JSONExtractUInt(Body, 'status') AS Status,
        JSONExtractString(Body, 'request_protocol') AS RequestProtocol,
        JSONExtractUInt(Body, 'run_time') AS RunTime,
        JSONExtractUInt(Body, 'size') AS Size,
        JSONExtractString(Body, 'user_agent') AS UserAgent,
        JSONExtractString(Body, 'referer') AS Referer,
        JSONExtractString(Body, 'remote_user') AS RemoteUser,
        JSONExtractString(Body, 'request_type') AS RequestType,
        JSONExtractString(Body, 'request_path') AS RequestPath,
        JSONExtractString(Body, 'remote_addr') AS remote_addr,
        domain(JSONExtractString(Body, 'referer')) AS RefererDomain,
        path(JSONExtractString(Body, 'request_path')) AS RequestPage,
        multiIf(Status::UInt64 > 500, 'CRITICAL', Status::UInt64 > 400, 'ERROR', Status::UInt64 > 300, 'WARNING', 'INFO') AS SeverityText,
        multiIf(Status::UInt64 > 500, 20, Status::UInt64 > 400, 17, Status::UInt64 > 300, 13, 9) AS SeverityNumber
FROM otel_logs

Будьте осторожны с типами

Приведённые выше materialized view опираются на неявное приведение типов — особенно при использовании map LogAttributes. ClickHouse часто автоматически приводит извлечённое значение к типу целевой таблицы, что позволяет упростить синтаксис. Однако мы рекомендуем всегда проверять свои view: выполнять оператор SELECT из view вместе с оператором INSERT INTO для целевой таблицы с той же схемой. Это поможет убедиться, что типы обрабатываются корректно. Особое внимание стоит обратить на следующие случаи:

Если ключ отсутствует в map, будет возвращена пустая строка. Для числовых значений её нужно преобразовать в подходящее значение. Это можно сделать с помощью условных функций, например if(LogAttributes['status'] = ", 200, LogAttributes['status']), или функций приведения типов, если допустимы значения по умолчанию, например toUInt8OrDefault(LogAttributes['status'] )
Некоторые типы приводятся не всегда — например, строковые представления чисел не будут приведены к значениям enum.
Функции извлечения JSON возвращают значения по умолчанию для своего типа, если значение не найдено. Убедитесь, что эти значения действительно подходят!

Избегайте NullableИзбегайте использования Nullable в ClickHouse для данных обсервабилити. В журнале и трассировке редко требуется различать пустое значение и null. Эта возможность создаёт дополнительные накладные расходы на хранение и негативно влияет на производительность запросов. Подробнее см. здесь.

Выбор первичного ключа (ключа сортировки)

После того как вы извлекли нужные столбцы, можно переходить к оптимизации ключа сортировки/первичного ключа. При выборе ключа сортировки можно руководствоваться несколькими простыми правилами. Иногда они могут противоречить друг другу, поэтому рассматривайте их именно в таком порядке. В ходе этого процесса вы можете определить несколько вариантов ключа; обычно достаточно 4–5 столбцов:

Выбирайте столбцы, которые соответствуют вашим типичным фильтрам и сценариям доступа. Если вы обычно начинаете расследование в обсервабилити с фильтрации по конкретному столбцу, например по имени пода, этот столбец будет часто использоваться в секциях WHERE. При прочих равных отдавайте приоритет таким столбцам при включении в ключ по сравнению с теми, которые используются реже.
Предпочитайте столбцы, которые при фильтрации позволяют исключить большую долю всех строк, тем самым уменьшая объем данных, которые нужно читать. Имена сервисов и коды состояния часто хорошо подходят на эту роль — во втором случае только если вы фильтруете по значениям, исключающим большую часть строк. Например, фильтрация по кодам 200 в большинстве систем будет соответствовать большей части строк, тогда как ошибки 500 затронут лишь небольшое подмножество.
Предпочитайте столбцы, которые, вероятно, будут сильно коррелировать с другими столбцами в таблице. Это поможет сделать так, чтобы эти значения тоже хранились рядом, что улучшит сжатие.
Операции GROUP BY и ORDER BY для столбцов, входящих в ключ сортировки, можно сделать более эффективными с точки зрения памяти.

После того как подмножество столбцов для ключа сортировки определено, их нужно объявить в определенном порядке. Этот порядок может существенно влиять как на эффективность фильтрации по вторичным столбцам ключа в запросах, так и на коэффициент сжатия файлов данных таблицы. В общем случае столбцы ключа лучше располагать в порядке возрастания мощности. При этом нужно учитывать, что фильтрация по столбцам, расположенным позже в ключе сортировки, будет менее эффективной, чем по тем, которые стоят раньше в кортеже. Учитывайте этот компромисс и ваши сценарии доступа. И самое главное — тестируйте разные варианты. Чтобы лучше понять ключи сортировки и способы их оптимизации, рекомендуем эту статью.

Сначала структураМы рекомендуем определяться с ключами сортировки после того, как вы структурировали свои журналы. Не используйте ключи из map атрибутов или выражения извлечения JSON в качестве ключа сортировки. Убедитесь, что ключи сортировки представлены в вашей таблице как корневые столбцы.

Использование Map

В предыдущих примерах показано, как использовать синтаксис map['key'] для доступа к значениям в столбцах типа Map(String, String). Помимо нотации map для доступа к вложенным ключам, в ClickHouse также доступны специализированные функции для map, которые позволяют фильтровать или выбирать данные из этих столбцов. Например, следующий запрос определяет все уникальные ключи, доступные в столбце LogAttributes, с помощью функции mapKeys, а затем функции groupArrayDistinctArray (комбинатора).

SELECT groupArrayDistinctArray(mapKeys(LogAttributes))
FROM otel_logs
FORMAT Vertical

Row 1:
──────
groupArrayDistinctArray(mapKeys(LogAttributes)): ['remote_user','run_time','request_type','log.file.name','referer','request_path','status','user_agent','remote_addr','time_local','size','request_protocol']

1 row in set. Elapsed: 1.139 sec. Processed 5.63 million rows, 2.53 GB (4.94 million rows/s., 2.22 GB/s.)
Peak memory usage: 71.90 MiB.

Избегайте точекМы не рекомендуем использовать точки в именах столбцов Map и в будущем можем отказаться от их поддержки. Используйте _.

Использование псевдонимов

Запросы к типам Map выполняются медленнее, чем запросы к обычным столбцам — см. “Ускорение запросов”. Кроме того, их синтаксис сложнее, и писать такие запросы может быть неудобно. Чтобы решить эту проблему, мы рекомендуем использовать столбцы ALIAS. Столбцы ALIAS вычисляются во время выполнения запроса и не хранятся в таблице. Поэтому INSERT значения в столбец этого типа невозможен. С помощью псевдонимов можно ссылаться на ключи Map, упрощать синтаксис и прозрачно обращаться к элементам Map как к обычным столбцам. Рассмотрим следующий пример:

CREATE TABLE otel_logs
(
        `Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
        `TraceId` String CODEC(ZSTD(1)),
        `SpanId` String CODEC(ZSTD(1)),
        `TraceFlags` UInt32 CODEC(ZSTD(1)),
        `SeverityText` LowCardinality(String) CODEC(ZSTD(1)),
        `SeverityNumber` Int32 CODEC(ZSTD(1)),
        `ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
        `Body` String CODEC(ZSTD(1)),
        `ResourceSchemaUrl` String CODEC(ZSTD(1)),
        `ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
        `ScopeSchemaUrl` String CODEC(ZSTD(1)),
        `ScopeName` String CODEC(ZSTD(1)),
        `ScopeVersion` String CODEC(ZSTD(1)),
        `ScopeAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
        `LogAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
        `RequestPath` String MATERIALIZED path(LogAttributes['request_path']),
        `RequestType` LowCardinality(String) MATERIALIZED LogAttributes['request_type'],
        `RefererDomain` String MATERIALIZED domain(LogAttributes['referer']),
        `RemoteAddr` IPv4 ALIAS LogAttributes['remote_addr']
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, Timestamp)

У нас есть несколько материализованных столбцов и столбец ALIAS RemoteAddr, который обращается к map LogAttributes. Теперь мы можем запрашивать значения LogAttributes['remote_addr'] через этот столбец, тем самым упрощая запрос, то есть

SELECT RemoteAddr
FROM default.otel_logs
LIMIT 5

┌─RemoteAddr────┐
│ 54.36.149.41  │
│ 31.56.96.51   │
│ 31.56.96.51   │
│ 40.77.167.129 │
│ 91.99.72.15   │
└───────────────┘

5 rows in set. Elapsed: 0.011 sec.

Кроме того, добавить ALIAS с помощью команды ALTER TABLE очень просто. Эти столбцы сразу становятся доступными, например:

ALTER TABLE default.otel_logs
        (ADD COLUMN `Size` String ALIAS LogAttributes['size'])

SELECT Size
FROM default.otel_logs_v3
LIMIT 5

┌─Size──┐
│ 30577 │
│ 5667  │
│ 5379  │
│ 1696  │
│ 41483 │
└───────┘

5 rows in set. Elapsed: 0.014 sec.

Столбцы ALIAS по умолчанию исключаютсяПо умолчанию SELECT * не включает столбцы ALIAS. Это поведение можно отключить, задав asterisk_include_alias_columns=1.

Оптимизация типов

Для ClickHouse применимы общие рекомендации по оптимизации типов.

Использование кодеков

Помимо оптимизаций типов, при попытке оптимизировать сжатие для схем обсервабилити в ClickHouse вы можете следовать общим рекомендациям по выбору кодеков. В целом кодек ZSTD хорошо подходит для датасетов журнала и трасс. Увеличение уровня сжатия относительно значения по умолчанию, равного 1, может улучшить сжатие. Однако это следует проверять на практике, поскольку более высокие значения увеличивают нагрузку на CPU во время вставки. Обычно прирост от увеличения этого значения невелик. Кроме того, хотя временные метки выигрывают от дельта-кодирования с точки зрения сжатия, если этот столбец используется в первичном ключе/ключе сортировки, это может приводить к снижению производительности запросов. Мы рекомендуем оценить соответствующий компромисс между степенью сжатия и производительностью запросов.

Использование словарей

Словари — одна из ключевых возможностей ClickHouse: они предоставляют хранящееся в памяти представление данных в формате ключ-значение, поступающих из различных внутренних и внешних источников, оптимизированное для запросов с поиском по ключу со сверхнизкой задержкой. Это удобно в самых разных сценариях: от обогащения принимаемых данных на лету без замедления процесса ингестии до общего повышения производительности запросов, особенно при использовании JOIN. Хотя JOIN в сценариях обсервабилити требуются редко, словари всё равно могут быть полезны для обогащения — как при вставке, так и во время выполнения запроса. Ниже мы приводим примеры обоих вариантов.

Ускорение JOINПользователи, которым интересно ускорить JOIN с помощью словарей, могут найти дополнительные сведения здесь.

При вставке vs при выполнении запроса

Словари можно использовать для обогащения датасетов как при выполнении запроса, так и при вставке. У каждого из этих подходов есть свои плюсы и минусы. Кратко:

При вставке - Обычно этот подход подходит, если значение обогащения не меняется и хранится во внешнем источнике, который можно использовать для заполнения словаря. В этом случае обогащение строки при вставке позволяет избежать обращения к словарю во время выполнения запроса. Плата за это — снижение производительности вставки и дополнительные затраты на хранение, поскольку обогащённые значения будут сохраняться в виде столбцов.
При выполнении запроса - Если значения в словаре часто меняются, обращения к словарю во время выполнения запроса обычно более уместны. Это позволяет избежать обновления столбцов (и перезаписи данных) при изменении сопоставленных значений. Однако за такую гибкость приходится платить дополнительными затратами на обращение к словарю во время выполнения запроса. Обычно эти затраты заметны, если поиск по ключу требуется для многих строк, например при использовании поиска по ключу по словарю в условии фильтрации. Для обогащения результатов, то есть в SELECT, эти накладные расходы обычно несущественны.

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

Использование IP-словарей

Геообогащение журналов и трасс значениями широты и долготы по IP-адресам — типичное требование для обсервабилити. Этого можно добиться с помощью структурированного словаря ip_trie. Мы используем общедоступный набор данных DB-IP с геоданными до уровня города, предоставляемый DB-IP.com на условиях лицензии CC BY 4.0. Из README видно, что данные имеют следующую структуру:

| ip_range_start | ip_range_end | country_code | state1 | state2 | city | postcode | latitude | longitude | timezone |

С учётом этой структуры давайте для начала посмотрим на данные с помощью табличной функции url():

SELECT *
FROM url('https://raw.githubusercontent.com/sapics/ip-location-db/master/dbip-city/dbip-city-ipv4.csv.gz', 'CSV', '\n           \tip_range_start IPv4, \n       \tip_range_end IPv4, \n         \tcountry_code Nullable(String), \n     \tstate1 Nullable(String), \n           \tstate2 Nullable(String), \n           \tcity Nullable(String), \n     \tpostcode Nullable(String), \n         \tlatitude Float64, \n          \tlongitude Float64, \n         \ttimezone Nullable(String)\n   \t')
LIMIT 1
FORMAT Vertical

Row 1:
──────
ip_range_start: 1.0.0.0
ip_range_end:   1.0.0.255
country_code:   AU
state1:         Queensland
state2:         ᴺᵁᴸᴸ
city:           South Brisbane
postcode:       ᴺᵁᴸᴸ
latitude:       -27.4767
longitude:      153.017
timezone:       ᴺᵁᴸᴸ

Чтобы упростить задачу, давайте используем движок таблицы URL(), чтобы создать в ClickHouse объект таблицы с именами наших полей и проверить общее количество строк:

CREATE TABLE geoip_url(
        ip_range_start IPv4,
        ip_range_end IPv4,
        country_code Nullable(String),
        state1 Nullable(String),
        state2 Nullable(String),
        city Nullable(String),
        postcode Nullable(String),
        latitude Float64,
        longitude Float64,
        timezone Nullable(String)
) ENGINE=URL('https://raw.githubusercontent.com/sapics/ip-location-db/master/dbip-city/dbip-city-ipv4.csv.gz', 'CSV')

select count() from geoip_url;

┌─count()─┐
│ 3261621 │ -- 3,26 миллиона
└─────────┘

Поскольку наш словарь ip_trie требует, чтобы диапазоны IP-адресов были заданы в нотации CIDR, нам потребуется преобразовать ip_range_start и ip_range_end. CIDR для каждого диапазона можно легко вычислить с помощью следующего запроса:

WITH
        bitXor(ip_range_start, ip_range_end) AS xor,
        if(xor != 0, ceil(log2(xor)), 0) AS unmatched,
        32 - unmatched AS cidr_suffix,
        toIPv4(bitAnd(bitNot(pow(2, unmatched) - 1), ip_range_start)::UInt64) AS cidr_address
SELECT
        ip_range_start,
        ip_range_end,
        concat(toString(cidr_address),'/',toString(cidr_suffix)) AS cidr    
FROM
        geoip_url
LIMIT 4;

┌─ip_range_start─┬─ip_range_end─┬─cidr───────┐
│ 1.0.0.0        │ 1.0.0.255    │ 1.0.0.0/24 │
│ 1.0.1.0        │ 1.0.3.255    │ 1.0.0.0/22 │
│ 1.0.4.0        │ 1.0.7.255    │ 1.0.4.0/22 │
│ 1.0.8.0        │ 1.0.15.255   │ 1.0.8.0/21 │
└────────────────┴──────────────┴────────────┘

4 строки в наборе. Elapsed: 0.259 sec.

В приведённом выше запросе много всего происходит. Если интересно, прочитайте это отличное объяснение. В противном случае просто примите, что этот код вычисляет CIDR для диапазона IP-адресов.

Для наших целей понадобятся только диапазон IP-адресов, код страны и координаты, поэтому давайте создадим новую таблицу и вставим в неё наши Geo IP-данные:

CREATE TABLE geoip
(
        `cidr` String,
        `latitude` Float64,
        `longitude` Float64,
        `country_code` String
)
ENGINE = MergeTree
ORDER BY cidr

INSERT INTO geoip
WITH
        bitXor(ip_range_start, ip_range_end) as xor,
        if(xor != 0, ceil(log2(xor)), 0) as unmatched,
        32 - unmatched as cidr_suffix,
        toIPv4(bitAnd(bitNot(pow(2, unmatched) - 1), ip_range_start)::UInt64) as cidr_address
SELECT
        concat(toString(cidr_address),'/',toString(cidr_suffix)) as cidr,
        latitude,
        longitude,
        country_code    
FROM geoip_url

Чтобы выполнять в ClickHouse быстрые IP lookup-операции с низкой задержкой, мы будем использовать словари для хранения в памяти сопоставления ключ -> атрибуты для наших GeoIP-данных. ClickHouse предоставляет ip_trie структуру словаря, чтобы сопоставлять наши сетевые префиксы (CIDR-блоки) с координатами и кодами стран. Следующий запрос задаёт словарь с этой структурой и использует указанную выше таблицу в качестве источника.

CREATE DICTIONARY ip_trie (
   cidr String,
   latitude Float64,
   longitude Float64,
   country_code String
)
primary key cidr
source(clickhouse(table 'geoip'))
layout(ip_trie)
lifetime(3600);

Мы можем выбрать строки из словаря и убедиться, что этот набор данных доступен для поиска по ключу:

SELECT * FROM ip_trie LIMIT 3

┌─cidr───────┬─latitude─┬─longitude─┬─country_code─┐
│ 1.0.0.0/22 │  26.0998 │   119.297 │ CN           │
│ 1.0.0.0/24 │ -27.4767 │   153.017 │ AU           │
│ 1.0.4.0/22 │ -38.0267 │   145.301 │ AU           │
└────────────┴──────────┴───────────┴──────────────┘

3 rows in set. Elapsed: 4.662 sec.

Периодическое обновлениеСловари в ClickHouse периодически обновляются на основе данных базовой таблицы и параметра lifetime, указанного выше. Чтобы обновить наш словарь Geo IP и отразить в нём последние изменения в наборе данных DB-IP, достаточно повторно вставить данные из удалённой таблицы geoip_url в нашу таблицу geoip, применив преобразования.

Теперь, когда данные Geo IP загружены в наш словарь ip_trie (который для удобства тоже называется ip_trie), мы можем использовать его для геолокации по IP-адресу. Это можно сделать с помощью функции dictGet() следующим образом:

SELECT dictGet('ip_trie', ('country_code', 'latitude', 'longitude'), CAST('85.242.48.167', 'IPv4')) AS ip_details

┌─ip_details──────────────┐
│ ('PT',38.7944,-9.34284) │
└─────────────────────────┘

1 строка в наборе. Elapsed: 0.003 sec.

Обратите внимание на скорость получения данных. Это позволяет нам обогащать журналы. В данном случае мы выбираем выполнять обогащение на этапе выполнения запроса. Возвращаясь к нашему исходному набору данных журналов, мы можем использовать описанное выше, чтобы агрегировать журналы по странам. Далее предполагается, что мы используем схему, полученную из ранее созданного materialized view, в которой есть извлечённый столбец RemoteAddress.

SELECT dictGet('ip_trie', 'country_code', tuple(RemoteAddress)) AS country,
        formatReadableQuantity(count()) AS num_requests
FROM default.otel_logs_v2
WHERE country != ''
GROUP BY country
ORDER BY count() DESC
LIMIT 5

┌─country─┬─num_requests────┐
│ IR      │ 7.36 million    │
│ US      │ 1.67 million    │
│ AE      │ 526.74 thousand │
│ DE      │ 159.35 thousand │
│ FR      │ 109.82 thousand │
└─────────┴─────────────────┘

5 rows in set. Elapsed: 0.140 sec. Processed 20.73 million rows, 82.92 MB (147.79 million rows/s., 591.16 MB/s.)
Peak memory usage: 1.16 MiB.

Поскольку сопоставление IP-адреса с географическим местоположением может меняться, пользователям, скорее всего, важно знать, откуда поступил запрос в момент, когда он был сделан, а не каково текущее географическое местоположение того же адреса. По этой причине здесь, вероятно, предпочтительнее обогащение на этапе индексации. Это можно сделать с помощью материализованных столбцов, как показано ниже, или в SELECT materialized view:

CREATE TABLE otel_logs_v2
(
        `Body` String,
        `Timestamp` DateTime,
        `ServiceName` LowCardinality(String),
        `Status` UInt16,
        `RequestProtocol` LowCardinality(String),
        `RunTime` UInt32,
        `Size` UInt32,
        `UserAgent` String,
        `Referer` String,
        `RemoteUser` String,
        `RequestType` LowCardinality(String),
        `RequestPath` String,
        `RemoteAddress` IPv4,
        `RefererDomain` String,
        `RequestPage` String,
        `SeverityText` LowCardinality(String),
        `SeverityNumber` UInt8,
        `Country` String MATERIALIZED dictGet('ip_trie', 'country_code', tuple(RemoteAddress)),
        `Latitude` Float32 MATERIALIZED dictGet('ip_trie', 'latitude', tuple(RemoteAddress)),
        `Longitude` Float32 MATERIALIZED dictGet('ip_trie', 'longitude', tuple(RemoteAddress))
)
ENGINE = MergeTree
ORDER BY (ServiceName, Timestamp)

Периодическое обновлениеПользователи, скорее всего, захотят, чтобы словарь IP-обогащения периодически обновлялся по мере поступления новых данных. Это можно настроить с помощью предложения LIFETIME словаря, благодаря которому словарь будет периодически перезагружаться из базовой таблицы. О том, как обновлять базовую таблицу, см. “Refreshable Materialized views”.

Указанные выше страны и координаты позволяют визуализировать данные не только с группировкой и фильтрацией по странам. Для примера см. “Visualizing geo data”.

Использование словарей на основе регулярных выражений (разбор user-agent)

Разбор строк user-agent — классическая задача для регулярных выражений и распространённое требование для наборов данных на основе логов и трасс. ClickHouse обеспечивает эффективный разбор user-agent с помощью словарей на основе дерева регулярных выражений. Словари на основе дерева регулярных выражений в ClickHouse open-source определяются с использованием типа источника словаря YAMLRegExpTree, который задаёт путь к YAML-файлу, содержащему дерево регулярных выражений. Если вы хотите использовать собственный словарь регулярных выражений, подробные сведения о требуемой структуре приведены здесь. Ниже мы сосредоточимся на разборе user-agent с помощью uap-core и загрузим наш словарь в поддерживаемом CSV-формате. Этот подход совместим с OSS и ClickHouse Cloud.

В примерах ниже мы используем снимки актуальных на июнь 2024 года регулярных выражений uap-core для разбора user-agent. Последний файл, который периодически обновляется, можно найти здесь. Вы можете выполнить шаги, описанные здесь, чтобы загрузить данные в используемый ниже CSV-файл.

Создайте следующие таблицы движка Memory. В них хранятся регулярные выражения для разбора устройств, браузеров и операционных систем.

CREATE TABLE regexp_os
(
        id UInt64,
        parent_id UInt64,
        regexp String,
        keys   Array(String),
        values Array(String)
) ENGINE=Memory;

CREATE TABLE regexp_browser
(
        id UInt64,
        parent_id UInt64,
        regexp String,
        keys   Array(String),
        values Array(String)
) ENGINE=Memory;

CREATE TABLE regexp_device
(
        id UInt64,
        parent_id UInt64,
        regexp String,
        keys   Array(String),
        values Array(String)
) ENGINE=Memory;

Эти таблицы можно заполнить данными из следующих общедоступных CSV-файлов с помощью табличной функции url:

INSERT INTO regexp_os SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/user_agent_regex/regexp_os.csv', 'CSV', 'id UInt64, parent_id UInt64, regexp String, keys Array(String), values Array(String)')

INSERT INTO regexp_device SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/user_agent_regex/regexp_device.csv', 'CSV', 'id UInt64, parent_id UInt64, regexp String, keys Array(String), values Array(String)')

INSERT INTO regexp_browser SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/user_agent_regex/regexp_browser.csv', 'CSV', 'id UInt64, parent_id UInt64, regexp String, keys Array(String), values Array(String)')

После заполнения наших таблиц в памяти мы можем загрузить словари регулярных выражений. Обратите внимание, что значения ключей нужно указать в виде столбцов — это будут атрибуты, которые мы сможем извлекать из User-Agent.

CREATE DICTIONARY regexp_os_dict
(
        regexp String,
        os_replacement String default 'Other',
        os_v1_replacement String default '0',
        os_v2_replacement String default '0',
        os_v3_replacement String default '0',
        os_v4_replacement String default '0'
)
PRIMARY KEY regexp
SOURCE(CLICKHOUSE(TABLE 'regexp_os'))
LIFETIME(MIN 0 MAX 0)
LAYOUT(REGEXP_TREE);

CREATE DICTIONARY regexp_device_dict
(
        regexp String,
        device_replacement String default 'Other',
        brand_replacement String,
        model_replacement String
)
PRIMARY KEY(regexp)
SOURCE(CLICKHOUSE(TABLE 'regexp_device'))
LIFETIME(0)
LAYOUT(regexp_tree);

CREATE DICTIONARY regexp_browser_dict
(
        regexp String,
        family_replacement String default 'Other',
        v1_replacement String default '0',
        v2_replacement String default '0'
)
PRIMARY KEY(regexp)
SOURCE(CLICKHOUSE(TABLE 'regexp_browser'))
LIFETIME(0)
LAYOUT(regexp_tree);

После загрузки этих словарей мы можем указать пример user-agent и протестировать новые возможности извлечения данных из словаря:

WITH 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:127.0) Gecko/20100101 Firefox/127.0' AS user_agent
SELECT
        dictGet('regexp_device_dict', ('device_replacement', 'brand_replacement', 'model_replacement'), user_agent) AS device,
        dictGet('regexp_browser_dict', ('family_replacement', 'v1_replacement', 'v2_replacement'), user_agent) AS browser,
        dictGet('regexp_os_dict', ('os_replacement', 'os_v1_replacement', 'os_v2_replacement', 'os_v3_replacement'), user_agent) AS os

┌─device────────────────┬─browser───────────────┬─os─────────────────────────┐
│ ('Mac','Apple','Mac') │ ('Firefox','127','0') │ ('Mac OS X','10','15','0') │
└───────────────────────┴───────────────────────┴────────────────────────────┘

1 строка в наборе. Elapsed: 0.003 sec.

Поскольку правила, связанные с user-agent, меняются редко, а словарь нужно обновлять только при появлении новых браузеров, операционных систем и устройств, имеет смысл выполнять это извлечение при вставке. Эту задачу можно решить либо с помощью материализованного столбца, либо с помощью materialized view. Ниже мы изменим materialized view, использованное ранее:

CREATE MATERIALIZED VIEW otel_logs_mv TO otel_logs_v2
AS SELECT
        Body,
        CAST(Timestamp, 'DateTime') AS Timestamp,
        ServiceName,
        LogAttributes['status'] AS Status,
        LogAttributes['request_protocol'] AS RequestProtocol,
        LogAttributes['run_time'] AS RunTime,
        LogAttributes['size'] AS Size,
        LogAttributes['user_agent'] AS UserAgent,
        LogAttributes['referer'] AS Referer,
        LogAttributes['remote_user'] AS RemoteUser,
        LogAttributes['request_type'] AS RequestType,
        LogAttributes['request_path'] AS RequestPath,
        LogAttributes['remote_addr'] AS RemoteAddress,
        domain(LogAttributes['referer']) AS RefererDomain,
        path(LogAttributes['request_path']) AS RequestPage,
        multiIf(CAST(Status, 'UInt64') > 500, 'CRITICAL', CAST(Status, 'UInt64') > 400, 'ERROR', CAST(Status, 'UInt64') > 300, 'WARNING', 'INFO') AS SeverityText,
        multiIf(CAST(Status, 'UInt64') > 500, 20, CAST(Status, 'UInt64') > 400, 17, CAST(Status, 'UInt64') > 300, 13, 9) AS SeverityNumber,
        dictGet('regexp_device_dict', ('device_replacement', 'brand_replacement', 'model_replacement'), UserAgent) AS Device,
        dictGet('regexp_browser_dict', ('family_replacement', 'v1_replacement', 'v2_replacement'), UserAgent) AS Browser,
        dictGet('regexp_os_dict', ('os_replacement', 'os_v1_replacement', 'os_v2_replacement', 'os_v3_replacement'), UserAgent) AS Os
FROM otel_logs

Для этого нужно изменить схему целевой таблицы otel_logs_v2:

CREATE TABLE default.otel_logs_v2
(
 `Body` String,
 `Timestamp` DateTime,
 `ServiceName` LowCardinality(String),
 `Status` UInt8,
 `RequestProtocol` LowCardinality(String),
 `RunTime` UInt32,
 `Size` UInt32,
 `UserAgent` String,
 `Referer` String,
 `RemoteUser` String,
 `RequestType` LowCardinality(String),
 `RequestPath` String,
 `remote_addr` IPv4,
 `RefererDomain` String,
 `RequestPage` String,
 `SeverityText` LowCardinality(String),
 `SeverityNumber` UInt8,
 `Device` Tuple(device_replacement LowCardinality(String), brand_replacement LowCardinality(String), model_replacement LowCardinality(String)),
 `Browser` Tuple(family_replacement LowCardinality(String), v1_replacement LowCardinality(String), v2_replacement LowCardinality(String)),
 `Os` Tuple(os_replacement LowCardinality(String), os_v1_replacement LowCardinality(String), os_v2_replacement LowCardinality(String), os_v3_replacement LowCardinality(String))
)
ENGINE = MergeTree
ORDER BY (ServiceName, Timestamp, Status)

После перезапуска коллектора и приёма структурированных журналов, как описано в предыдущих шагах, мы можем выполнить запрос с использованием недавно извлечённых столбцов Device, Browser и Os.

SELECT Device, Browser, Os
FROM otel_logs_v2
LIMIT 1
FORMAT Vertical

Row 1:
──────
Device:  ('Spider','Spider','Desktop')
Browser: ('AhrefsBot','6','1')
Os:     ('Other','0','0','0')

Tuple для сложных структурОбратите внимание на использование типа Tuple в этих столбцах user-agent. Для сложных структур, где иерархия известна заранее, рекомендуется использовать Tuple. Подстолбцы обеспечивают ту же производительность, что и обычные столбцы (в отличие от ключей Map), при этом позволяя использовать неоднородные типы.

Дополнительные материалы

Чтобы увидеть больше примеров и подробнее ознакомиться со словарями, рекомендуем следующие статьи:

Ускорение запросов

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

Использование Materialized views (incremental) для агрегаций

В предыдущих разделах мы рассмотрели использование Materialized views для преобразования и фильтрации данных. Однако Materialized views также можно использовать для предварительного вычисления агрегаций при вставке и сохранения результата. Этот результат можно обновлять данными из последующих вставок, что фактически позволяет заранее вычислять агрегации уже на этапе вставки. Основная идея в том, что результат часто представляет собой более компактное представление исходных данных (в случае агрегаций — частичный скетч). В сочетании с более простым запросом для чтения результатов из целевой таблицы это ускоряет выполнение запросов по сравнению с вычислением тех же значений на исходных данных. Рассмотрим следующий запрос, в котором мы вычисляем общий трафик по часам, используя наши структурированные журналы:

SELECT toStartOfHour(Timestamp) AS Hour,
        sum(toUInt64OrDefault(LogAttributes['size'])) AS TotalBytes
FROM otel_logs
GROUP BY Hour
ORDER BY Hour DESC
LIMIT 5

┌────────────────Hour─┬─TotalBytes─┐
│ 2019-01-26 16:00:00 │ 1661716343 │
│ 2019-01-26 15:00:00 │ 1824015281 │
│ 2019-01-26 14:00:00 │ 1506284139 │
│ 2019-01-26 13:00:00 │ 1580955392 │
│ 2019-01-26 12:00:00 │ 1736840933 │
└─────────────────────┴────────────┘

5 rows in set. Elapsed: 0.666 sec. Processed 10.37 million rows, 4.73 GB (15.56 million rows/s., 7.10 GB/s.)
Пиковое потребление памяти: 1.40 MiB.

Можно представить, что это типичный линейный график, который пользователи строят в Grafana. Этот запрос, конечно, очень быстрый — набор данных содержит всего 10 млн строк, а ClickHouse действительно быстр! Однако если масштабировать это до миллиардов и триллионов строк, в идеале хотелось бы сохранить такую производительность запроса.

Этот запрос был бы в 10 раз быстрее, если бы мы использовали таблицу otel_logs_v2, полученную из нашей ранее созданной materialized view, которая извлекает ключ size из Map LogAttributes. Здесь мы используем исходные данные только в иллюстративных целях и рекомендуем использовать предыдущую view, если такой запрос встречается часто.

Нам нужна таблица, которая будет принимать результаты, если мы хотим вычислять это во время вставки с помощью Materialized view. Эта таблица должна хранить только 1 строку на каждый час. Если для уже существующего часа поступает обновление, остальные столбцы должны быть объединены с существующей строкой для этого часа. Чтобы такое слияние инкрементальных состояний происходило, для остальных столбцов необходимо хранить частичные состояния. Для этого в ClickHouse нужен специальный тип движка: SummingMergeTree. Он заменяет все строки с одинаковым ключом сортировки одной строкой, содержащей суммарные значения для числовых столбцов. Следующая таблица будет объединять все строки с одинаковой датой, суммируя все числовые столбцы.

CREATE TABLE bytes_per_hour
(
  `Hour` DateTime,
  `TotalBytes` UInt64
)
ENGINE = SummingMergeTree
ORDER BY Hour

Чтобы продемонстрировать работу нашего materialized view, предположим, что таблица bytes_per_hour пуста и в неё ещё не поступали данные. Наш materialized view выполняет приведённый выше SELECT для данных, вставляемых в otel_logs (это выполняется по блокам заданного размера), а результаты отправляются в bytes_per_hour. Синтаксис показан ниже:

CREATE MATERIALIZED VIEW bytes_per_hour_mv TO bytes_per_hour AS
SELECT toStartOfHour(Timestamp) AS Hour,
       sum(toUInt64OrDefault(LogAttributes['size'])) AS TotalBytes
FROM otel_logs
GROUP BY Hour

Здесь ключевую роль играет clause TO, указывающий, куда будут отправлены результаты, то есть в bytes_per_hour. Если перезапустить наш OTel collector и повторно отправить журналы, table bytes_per_hour будет постепенно заполняться результатами приведённого выше запроса. После завершения мы можем проверить размер bytes_per_hour — у нас должна быть 1 строка на каждый час:

SELECT count()
FROM bytes_per_hour
FINAL

┌─count()─┐
│     113 │
└─────────┘

1 строка в наборе. Elapsed: 0.039 sec.

Здесь мы фактически сократили число строк с 10 млн (в otel_logs) до 113, сохранив результат нашего запроса. Ключевой момент в том, что если в таблицу otel_logs вставляются новые записи журнала, новые значения будут отправляться в bytes_per_hour для соответствующего часа, где они будут автоматически асинхронно объединяться в фоновом режиме — поскольку в bytes_per_hour хранится только одна строка на каждый час, эта таблица всегда остается и компактной, и актуальной. Поскольку слияние строк происходит асинхронно, на момент выполнения запроса у пользователя может оказаться более одной строки на час. Чтобы гарантировать, что все ожидающие строки будут слиты во время выполнения запроса, у нас есть два варианта:

Использовать модификатор FINAL у имени таблицы (что мы и сделали для запроса подсчета выше).
Выполнить агрегацию по ключу сортировки, используемому в нашей итоговой таблице, то есть по Timestamp, и суммировать метрики.

Обычно второй вариант эффективнее и гибче (таблицу можно использовать и для других задач), но первый может быть проще для некоторых запросов. Ниже мы покажем оба:

SELECT
        Hour,
        sum(TotalBytes) AS TotalBytes
FROM bytes_per_hour
GROUP BY Hour
ORDER BY Hour DESC
LIMIT 5

┌────────────────Hour─┬─TotalBytes─┐
│ 2019-01-26 16:00:00 │ 1661716343 │
│ 2019-01-26 15:00:00 │ 1824015281 │
│ 2019-01-26 14:00:00 │ 1506284139 │
│ 2019-01-26 13:00:00 │ 1580955392 │
│ 2019-01-26 12:00:00 │ 1736840933 │
└─────────────────────┴────────────┘

5 rows in set. Elapsed: 0.008 sec.

SELECT
        Hour,
        TotalBytes
FROM bytes_per_hour
FINAL
ORDER BY Hour DESC
LIMIT 5

┌────────────────Hour─┬─TotalBytes─┐
│ 2019-01-26 16:00:00 │ 1661716343 │
│ 2019-01-26 15:00:00 │ 1824015281 │
│ 2019-01-26 14:00:00 │ 1506284139 │
│ 2019-01-26 13:00:00 │ 1580955392 │
│ 2019-01-26 12:00:00 │ 1736840933 │
└─────────────────────┴────────────┘

5 rows in set. Elapsed: 0.005 sec.

Это ускорило наш запрос с 0,6 с до 0,008 с — более чем в 75 раз!

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

Более сложный пример

В приведенном выше примере выполняется простое агрегирование количества по часам с использованием SummingMergeTree. Для статистики, выходящей за рамки простых сумм, нужен другой движок целевой таблицы: AggregatingMergeTree. Предположим, мы хотим вычислить число уникальных IP-адресов (или уникальных пользователей) в день. Вот запрос для этого:

SELECT toStartOfHour(Timestamp) AS Hour, uniq(LogAttributes['remote_addr']) AS UniqueUsers
FROM otel_logs
GROUP BY Hour
ORDER BY Hour DESC

┌────────────────Hour─┬─UniqueUsers─┐
│ 2019-01-26 16:00:00 │     4763    │
│ 2019-01-22 00:00:00 │     536     │
└─────────────────────┴─────────────┘

113 rows in set. Elapsed: 0.667 sec. Processed 10.37 million rows, 4.73 GB (15.53 million rows/s., 7.09 GB/s.)

Для сохранения значения мощности с возможностью инкрементального обновления требуется AggregatingMergeTree.

CREATE TABLE unique_visitors_per_hour
(
  `Hour` DateTime,
  `UniqueUsers` AggregateFunction(uniq, IPv4)
)
ENGINE = AggregatingMergeTree
ORDER BY Hour

Чтобы ClickHouse понимал, что будут храниться состояния агрегатных функций, мы задаём для столбца UniqueUsers тип AggregateFunction, указывая функцию, из которой формируются частичные состояния (uniq), и тип исходного столбца (IPv4). Как и в случае с SummingMergeTree, строки с одинаковым значением ключа ORDER BY будут объединяться (Hour в приведённом выше примере). Соответствующее materialized view использует предыдущий запрос:

CREATE MATERIALIZED VIEW unique_visitors_per_hour_mv TO unique_visitors_per_hour AS
SELECT toStartOfHour(Timestamp) AS Hour,
        uniqState(LogAttributes['remote_addr']::IPv4) AS UniqueUsers
FROM otel_logs
GROUP BY Hour
ORDER BY Hour DESC

Обратите внимание, что к именам наших агрегатных функций мы добавляем суффикс State. Это гарантирует, что будет возвращаться агрегатное состояние функции, а не итоговый результат. Оно содержит дополнительную информацию, которая позволяет этому частичному состоянию объединяться с другими состояниями. После повторной загрузки данных из-за перезапуска Collector мы можем убедиться, что в таблице unique_visitors_per_hour доступно 113 строк.

SELECT count()
FROM unique_visitors_per_hour
FINAL

┌─count()─┐
│   113   │
└─────────┘

1 row in set. Elapsed: 0.009 sec.

В нашем итоговом запросе нужно использовать суффикс Merge у функций (поскольку в столбцах хранятся промежуточные состояния агрегации):

SELECT Hour, uniqMerge(UniqueUsers) AS UniqueUsers
FROM unique_visitors_per_hour
GROUP BY Hour
ORDER BY Hour DESC

┌────────────────Hour─┬─UniqueUsers─┐
│ 2019-01-26 16:00:00 │      4763   │
│ 2019-01-22 00:00:00 │      536    │
└─────────────────────┴─────────────┘

113 rows in set. Elapsed: 0.027 sec.

Обратите внимание: здесь мы используем GROUP BY, а не FINAL.

Использование materialized views (incremental) для быстрого поиска

При выборе ключа сортировки в ClickHouse следует учитывать типичные паттерны доступа и включать в него столбцы, которые часто используются в секциях фильтрации и агрегации. В сценариях обсервабилити это может быть ограничением, поскольку у пользователей паттерны доступа более разнообразны и их нельзя выразить одним набором столбцов. Это хорошо видно на примере, встроенном в схемы OTel по умолчанию. Рассмотрим схему по умолчанию для трасс:

CREATE TABLE otel_traces
(
        `Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
        `TraceId` String CODEC(ZSTD(1)),
        `SpanId` String CODEC(ZSTD(1)),
        `ParentSpanId` String CODEC(ZSTD(1)),
        `TraceState` String CODEC(ZSTD(1)),
        `SpanName` LowCardinality(String) CODEC(ZSTD(1)),
        `SpanKind` LowCardinality(String) CODEC(ZSTD(1)),
        `ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
        `ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
        `ScopeName` String CODEC(ZSTD(1)),
        `ScopeVersion` String CODEC(ZSTD(1)),
        `SpanAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
        `Duration` Int64 CODEC(ZSTD(1)),
        `StatusCode` LowCardinality(String) CODEC(ZSTD(1)),
        `StatusMessage` String CODEC(ZSTD(1)),
        `Events.Timestamp` Array(DateTime64(9)) CODEC(ZSTD(1)),
        `Events.Name` Array(LowCardinality(String)) CODEC(ZSTD(1)),
        `Events.Attributes` Array(Map(LowCardinality(String), String)) CODEC(ZSTD(1)),
        `Links.TraceId` Array(String) CODEC(ZSTD(1)),
        `Links.SpanId` Array(String) CODEC(ZSTD(1)),
        `Links.TraceState` Array(String) CODEC(ZSTD(1)),
        `Links.Attributes` Array(Map(LowCardinality(String), String)) CODEC(ZSTD(1)),
        INDEX idx_trace_id TraceId TYPE bloom_filter(0.001) GRANULARITY 1,
        INDEX idx_res_attr_key mapKeys(ResourceAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
        INDEX idx_res_attr_value mapValues(ResourceAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
        INDEX idx_span_attr_key mapKeys(SpanAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
        INDEX idx_span_attr_value mapValues(SpanAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
        INDEX idx_duration Duration TYPE minmax GRANULARITY 1
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SpanName, toUnixTimestamp(Timestamp), TraceId)

Эта схема оптимизирована для фильтрации по ServiceName, SpanName и Timestamp. Однако при трассировке пользователям также нужна возможность выполнять поиск по конкретному TraceId и получать связанные с этим трейсом спаны. Хотя это поле входит в ключ упорядочивания, его расположение в конце означает, что фильтрация будет менее эффективной, и, вероятно, при извлечении одного трейса потребуется сканировать значительные объёмы данных. OTel collector также устанавливает materialized view и связанную с ней таблицу, чтобы решить эту проблему. Таблица и представление показаны ниже:

CREATE TABLE otel_traces_trace_id_ts
(
        `TraceId` String CODEC(ZSTD(1)),
        `Start` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
        `End` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
        INDEX idx_trace_id TraceId TYPE bloom_filter(0.01) GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY (TraceId, toUnixTimestamp(Start))

CREATE MATERIALIZED VIEW otel_traces_trace_id_ts_mv TO otel_traces_trace_id_ts
(
        `TraceId` String,
        `Start` DateTime64(9),
        `End` DateTime64(9)
)
AS SELECT
        TraceId,
        min(Timestamp) AS Start,
        max(Timestamp) AS End
FROM otel_traces
WHERE TraceId != ''
GROUP BY TraceId

Представление фактически гарантирует, что в таблице otel_traces_trace_id_ts хранятся минимальная и максимальная временные метки для трассировки. Эта таблица, упорядоченная по TraceId, позволяет эффективно извлекать эти временные метки. Эти диапазоны временных меток, в свою очередь, можно использовать при выполнении запросов к основной таблице otel_traces. Точнее, при получении трассировки по её идентификатору Grafana использует следующий запрос:

WITH 'ae9226c78d1d360601e6383928e4d22d' AS trace_id,
        (
        SELECT min(Start)
          FROM default.otel_traces_trace_id_ts
          WHERE TraceId = trace_id
        ) AS trace_start,
        (
        SELECT max(End) + 1
          FROM default.otel_traces_trace_id_ts
          WHERE TraceId = trace_id
        ) AS trace_end
SELECT
        TraceId AS traceID,
        SpanId AS spanID,
        ParentSpanId AS parentSpanID,
        ServiceName AS serviceName,
        SpanName AS operationName,
        Timestamp AS startTime,
        Duration * 0.000001 AS duration,
        arrayMap(key -> map('key', key, 'value', SpanAttributes[key]), mapKeys(SpanAttributes)) AS tags,
        arrayMap(key -> map('key', key, 'value', ResourceAttributes[key]), mapKeys(ResourceAttributes)) AS serviceTags
FROM otel_traces
WHERE (traceID = trace_id) AND (startTime >= trace_start) AND (startTime <= trace_end)
LIMIT 1000

Здесь CTE определяет минимальную и максимальную временные метки для идентификатора trace ID ae9226c78d1d360601e6383928e4d22d, а затем использует их, чтобы отфильтровать основную таблицу otel_traces по связанным с ним спанам. Этот же подход можно применять и для похожих сценариев доступа. Мы рассматриваем аналогичный пример в разделе «Моделирование данных» здесь.

Использование проекций

Проекции ClickHouse позволяют задавать для таблицы несколько секций ORDER BY. В предыдущих разделах мы рассматривали, как materialized view можно использовать в ClickHouse для предварительного вычисления агрегаций, преобразования строк и оптимизации запросов обсервабилити для разных сценариев доступа. Мы приводили пример, в котором materialized view отправляет строки в целевую таблицу с другим ключом сортировки, чем у исходной таблицы, принимающей вставки, чтобы оптимизировать поиск по trace ID. Проекции можно использовать для решения той же задачи, позволяя пользователю оптимизировать запросы по столбцу, который не входит в первичный ключ. Теоретически эту возможность можно использовать, чтобы задать для таблицы несколько ключей сортировки, но у этого есть один существенный недостаток: дублирование данных. В частности, данные нужно будет записывать в порядке основного первичного ключа, а также в порядке, заданном для каждой проекции. Это замедлит вставки и потребует больше места на диске.

Проекции и materialized viewПроекции предоставляют многие из тех же возможностей, что и materialized view, но использовать их следует с осторожностью, поскольку во многих случаях предпочтительнее именно materialized view. Важно понимать их недостатки и когда их уместно применять. Например, хотя проекции можно использовать для предварительного вычисления агрегаций, для этого мы рекомендуем использовать Materialized views.

Рассмотрим следующий запрос, который фильтрует нашу таблицу otel_logs_v2 по кодам ошибок 500. Вероятно, это распространенный сценарий для журналирования, когда пользователи хотят фильтровать данные по кодам ошибок:

SELECT Timestamp, RequestPath, Status, RemoteAddress, UserAgent
FROM otel_logs_v2
WHERE Status = 500
FORMAT `Null`

Ok.

0 rows in set. Elapsed: 0.177 sec. Processed 10.37 million rows, 685.32 MB (58.66 million rows/s., 3.88 GB/s.)
пиковое потребление памяти: 56.54 MiB.

Используйте Null для оценки производительностиЗдесь мы не выводим результаты с помощью FORMAT Null. Это заставляет систему прочитать все результаты, но не возвращать их, и тем самым предотвращает досрочное завершение запроса из-за LIMIT. Это нужно лишь для того, чтобы показать, сколько времени занимает сканирование всех 10 млн строк.

Приведённый выше запрос требует линейного сканирования при выбранном ключе сортировки (ServiceName, Timestamp). Хотя мы могли бы добавить Status в конец ключа сортировки, чтобы повысить производительность этого запроса, можно также добавить проекцию.

ALTER TABLE otel_logs_v2 (
  ADD PROJECTION status
  (
     SELECT Timestamp, RequestPath, Status, RemoteAddress, UserAgent ORDER BY Status
  )
)

ALTER TABLE otel_logs_v2 MATERIALIZE PROJECTION status

Обратите внимание: сначала нужно создать проекцию, а затем материализовать её. Эта команда приводит к тому, что данные хранятся на диске дважды — в двух разных порядках. Проекцию также можно определить при создании данных, как показано ниже, и она будет автоматически обновляться при вставке данных.

CREATE TABLE otel_logs_v2
(
        `Body` String,
        `Timestamp` DateTime,
        `ServiceName` LowCardinality(String),
        `Status` UInt16,
        `RequestProtocol` LowCardinality(String),
        `RunTime` UInt32,
        `Size` UInt32,
        `UserAgent` String,
        `Referer` String,
        `RemoteUser` String,
        `RequestType` LowCardinality(String),
        `RequestPath` String,
        `RemoteAddress` IPv4,
        `RefererDomain` String,
        `RequestPage` String,
        `SeverityText` LowCardinality(String),
        `SeverityNumber` UInt8,
        PROJECTION status
        (
           SELECT Timestamp, RequestPath, Status, RemoteAddress, UserAgent
           ORDER BY Status
        )
)
ENGINE = MergeTree
ORDER BY (ServiceName, Timestamp)

Важно отметить, что если проекция создаётся через ALTER, то после выполнения команды MATERIALIZE PROJECTION она создаётся асинхронно. Ход этой операции можно проверить с помощью следующего запроса, дождавшись значения is_done=1.

SELECT parts_to_do, is_done, latest_fail_reason
FROM system.mutations
WHERE (`table` = 'otel_logs_v2') AND (command LIKE '%MATERIALIZE%')

┌─parts_to_do─┬─is_done─┬─latest_fail_reason─┐
│           0 │     1   │                    │
└─────────────┴─────────┴────────────────────┘

1 row in set. Elapsed: 0.008 sec.

Если повторить приведённый выше запрос, можно увидеть, что производительность значительно выросла ценой дополнительного объёма хранилища (о том, как это измерить, см. “Измерение размера таблицы и степени сжатия”).

SELECT Timestamp, RequestPath, Status, RemoteAddress, UserAgent
FROM otel_logs_v2
WHERE Status = 500
FORMAT `Null`

0 rows in set. Elapsed: 0.031 sec. Processed 51.42 thousand rows, 22.85 MB (1.65 million rows/s., 734.63 MB/s.)
Пиковое потребление памяти: 27.85 MiB.

В приведённом выше примере мы указываем в проекции столбцы, использованные в предыдущем запросе. Это означает, что на диске как часть проекции будут храниться только эти столбцы, отсортированные по Status. Если же вместо этого использовать здесь SELECT *, будут сохранены все столбцы. Это позволит большему числу запросов (использующих любое подмножество столбцов) воспользоваться проекцией, но потребует дополнительного места на диске. О том, как измерить дисковое пространство и степень сжатия, см. “Измерение размера таблицы и степени сжатия”.

Вторичные индексы / индексы пропуска данных

Как бы хорошо ни был настроен первичный ключ в ClickHouse, некоторые запросы неизбежно потребуют полного сканирования таблицы. Хотя это можно частично компенсировать с помощью materialized view (а для некоторых запросов — и проекций), они требуют дополнительного сопровождения, а пользователи должны знать об их наличии, чтобы действительно их задействовать. Если в традиционных реляционных базах данных эта задача решается вторичными индексами, то в столбцовых базах данных, таких как ClickHouse, они неэффективны. Вместо этого ClickHouse использует индексы “Skip”, которые могут значительно повысить производительность запросов, позволяя базе данных пропускать большие фрагменты данных, в которых нет подходящих значений. Схемы OTel по умолчанию используют вторичные индексы в попытке ускорить доступ к map. Хотя, по нашему опыту, они в целом неэффективны, и мы не рекомендуем копировать их в свою пользовательскую схему, индексы пропуска всё же могут быть полезны. Прежде чем пытаться применять их, вам следует прочитать и понять руководство по вторичным индексам. В целом они эффективны, когда существует сильная корреляция между первичным ключом и целевым непервичным столбцом/выражением, а пользователи ищут редкие значения, то есть такие, которые встречаются лишь в небольшом числе гранул.

Текстовый индекс для полнотекстового поиска

ClickHouse предоставляет специализированный текстовый индекс для полнотекстового поиска. Этот индекс создаёт обратный индекс по токенизированным текстовым данным, обеспечивая быстрый поиск по токенам. Текстовые индексы доступны начиная с версии ClickHouse 26.2. Их можно определять для столбцов следующих типов в таблицах MergeTree: String, FixedString, Array(String), Array(FixedString) и Map (через map-функции mapKeys и mapValues). Текстовый индекс требует аргумента tokenizer в своём определении. При необходимости также можно указать функцию предобработки, чтобы преобразовать входную строку перед токенизацией. Для поиска по индексу рекомендуется использовать функции hasAnyTokens и hasAllTokens. Некоторые традиционные функции поиска по строкам также автоматически оптимизируются при наличии текстового индекса. Подробности и список поддерживаемых функций см. в документации здесь и здесь. В примерах ниже мы используем набор данных со структурированными журналами.

CREATE TABLE otel_logs
(
        `Body` String,
        `Timestamp` DateTime,
        `ServiceName` LowCardinality(String),
        `Status` UInt16,
        `RequestProtocol` LowCardinality(String),
        `RunTime` UInt32,
        `Size` UInt32,
        `UserAgent` String,
        `Referer` String,
        `RemoteUser` String,
        `RequestType` LowCardinality(String),
        `RequestPath` String,
        `RemoteAddress` IPv4,
        `RefererDomain` String,
        `RequestPage` String,
        `SeverityText` LowCardinality(String),
        `SeverityNumber` UInt8
)
ENGINE = MergeTree
ORDER BY Timestamp
SETTINGS index_granularity = 8192

Мы также можем использовать hasAnyTokens без текстового индекса, но тогда запрос выполнит медленное полное сканирование столбца Body:

SELECT count()
FROM otel_logs
WHERE hasAllTokens(Body, ['Connection', 'accepted'])

Query id: ff0b866c-6df7-47be-9e36-795ef3888169

   ┌─count()─┐
1. │   27281 │
   └─────────┘

1 row in set. Elapsed: 0.584 sec. Processed 19.95 million rows, 3.08 GB (34.15 million rows/s., 5.27 GB/s.)

Добавление текстового индекса

Текстовый индекс для столбца Body можно добавить при создании таблицы:

CREATE TABLE otel_logs_index_body
(
        `Body` String,
        `Timestamp` DateTime,
        `ServiceName` LowCardinality(String),
        `Status` UInt16,
        `RequestProtocol` LowCardinality(String),
        `RunTime` UInt32,
        `Size` UInt32,
        `UserAgent` String,
        `Referer` String,
        `RemoteUser` String,
        `RequestType` LowCardinality(String),
        `RequestPath` String,
        `RemoteAddress` IPv4,
        `RefererDomain` String,
        `RequestPage` String,
        `SeverityText` LowCardinality(String),
        `SeverityNumber` UInt8,
         INDEX idx_body Body TYPE text(tokenizer = splitByNonAlpha) GRANULARITY 100000000
)
ENGINE = MergeTree
ORDER BY Timestamp
SETTINGS index_granularity = 8192

или добавлен позднее с помощью ALTER TABLE:

ALTER TABLE otel_logs ADD INDEX idx_body Body TYPE text(tokenizer = splitByNonAlpha) GRANULARITY 100000000;
ALTER TABLE otel_logs MATERIALIZE INDEX idx_body;

Если мы снова выполним тот же запрос SELECT, будет выполнен поиск по текстовому индексу. Объем обрабатываемых данных уменьшится с гигабайт до мегабайт, а производительность повысится примерно в 45 раз.

SELECT count()
FROM otel_logs_index_body
WHERE hasAllTokens(Body, ['Connection', 'accepted'])

Query id: ebc31a94-92b3-48aa-860a-939d7e788ef4

   ┌─count()─┐
1. │   27281 │
   └─────────┘

1 row in set. Elapsed: 0.013 sec. Processed 20.41 million rows, 20.41 MB (1.59 billion rows/s., 1.59 GB/s.)
Пиковое потребление памяти: 15.23 MiB.

Использование препроцессора

В этом наборе данных столбец Body содержит строку в формате JSON с несколькими парами ключ-значение (например, msg, id, ctx, attr и т. д.). Предположим, что нас интересует поиск только по полю msg. Вместо индексации всей JSON-строки мы можем определить препроцессор, чтобы извлекать только значение msg перед токенизацией. Например:

 INDEX idx_text Body TYPE text(tokenizer = splitByNonAlpha,
                               preprocessor = JSONExtract(Body, 'msg', 'String'))

В этом примере препроцессор:

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

SELECT count()
FROM otel_logs_text_body_preprocessed
WHERE hasAllTokens(Body, ['Connection', 'accepted'])

Query id: f6a5cd9c-665f-4e4f-82f2-d6a4408a68a8

   ┌─count()─┐
1. │   27281 │
   └─────────┘

1 row in set. Elapsed: 0.006 sec. Processed 13.54 million rows, 13.54 MB (2.45 billion rows/s., 2.45 GB/s.)
Пиковое потребление памяти: 1.95 MiB.

По сравнению с индексом без предварительной обработки производительность повышается примерно в 2 раза. Использование препроцессора также уменьшает размер индекса с гигабайтов до нескольких сотен килобайт — примерно до 0,01 % от исходного размера

SELECT
    `table`,
    formatReadableSize(data_compressed_bytes) AS compressed_size,
    formatReadableSize(data_uncompressed_bytes) AS uncompressed_size
FROM system.data_skipping_indices
WHERE startsWith(`table`, 'otel_logs')

Query id: 730e4b77-e697-40b3-a24d-67219ec42075

   ┌─table───────────────────────────────────┬─compressed_size─┬─uncompressed_size─┐
1. │ otel_logs_text_index_body_preprocessed  │ 423.98 KiB      │ 424.29 KiB        │
2. │ otel_logs_text_index_body               │ 2.76 GiB        │ 2.78 GiB          │
   └─────────────────────────────────────────┴─────────────────┴───────────────────┘

**Другие индексы для текстового поиска Подробнее о вторичных индексах пропуска можно узнать здесь.

Извлечение из Map

Тип Map широко распространен в схемах OTel. В нем и ключи, и значения должны быть одного типа — этого достаточно для метаданных, таких как метки Kubernetes. Имейте в виду: при запросе по вложенному ключу Map загружается весь столбец целиком. Если в Map много ключей, это может заметно ухудшить производительность запроса, поскольку с диска придется читать больше данных, чем если бы этот ключ был вынесен в отдельный столбец. Если вы часто запрашиваете определенный ключ, подумайте о том, чтобы вынести его в отдельный столбец верхнего уровня. Обычно такая задача возникает уже после развертывания, по мере появления типичных паттернов доступа, и ее бывает сложно предвидеть до выхода в production. О том, как изменить схему после развертывания, см. в разделе “Управление изменениями схемы”.

Измерение размера таблицы и степени сжатия

Одна из главных причин, по которым ClickHouse используют для обсервабилити, — сжатие. Помимо существенного снижения затрат на хранилище, меньший объём данных на диске означает меньше операций I/O, а также более быстрые запросы и вставки. Снижение объёма IO перевешивает накладные расходы любого алгоритма сжатия по нагрузке на CPU. Поэтому повышение степени сжатия данных должно быть одной из первоочередных задач, если вы хотите, чтобы запросы ClickHouse выполнялись быстро. Подробности об измерении степени сжатия можно найти здесь.

Последнее изменение 23 июля 2026 г.

Интеграция OpenTelemetry для сбора данныхИнтеграция OpenTelemetry и ClickHouse для обсервабилити

​Извлечение структуры с помощью SQL

​Материализованные столбцы

​Materialized views

​Будьте осторожны с типами

​Выбор первичного ключа (ключа сортировки)

​Использование Map

​Использование псевдонимов

​Оптимизация типов

​Использование кодеков

​Использование словарей

​При вставке vs при выполнении запроса

​Использование IP-словарей

​Использование словарей на основе регулярных выражений (разбор user-agent)

​Дополнительные материалы

​Ускорение запросов

​Использование Materialized views (incremental) для агрегаций

​Более сложный пример

​Использование materialized views (incremental) для быстрого поиска

​Использование проекций

​Вторичные индексы / индексы пропуска данных

​Текстовый индекс для полнотекстового поиска

​Добавление текстового индекса

​Использование препроцессора

​Извлечение из Map

​Измерение размера таблицы и степени сжатия

Извлечение структуры с помощью SQL

Материализованные столбцы

Materialized views

Будьте осторожны с типами

Выбор первичного ключа (ключа сортировки)

Использование Map

Использование псевдонимов

Оптимизация типов

Использование кодеков

Использование словарей

При вставке vs при выполнении запроса

Использование IP-словарей

Использование словарей на основе регулярных выражений (разбор user-agent)

Дополнительные материалы

Ускорение запросов

Использование Materialized views (incremental) для агрегаций

Более сложный пример

Использование materialized views (incremental) для быстрого поиска

Использование проекций

Вторичные индексы / индексы пропуска данных

Текстовый индекс для полнотекстового поиска

Добавление текстового индекса

Использование препроцессора

Извлечение из Map

Измерение размера таблицы и степени сжатия