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

Real-time аналитикаХранилище данныхCloud

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

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

A running ClickHouse Cloud service. If you don’t have one yet, complete the ClickHouse Cloud quick start first.

Вам также следует пройти следующие руководства из раздела «Быстрый старт», поскольку это руководство напрямую опирается на таблицу uk_price_paid и представленные в них концепции:

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

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

Зачем нужна проекция

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

Нет отдельной таблицы — проекция находится прямо внутри uk_price_paid
Прозрачная оптимизация запросов — вы обращаетесь к uk_price_paid как обычно; ClickHouse автоматически выбирает проекцию
Синхронизируется с мутациями — удаления и обновления, применённые к таблице, отражаются в проекции

Подробнее см. в справочной документации по проекциям.

Добавьте проекцию в таблицу

Определите проекцию для uk_price_paid, в которой town, date, price и type хранятся с сортировкой по (town, date):

ALTER TABLE uk_price_paid
    ADD PROJECTION uk_price_paid_by_town
    (
        SELECT town, date, price, type
        ORDER BY (town, date)
    );

Это регистрирует проекцию в метаданных таблицы, но не материализует её для существующих данных — только будущие вставки будут её заполнять.Убедитесь, что проекция присутствует в определении таблицы:

SHOW CREATE TABLE uk_price_paid;

В выводе должен быть блок PROJECTION uk_price_paid_by_town.

Материализуйте проекцию для существующих данных

Как и materialized view, недавно добавленная проекция применяется только к будущим вставкам. Чтобы заполнить её 30 миллионами строк, уже находящимися в таблице, нужно материализовать её явно:

ALTER TABLE uk_price_paid
    MATERIALIZE PROJECTION uk_price_paid_by_town;

Эта операция выполняется как фоновая мутация. Вы можете проверить её прогресс:

SELECT
    mutation_id,
    command,
    is_done
FROM system.mutations
WHERE table = 'uk_price_paid'
ORDER BY create_time DESC
LIMIT 5;

Когда is_done = 1, проекция полностью материализована. Также можно убедиться в этом, проверив system.projection_parts:

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

Выполните запрос к таблице и обратите внимание на автоматическое использование проекции

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

Проверьте статистику запроса — считывается значительно меньше строк, чем до появления проекции, поскольку ClickHouse автоматически выбрал чтение из проекции uk_price_paid_by_town вместо сканирования исходных данных.Убедиться, что проекция действительно использовалась, можно с помощью EXPLAIN:

EXPLAIN
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;

Ищите в выводе ReadFromMergeTree, где упоминается имя проекции. Если вы хотите явно сравнить производительность, можно отключить оптимизацию проекций для одного запроса:

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
SETTINGS optimize_use_projections = 0;

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

Сравнение проекций и materialized views

И проекции, и materialized views решают одну и ту же задачу — ускоряют чтение при альтернативных шаблонах доступа, — но предполагают разные компромиссы. Коротко говоря, проекции лучше подходят, когда нужен лишь другой порядок сортировки тех же данных; materialized views более гибки, когда требуется преобразовывать, агрегировать или направлять данные в другую схему. Подробное сравнение см. в разделе Materialized views versus projections.

Оцените дополнительный расход хранилища

Проекции хранят вторую копию выбранных столбцов в той же таблице, увеличивая использование диска. Выполните запрос к system.parts, чтобы увидеть общий размер uk_price_paid (который теперь включает данные проекций):

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

Вы также можете проверить хранилище для конкретных проекций:

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

Это тот же базовый компромисс, что и у materialized view: больше места на диске в обмен на более быстрое чтение. Проекция может быть меньше полной копии, поскольку включает только четыре выбранных столбца, а степень сжатия зависит от порядка сортировки.

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

В этом Быстром старте вы добавили проекцию в uk_price_paid, которая хранит данные, отсортированные по (town, date), что позволяет выполнять быстрые выборки по городу без создания отдельной таблицы. Вы узнали, что проекции прозрачно выбираются оптимизатором запросов, остаются синхронизированными с мутациями и обеспечивают более высокую производительность чтения в обмен на дополнительное место на диске. Далее ознакомьтесь со следующими Быстрыми стартами: Или подробнее изучите справочную документацию:

ClickHouse Academy — Master ClickHouse with expert-designed training for every skill level — Check out the ClickHouse academy for on-demand and live training

All quickstarts

Get started

Setup

Sample datasets

Migrate

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

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

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

Зачем нужна проекция

Добавьте проекцию в таблицу

Материализуйте проекцию для существующих данных

Выполните запрос к таблице и обратите внимание на автоматическое использование проекции

Сравнение проекций и materialized views

Оцените дополнительный расход хранилища

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

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

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

Зачем нужна проекция

Добавьте проекцию в таблицу

Материализуйте проекцию для существующих данных

Выполните запрос к таблице и обратите внимание на автоматическое использование проекции

Сравнение проекций и materialized views

Оцените дополнительный расход хранилища

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

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

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

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