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

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

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

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

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

Пример

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

Это reasonably simple query в ClickHouse благодаря функции toStartOfDay:

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

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

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

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

Клаузула 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 здесь, чтобы продемонстрировать таблицу null.

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

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

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

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

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

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

Объяснение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Таблица справочников

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

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

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

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

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

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

Цепочка

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