Зачем создавать разные проекции таблиц в базе данных и как это работает в Clickhouse: практический пример с агрегатным запросом. Возможности и ограничения механизма проекций в колоночной аналитической СУБД.
Что такое проекции и как они реализованы в ClickHouse
Поскольку основное назначение ClickHouse – аналитика больших объемов данных в реальном времени, это колоночное хранилище поддерживает различные механизмы оптимизации аналитических запросов. Одним из них является механизм проекций. Проекция – это простая операция реляционной алгебры для выбора определенных столбцов из таблицы при получении данных, исключая другие. Это похоже на создание подмножества данных, которые содержат только интересующие атрибуты. Проекция помогает оптимизировать запросы, улучшая производительность и упрощая обработку данных. Благодаря проекциям снижается нагрузка на дисковую подсистему и процессор, так как запросы выполняются быстрее и требуют меньше ресурсов.
Проекции особенно полезны для часто выполняемых сложных аналитических запросов, где требуется быстрая агрегация больших объемов данных. Однако, использование проекций увеличивает объем хранимых данных из-за фактического создания дополнительных копий данных в агрегированном виде. Впрочем, архитектура ClickHouse частично обходит этот недостаток.
В ClickHouse проекции представляют собой механизм, который позволяет создавать материальные представления данных внутри одной таблицы для оптимизации специфических запросов. В отличие от реляционных хранилищ, в ClickHouse проекции тесно интегрированы с архитектурой хранения данных в виде, оптимизированном для определённых запросов, без необходимости создания отдельных таблиц. Проекции хранятся как отдельные физические структуры, которые могут содержать предагрегированные данные. Это позволяет выполнять сложные агрегатные запросы быстрее, так как проекции уже содержат необходимую информацию. Таким образом, проекции в ClickHouse разработаны с учётом аналитических нагрузок и, в отличие от материализованных представлений в других СУБД, автоматически обновляются вместе с изменением исходных данных. Проекции хранят данные в формате, оптимизирующем выполнение запросов. Это особенно полезно для выполнения запросов по столбцу, который не является частью первичного ключа. Предварительное агрегирование столбцов позволит сократить как вычисления, так и операции дискового ввода-вывода. Можно определить одну или несколько проекций для таблицы, и во время анализа запроса ClickHouse выберет проекцию с наименьшим объемом данных для сканирования, не изменяя пользовательский SQL-запрос. ClickHouse автоматически выбирает проекцию, если она может ускорить выполнение запроса. Это возможно благодаря оптимизатору запросов, который анализирует доступные проекции и выбирает наиболее подходящую. Примечательно, что проекции не делают запросы, использующие выражение сортировки ORDER BY, более эффективными, даже при совпадении столбцов, указанных в этом операторе для проекции и запроса.
Проекции в ClickHouse создают внутри новую скрытую таблицу, увеличивая потребление ресурсов дискового ввода-вывода и пространства. Скрытая таблица и ее первичный индекс, созданная проекцией, теперь может неявно использоваться для значительного ускорения выполнения запроса, фильтрующего конкретный столбец. Например, если проекция определила другой первичный ключ, все данные из исходной таблицы будут дублированы. Проекции могут быть созданы с использованием различных движков ClickHouse (MergeTree, AggregatingMergeTree и пр.), что позволяет адаптировать их под конкретные сценарии использования.
Чтобы проверить, что запрос использует проекцию, можно посмотреть таблицу system.query_log:
SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'
В поле projections выведется имя используемой проекции. Если при выполнении запроса с заданным идентификатором не использовалась ни одна проекция, ответ на запрос вернет пустое значения поля projections.
Манипулирование проекциями в ClickHouse поддерживается только для таблиц с движками семейства MergeTree, включая реплицированные варианты. При этом с проекциями возможны следующие операции:
- Добавление описания проекции в метаданные таблицы;
- Удаление описания проекции из метаданных таблиц и файлов проекции с диска;
- Удаление файлов проекции с диска без удаления описания из метаданных таблицы
- Материализации проекции в разделе партиционированной таблицы;
Команды добавления и удаления проекций являются легковесными в том смысле, что они только изменяют метаданные или удаляют файлы. Кроме того, они реплицируются, синхронизируя метаданные проекций через ClickHouse Keeper или ZooKeeper.
Разобравшись, что такое проекции и зачем они нужны, далее рассмотрим пример работы с проекциями.
Практический пример
В качестве примера создадим таблицу пользовательского поведения user_events с четырьмя столбцами:
- timestamp — время события, тип DateTime;
- user_session — идентификатор сессии пользователя, тип String;
- page – страница сайта, на которой произошло событие, тип перечисление (Enum);
- event_type – тип события, перечисление Enum.
Таблица будет использовать движок MergeTree, который оптимизирован для больших объёмов данных и позволяет выполнять эффективные запросы. Упорядочим данные по типу событий, чтобы агрегатные запросы с ними выполнялись быстрее.
Запрос создания такой таблицы в ClickHouse выглядит так:
CREATE TABLE user_events (
timestamp DateTime,
user_session String,
page Enum('/product' = 1, '/about' = 2, '/provider' = 3, '/question' = 4, '/' =5),
event_type Enum('click' = 1, 'download' = 2, 'submit' = 3, 'scroll' = 4)
) ENGINE = MergeTree()
ORDER BY event_type;
Создадим проекцию event_type_projection, которая будет содержать данные таблицы, упорядоченные по типу события. Чтобы сократить дублирование данных, в проекции укажем тот же ключ сортировки.
ALTER TABLE user_events ADD PROJECTION event_type_projection ( SELECT * ORDER BY event_type );
Материализуем эту проекцию, чтобы данные в ней физически пересчитаны и упорядочены согласно указанным правилам.
ALTER TABLE user_events MATERIALIZE PROJECTION event_type_projection;
Сейчас проекция и основная таблица используют один и тот же ключ сортировки — тип события, event_type. Это эффективно, если большинство запросов к таблице будут фильтроваться по этому полю. Однако, если у нас планируется фильтрация или группировка по другим полям, например, страницам сайта, имеет смысл создать проекции с соответствующими ключами сортировки, чтобы оптимизировать выполнение различных запросов. Например, создадим и материализуем проекцию для агрегаций по страницам сайта.
ALTER TABLE user_events ADD PROJECTION page_projection ( SELECT * ORDER BY page ); ALTER TABLE user_events MATERIALIZE PROJECTION page_projection;
Теперь наполним таблицу случайными данными:
INSERT INTO user_events
SELECT
now() - INTERVAL rand() % 10000 SECOND AS timestamp,
concat('user_session_', toString(rand() % 10 + 1)) AS session,
arrayElement(['/product', '/about', '/provider', '/question', '/'], rand() % 5 + 1) AS page,
arrayElement(['click', 'download', 'submit', 'scroll'], rand() % 4 + 1) AS event_type
FROM numbers(100);
Сделаем агрегатные запросы о количестве событий пользовательского поведения, сгруппированных по типу и по страницам. Чтобы вывод результатов был более наглядным, укажем формат вывода Pretty. При выполнении этих запросов Clickhouse сам будет использовать ранее созданные проекции, выбирая наиболее подходящую.
SELECT
event_type AS "Тип события",
count() AS "Количество событий"
FROM user_events
GROUP BY event_type
FORMAT Pretty;
SELECT
page AS "Страница сайта",
count() AS "Количество событий"
FROM user_events
GROUP BY page
FORMAT Pretty;
Протестируем все запросы в песочнице https://fiddle.clickhouse.com/.
Научиться работать с ClickHouse вам помогут специализированные курсы в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве:
Источники
