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

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

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

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

Для 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;

Теперь создайте 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 не синхронизируются с удалениями и обновлениями. Если вам нужна такая синхронизация, рассмотрите возможность использовать проекции.

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;

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

Теперь выполните запрос с фильтрацией по 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.

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) может сжиматься иначе, чем в исходной таблице.