Инкрементные материализованные представления

Инкрементные материализованные представления (Materialized Views) позволяют пользователям перенести стоимость вычислений с времени запроса на время вставки, что приводит к более быстрым запросам SELECT.

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

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

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

Пример

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

Это довольно простой запрос в ClickHouse благодаря функции toStartOfDay:

Этот запрос уже быстрый благодаря ClickHouse, но можем ли мы сделать лучше?

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

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

Чтобы продемонстрировать наше материализованное представление, предположим, что наша таблица голосов пуста и еще не получила никаких данных. Наше материализованное представление выполняет указанный выше SELECT на данных, вставленных в votes, с результатами, отправленными в up_down_votes_per_day:

Ключевым здесь является CLAUSE TO, обозначающий, куда будут отправлены результаты, т.е. up_down_votes_per_day.

Теперь мы можем заново заполнить нашу таблицу голосов с помощью раннего вставки:

По завершении, мы можем подтвердить размер нашего up_down_votes_per_day - у нас должно быть 1 строка на день:

Мы тем самым эффективно снизили количество строк здесь с 238 миллионов (в votes) до 5000 за счет хранения результата нашего запроса. Ключевым здесь, однако, является то, что если новые голоса будут вставлены в таблицу votes, новые значения будут отправлены в up_down_votes_per_day для соответствующего дня, где они автоматически будут асинхронно объединены в фоновом режиме - сохраняя только одну строку на день. Таким образом, up_down_votes_per_day всегда будет как маленьким, так и актуальным.

Поскольку объединение строк происходит асинхронно, может быть больше одного голоса за день, когда пользователь выполняет запрос. Чтобы обеспечить объединение любых ожидающих строк во время запроса, у нас есть два варианта:

• Использовать модификатор FINAL в имени таблицы. Мы сделали это для запроса счётчика выше.
• Агрегировать по ключу упорядочивания, используемому в нашей финальной таблице, т.е. CreationDate и суммировать метрики. Обычно это более эффективно и гибко (таблицу можно использовать для других целей), но первый вариант может быть проще для некоторых запросов. Мы показываем оба ниже:

Это ускорило наш запрос с 0.133с до 0.004с – более чем 25-кратное улучшение!

к сведению

Важно: ORDER BY = GROUP BY

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

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

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

Предположим, мы хотим вычислить некоторые статистические данные для постов за каждый день: 99.9-й процентиль для Score и среднее значение CommentCount. Запрос для вычисления этого может выглядеть так:

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

В целях примера, и чтобы избежать загрузки данных постов из S3, мы создадим дубликат таблицы posts_null с той же схемой, что и posts. Однако эта таблица не будет хранить никаких данных и будет просто использоваться материализованным представлением, когда строки вставляются. Чтобы предотвратить хранение данных, мы можем использовать тип движка таблицы Null.

Движок таблицы Null является мощной оптимизацией - думайте об этом как о /dev/null. Наше материализованное представление будет вычислять и хранить наши сводные статистики, когда в таблицу posts_null будут поступать строки при вставке - это просто триггер. Однако сырье не будет храниться. Хотя в нашем случае мы, вероятно, все еще хотим хранить оригинальные посты, этот подход можно использовать для вычисления агрегатов, избегая затрат на хранение сырьевых данных.

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

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

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

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

Хотя ранее SummingMergeTree был достаточен для хранения количества, нам требуется более продвинутый тип движка для других функций: AggregatingMergeTree. Чтобы обеспечить ClickHouse, что будут храниться агрегатные состояния, мы определяем Score_quantiles и AvgCommentCount как тип AggregateFunction, указывая функцию-источник частичных состояний и тип их исходных столбцов. Как и в случае с SummingMergeTree, строки с одинаковым значением ключа ORDER BY будут объединены (Day в приведенном примере).

Чтобы заполнить нашу post_stats_per_day через наше материализованное представление, мы можем просто вставить все строки из posts в posts_null:

На практике вы, вероятно, прикрепите материализованное представление к таблице posts. Мы использовали posts_null здесь для демонстрации нулевой таблицы.

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

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

Использование исходной таблицы в фильтрах и соединениях в материализованных представлениях

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

Пример сценария

Рассмотрим следующую настройку:

Объяснение

В приведенном выше примере у нас есть два материализованных представления mvw1 и mvw2, которые выполняют похожие операции, но с небольшим различием в том, как они ссылаются на исходную таблицу t0.

В mvw1 таблица t0 ссылается непосредственно внутри подзапроса (SELECT * FROM t0) с правой стороны от JOIN. Когда данные вставляются в t0, запрос к материализованному представлению выполняется с вставленным блоком данных, заменяющим t0. Это означает, что операция соединения выполняется только над вновь вставленными строками, а не над всей таблицей.

Во втором случае с присоединением к vt0 представление считывает все данные из t0. Это гарантирует, что операция соединения учитывает все строки в t0, а не только вновь вставленный блок.

Почему это работает именно так

Ключевое различие заключается в том, как ClickHouse обрабатывает исходную таблицу в запросе к материализованному представлению. Когда материализованное представление срабатывает по вставке, исходная таблица (в данном случае t0) заменяется вставленным блоком данных. Это поведение может быть использовано для оптимизации запросов, но также требует внимательного рассмотрения, чтобы избежать неожиданных результатов.

Сценарии использования и предостережения

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

В этом примере набор для построения из подзапроса IN (SELECT id FROM t0) содержит только вновь вставленные строки, что может помочь отфильтровать t1 по этому критерию.

Другие применения

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

Фильтрация и преобразование

В некоторых ситуациях мы можем пожелать вставить только подмножество строк и столбцов при вставке. В этом случае наша таблица posts_null может получать вставки, с помощью запроса SELECT, который фильтрует строки перед вставкой в таблицу posts. Например, предположим, что мы хотели бы преобразовать столбец Tags в нашей таблице posts. Это содержит список имён тегов, разделённых символами "pipe". Преобразовав их в массив, мы сможем легче агрегировать по индивидуальным значениям тегов.

Мы могли бы выполнить это преобразование при выполнении INSERT INTO SELECT. Материализованное представление позволяет нам инкапсулировать эту логику в DDL ClickHouse и упростить наш INSERT, применив преобразование к любым новым строкам.

Наше материализованное представление для этого преобразования показано ниже:

Таблица соответствий

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

Ключ упорядочивания здесь оптимизирует таблицу для запросов, фильтрующих по PostId.

Предположим, пользователь хочет отфильтровать по конкретному UserId и вычислить их средний Score:

Хотя это быстро (данные малы для ClickHouse), мы можем сказать, что это требует полного сканирования таблицы по количеству обработанных строк - 90.38 миллиона. Для больших наборов данных мы можем использовать материализованное представление для поиска значений нашего ключа упорядочивания PostId для фильтрации столбца UserId. Эти значения затем могут быть использованы для выполнения эффективного поиска.

В этом примере наше материализованное представление может быть очень простым, выбирая только PostId и UserId из comments при вставке. Эти результаты затем отправляются в таблицу comments_posts_users, которая упорядочена по UserId. Мы создаем нулевую версию таблицы Comments ниже и используем это для заполнения нашего представления и таблицы comments_posts_users:

Теперь мы можем использовать это представление в подзапросе для ускорения нашего предыдущего запроса:

Цепочка

Материализованные представления могут быть связаны друг с другом, что позволяет устанавливать сложные рабочие процессы. Для практического примера мы рекомендуем этот блог.