Создайте свое первое materialized view

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

Все руководства по быстрому старту

Real-time аналитикаХранилище данныхОбсервабилитиAI/MLCloudOss

Предварительные требования

To successfully follow this guide, you’ll need the following:

• A running ClickHouse Cloud service. If you don’t have one yet, complete the Create your first Cloud service quickstart first.

Вам также следует пройти руководство по быстрому старту Создайте свою первую таблицу MergeTree, поскольку это руководство напрямую использует таблицу uk_price_paid, созданную в нем.

Что вы создадите

В руководстве по быстрому старту по MergeTree вы увидели, что запросы к uk_price_paid по town или county требуют полного сканирования таблицы, поскольку она отсортирована по (postcode, addr1, addr2). В этом руководстве по быстрому старту вы решите эту проблему, создав materialized view, которое хранит те же данные, но в сортировке по (town, date), что позволяет быстро выполнять поиск по городу без изменения исходной таблицы. В итоге вы поймёте, как materialized view работают как триггеры вставки, как выполнять дозагрузку существующих данных и каков компромисс по дисковому пространству при хранении данных в двух экземплярах.

1. Зачем нужна materialized view

   Ваша таблица uk_price_paid отсортирована по (postcode, addr1, addr2). Это означает, что ClickHouse может пропускать большие блоки данных, когда вы фильтруете по postcode, addr1 или addr2, но запросы с фильтрацией по town должны сканировать каждую строку — все 30 миллионов.

   Можно было бы создать вторую таблицу с другим ORDER BY, но тогда вам пришлось бы не забывать выполнять вставку в обе таблицы каждый раз, когда поступают новые данные. Materialized view автоматизирует этот процесс: она отслеживает вставки в исходную таблицу, преобразует строки и автоматически записывает их в целевую таблицу.

   Думайте о materialized view как об insert trigger: каждый раз, когда в исходную таблицу вставляются строки, запрос SELECT этой MV выполняется для нового блока строк, а результат вставляется в целевую таблицу.

2. Создайте целевую таблицу

   Для materialized view нужно место для сохранения результата. Это обычная таблица MergeTree — вы полностью управляете её схемой, ORDER BY и PARTITION BY.

   Создайте таблицу с сортировкой по (town, date) и только с теми столбцами, которые нужны для запросов по городам:

   CREATE TABLE uk_price_paid_by_town
   (
       town       LowCardinality(String),
       date       Date,
       price      UInt32,
       type       Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0)
   )
   ENGINE = MergeTree
   PARTITION BY toYYYYMM(date)
   ORDER BY (town, date);
   

   В этой таблице нет ничего особенного — это стандартная таблица MergeTree. Materialized view, которую вы создадите далее, будет просто направлять в неё данные.

   Проверьте, что таблица была создана:

   SHOW CREATE TABLE uk_price_paid_by_town;
   
3. Создайте materialized view

   Теперь создайте materialized view, которая связывает исходную таблицу (uk_price_paid) с целевой таблицей (uk_price_paid_by_town):

   CREATE MATERIALIZED VIEW uk_price_paid_by_town_mv
   TO uk_price_paid_by_town
   AS SELECT
       town,
       date,
       price,
       type
   FROM uk_price_paid;
   

   Конструкция TO uk_price_paid_by_town указывает ClickHouse записывать результат SELECT в целевую таблицу. Теперь при каждой вставке строк в uk_price_paid это MV срабатывает и вставляет преобразованные строки в uk_price_paid_by_town.

   Но есть важный нюанс: materialized views срабатывают только при вставках. Если вы удаляете или обновляете строки в исходной таблице, целевая таблица об этом не узнает — MVs не синхронизируются с удалениями и обновлениями. Если вам нужна такая синхронизация, рассмотрите возможность использовать проекции.

4. Дозагрузка существующих данных

   materialized view обрабатывает только будущие вставки. 30 миллионов строк в uk_price_paid были вставлены до создания MV, поэтому целевая таблица сейчас пуста.

   Выполните дозагрузку вручную:

   INSERT INTO uk_price_paid_by_town
   SELECT
       town,
       date,
       price,
       type
   FROM uk_price_paid;
   

   Данные вставляются напрямую в целевую таблицу — на этом этапе MV не используется. После завершения убедитесь, что количество строк совпадает:

   SELECT
       'uk_price_paid' AS table,
       count() AS rows
   FROM uk_price_paid
   UNION ALL
   SELECT
       'uk_price_paid_by_town' AS table,
       count() AS rows
   FROM uk_price_paid_by_town;
   

   В обеих таблицах должно быть одинаковое количество строк.

5. Выполните запрос к целевой таблице materialized view

   Теперь выполните запрос с фильтрацией по town для целевой таблицы и сравните его с запросом непосредственно к исходной таблице.

   Сначала выполните запрос к исходной таблице:

   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC;
   

   Проверьте статистику запроса — считываются все 30 миллионов строк, потому что town отсутствует в ORDER BY исходной таблицы.

   Теперь выполните тот же запрос к целевой таблице materialized view:

   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid_by_town
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC;
   

   Снова проверьте статистику запроса — считывается значительно меньше строк, потому что целевая таблица отсортирована по (town, date), и ClickHouse может пропустить все данные, не соответствующие LONDON.

   Выполните SHOW TABLES, чтобы увидеть, что было создано:

   SHOW TABLES;
   

   Вы увидите и uk_price_paid_by_town (целевую таблицу), и uk_price_paid_by_town_mv (представление). Поскольку вы использовали CREATE MATERIALIZED VIEW ... TO, вы сами задаёте имя целевой таблицы. Если опустить предложение TO, ClickHouse создаст целевую таблицу с неявным именем (.inner.xxx), с которой сложнее работать напрямую. Поэтому materialized view рекомендуется создавать с предложением TO.

6. Обратите внимание: данные хранятся в двух местах

   Materialized views обеспечивают более быстрое чтение за счёт дополнительного места на диске. Выполните запрос к system.parts, чтобы увидеть, сколько места занимает каждая таблица:

   SELECT
       table,
       count() AS parts,
       sum(rows) AS total_rows,
       formatReadableSize(sum(bytes_on_disk)) AS compressed_size
   FROM system.parts
   WHERE table IN ('uk_price_paid', 'uk_price_paid_by_town')
     AND active = true
   GROUP BY table;
   

   Данные физически хранятся дважды: один раз в uk_price_paid с сортировкой по (postcode, addr1, addr2) и один раз в uk_price_paid_by_town с сортировкой по (town, date). В этом и заключается основной компромисс: вы расходуете больше места на диске в обмен на более быстрое чтение для разных сценариев доступа.

   Целевая таблица может занимать меньше места на диске, потому что содержит меньше столбцов, а порядок сортировки (town, date) может сжиматься иначе, чем в исходной таблице.

Следующие шаги

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

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

• Создайте свою первую проекцию

Или подробнее изучите справочную документацию:

• Справочник по materialized view
• Incremental materialized views
• Проекции

Check out the ClickHouse academy for on-demand and live training

Все руководства по быстрому старту