3 вида представлений в ClickHouse

Чем материализованное представление в ClickHouse отличается от обычного, зачем нужны LIVE-представления и как их использовать. Примеры SQL-запросов с VIEW для самой популярной колоночной аналитической СУБД.

Представления vs словари в ClickHouse

Поскольку ClickHouse, как типовая колоночная СУБД, используется для аналитической обработки огромных объемов данных в реальном времени, вопрос ускорения вычислений для нее очень актуален. Одним из приемов, которые позволяют повысить скорость доступа к данным, является хранение часто используемых данных в области ближнего доступа, подобно кэшу памяти или жесткого диска. Обычно в реляционных СУБД для этого есть виртуальные таблицы или представления (view) – заранее вычисленные результаты, которые можно повторно использовать при выполнении SQL-запросов, позволяя снизить количество ресурсоемких операций соединения. Это особенно актуально для ClickHouse, которая не очень хорошо поддерживает операторы JOIN, что мы недавно упоминали здесь. В отличие от словарей, о которых мы рассказывали в прошлый раз, представления в ClickHouse – это не примитивная структура данных типа ключ-значение в оперативной памяти сервера, а результаты выполнения SQL-запроса. И, если данные словаря можно только читать с помощью SQL-запроса, то представления можно как читать, так и создавать, используя соответствующие выражения.

ClickHouse позволяет создавать представления трех видов:

• обычные, которые не хранят данные, а читают их из другой таблицы при каждом доступе;
• материализованные, которые хранят данные, преобразованные SQL-запросом с оператором SELECT;
• LIVE-представления, которые хранят в оперативной памяти результат запроса на выборку, указанного при создании, и обновляется сразу же при изменении этого результата.

Как их создать и работать с этими представлениями, рассмотрим далее на практическом примере.

Обычные представления

Поскольку обычные представления не хранят данные, а читают их из другой таблицы при каждом запросе, по своей сути обычные представления являются сохраненным запросом, который используется как подзапрос в разделе FROM. Предположим, в ClickHouse есть таблица с событиями пользовательского поведения. Чтобы она хранила данные о времени события, пользовательской сессии и типе события (клик, просмотр, отправка данных, скачивание файла, загрузка файла), напишем следующий SQL-запрос на создание этой таблицы:

CREATE TABLE user_behavior_events
(
    EventTime DateTime,
    UserSessionID String,
    EventType Enum('click' = 1, 'view' = 2, 'data_submission' = 3, 'file_download' = 4, 'file_upload' = 5)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY (UserSessionID, EventTime);

В этом запросе определены следующие поля:

• EventTime — время события;
• UserSessionID — пользовательская сессия;
• EventType — перечисление, которое содержит возможные типы событий(клик, просмотр, отправка данных, скачивание файла, загрузка файла).

При создании таблицы user_behavior_events указано использовать движок MergeTree, который отлично подходит для хранения временных рядов в ClickHouse, поддерживая высокую скорость вставки и запросов на больших объемах данных. А чтобы повысить производительность SQL-запросов и оптимизировать хранение данных, в DDL-запросе указано выражение PARTITION BY toYYYYMM(EventTime), которое разделяет большую таблицу на несколько более мелких по месяцам. Наконец, выражение ORDER BY (UserSessionID, EventTime) задает порядок сортировки данных внутри каждого раздела, чтобы быстрее выполнять запросы, фильтрующие данные по полю UserSessionID и временным диапазонам.

Создадим представление user_activity_summary, которое будет оценивать активность пользователя по количеству событий в течение пользовательской сессии:

CREATE VIEW user_activity_summary 
ENGINE = SummingMergeTree() 
AS
SELECT
    UserSessionID,
    count(*) AS NumberOfEvents,
    min(EventTime) AS SessionStart,
    max(EventTime) AS SessionEnd,
    max(EventTime) - min(EventTime) AS SessionDuration
FROM user_behavior_events
GROUP BY UserSessionID
ORDER BY NumberOfEvents DESC;

Этот запрос создаст представление user_activity_summary, которое группирует данные по UserSessionID. В качестве движка таблицы будем использовать SummingMergeTree, который хорошо работает с простыми агрегациями. Для каждой сессии будет посчитано количество событий NumberOfEvents, время начала сессии SessionStart, время окончания сессии SessionEnd и продолжительность сессии SessionDuration. Данные будут отсортированы по убыванию количества событий в сессиях, что позволяет быстро увидеть самые активные сессии.

По своей сути это представление аналогично использованию подзапроса:

SELECT UserSessionID, SessionDuration FROM (
    SELECT 
        UserSessionID,
        count(*) AS NumberOfEvents,
        min(EventTime) AS SessionStart,
        max(EventTime) AS SessionEnd,
        max(EventTime) - min(EventTime) AS SessionDuration
    FROM user_behavior_events
    GROUP BY UserSessionID
) AS session_summary;

Внешний SQL-запрос выбирает из результатов подзапроса только UserSessionID и SessionDuration, т.е. идентификатор каждой пользовательской сессии и её продолжительность.

Вставим в таблицу user_behavior_events 10 записей с помощью следующего SQL-запроса:

INSERT INTO user_behavior_events (EventTime, UserSessionID, EventType) VALUES
('2024-04-01 00:00:00', 'session_1', 1),
('2024-04-01 01:00:00', 'session_1', 2),
('2024-04-01 02:45:00', 'session_1', 3),
('2024-04-01 03:00:00', 'session_2', 4),
('2024-04-01 04:00:12', 'session_2', 5),
('2024-04-02 00:01:20', 'session_3', 1),
('2024-04-02 01:00:00', 'session_3', 2),
('2024-04-02 02:00:00', 'session_4', 3),
('2024-04-02 03:14:56', 'session_5', 4),
('2024-04-02 04:01:20', 'session_6', 5);

Применим к этой таблице, вернее к ее представлению, следующий запрос:

SELECT UserSessionID, SessionDuration FROM user_activity_summary WHERE UserSessionID='session_3';

Он вернет такие результаты:

UserSessionID | SessionDuration
session_3     | 00:58:40

Продолжительность сессии SessionDuration рассчитывается как время между первым и последним событием пользовательской сессии с идентификатором session_3:

• первое событие с session_3: ‘2024-04-02 00:01:20’;
• последнее событие с session_3: ‘2024-04-02 01:00:00’.

Разница между этими моментами времени составляет 58 минут и 40 секунд.

Материализованные представления

В отличие от обычного представления (VIEW), которое вычисляется при каждом обращении к нему, материализованное представление (MATERIALIZED VIEW) сохраняет данные физически после выполнения определенного запроса и обновляется, как правило, при добавлении новых данных в исходную таблицу. Материализованные представления в ClickHouse хранят данные, преобразованные соответствующим запросом SELECT. При вставке данных в таблицу, указанную в этом запросе, они преобразуются и полученный результат вставляется в представление. Материализованные представления ClickHouse автоматически преобразуют данные между таблицами. Они подобны триггерам, которые выполняют запросы к вставленным строкам и помещают результат во вторую таблицу.

Материализованные представления используют имена столбцов вместо порядка их следования при вставке в целевую таблицу. Если в результатах запроса SELECT некоторые имена столбцов отсутствуют, используется значение по умолчанию, даже для не Nullable-столбцов. Поэтому рекомендуется использовать псевдонимы для каждого столбца при работе с материализованными представлениями. По сути, материализованные представления в ClickHouse похожи на триггеры после вставки (after insert). Если в запросе к материализованному представлению есть агрегация, она применяется только к вставляемому блоку записей. Любые изменения существующих данных исходной таблицы (обновление, удаление, удаление раздела и пр.) не изменяют ее материализованное представление.

В отличие от PostgreSQL и основанном на нем Greenplum, где материализованные представления необходимо обновлять вручную, ClickHouse обновляет их сам, автоматически и сжимает данные в них во время простоя, чтобы ускорить будущий поиск. Впрочем, на скорость поиска влияет движок таблицы, который рекомендуется выбирать в зависимости от сценария. Например, для суммирования данных лучше подходит SummingMergeTree, который распараллеливает процесс вычисления суммы. А для получения среднего значения отлично подойдет механизм AggregatingMergeTree. Подробнее о движках таблиц в ClickHouse читайте в нашей новой статье.

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

Пример создания материализованного представления к таблице user_behavior_events:

CREATE MATERIALIZED VIEW mv_user_activity_summary AS
SELECT
    UserSessionID,
    count(*) AS NumberOfEvents,
    min(EventTime) AS SessionStart,
    max(EventTime) AS SessionEnd,
    max(EventTime) - min(EventTime) AS SessionDuration
FROM user_behavior_events
GROUP BY UserSessionID;

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

LIVE-представления

В заключение хочется упомянуть еще один вид представлений, которые есть в ClickHouse – LIVE VIEW. Оно хранит в оперативной памяти результат запроса на выборку, указанного при создании, и обновляется сразу же при изменении этого результата. Поэтому LIVE-представления быстрее материализованных представлений и отлично подходят для повторяющихся запросов. Также они позволяют отслеживать изменения данных, отправляя push-уведомления при изменении результата исходного запроса SELECT, когда в исходную таблицу вставляются новые данные. LIVE-представления объединяют имеющийся результат с новыми данными. В случае вложенных подзапросов к исходному запросу на создание LIVE-представления, их результаты не кэшируются, кэшируется только результат основного SQL-запроса.

Для примера создадим LIVE-представление к ранее представленной таблице с событиями пользовательского поведения, которое будет содержать агрегированную информацию о пользовательских сессиях, включая их идентификаторы, количество событий в каждой сессии, время начала и окончания сессии, а также общую продолжительность сессии. Эти данные будут автоматически обновляться каждые 5 секунд с учётом новых данных в исходной таблице user_behavior_events. Для этого напишем следующий запрос:

CREATE LIVE VIEW user_activity_live WITH REFRESH 5 AS
SELECT
    UserSessionID,
    count() AS EventCount,
    min(EventTime) AS SessionStart,
    max(EventTime) AS SessionEnd,
    max(EventTime) - min(EventTime) AS SessionDuration
FROM user_behavior_events
GROUP BY UserSessionID;

Поскольку LIVE VIEW обновляется в реальном времени, с помощью этого представления можно отслеживать активность пользователя по мере её изменения при каждом добавлении новых данных в основную таблицу user_behavior_events. Это делается с помощью следующего запроса:

WATCH user_activity_live;

А получить список изменений исходной таблицы поможет ключевое слово EVENTS:

WATCH user_activity_live EVENTS;

LIVE-представления в ClickHouse пока являются экспериментальной функцией и имеют ряд ограничений:

• нет поддержки табличных функций в основном запросе;
• не поддерживаются обновления LIVE-представлений для словарей, системных таблиц, обычных или материализованных представлений;
• не поддерживаются обновления, когда в исходном запросе указано несколько таблиц;
• нельзя использовать запросы, требующие полного пересчета данных или агрегирования с сохранением состояния, допустимо только объединять результаты прежних и новых (добавленных) данных;
• LIVE VIEW не работает для реплицируемых и распределенных таблиц, поскольку в этом случае добавление данных происходит на разных узлах.

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

Освойте администрирование и эксплуатацию ClickHouse для аналитики больших данных на специализированных курсах в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве:

• ClickHouse как основа DWH

Источники

1. https://clickhouse.com/docs/ru/sql-reference/statements/create/view
2. https://leftjoin.ru/all/materialized-view-in-clickhouse/
3. https://posthog.com/blog/clickhouse-vs-postgres
4. https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-1
5. https://altinity.com/blog/clickhouse-materialized-views-illuminated-part-2