ALTER TABLE ... PROJECTION

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

Обзор проекций

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

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

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

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

Более подробную техническую информацию о том, как проекции работают изнутри, см. на этой странице.

Использование проекций

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

Создание таблицы:

CREATE TABLE visits_order
(
   `user_id` UInt64,
   `user_name` String,
   `pages_visited` Nullable(Float64),
   `user_agent` String
)
ENGINE = MergeTree()
PRIMARY KEY user_agent

С помощью ALTER TABLE можно добавить проекцию в существующую таблицу:

ALTER TABLE visits_order ADD PROJECTION user_name_projection (
    SELECT *
    ORDER BY user_name
)

ALTER TABLE visits_order MATERIALIZE PROJECTION user_name_projection

Вставка данных:

INSERT INTO visits_order SELECT
    number,
    'test',
    1.5 * (number / 2),
    'Android'
FROM numbers(1, 100);

Проекция позволит нам быстро фильтровать по user_name, даже если в исходной таблице user_name не был определён как PRIMARY_KEY. Во время выполнения запроса ClickHouse определяет, что при использовании проекции будет обработано меньше данных, так как данные упорядочены по user_name.

SELECT
    *
FROM visits_order
WHERE user_name='test'
LIMIT 2

Чтобы убедиться, что запрос использует проекцию, можно проверить таблицу system.query_log. В поле projections указано имя использованной проекции или пустое значение, если проекция не использовалась:

SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'

Пример запроса с предварительной агрегацией

Создайте таблицу с проекцией projection_visits_by_user:

CREATE TABLE visits
(
   `user_id` UInt64,
   `user_name` String,
   `pages_visited` Nullable(Float64),
   `user_agent` String,
   PROJECTION projection_visits_by_user
   (
       SELECT
           user_agent,
           sum(pages_visited)
       GROUP BY user_id, user_agent
   )
)
ENGINE = MergeTree()
ORDER BY user_agent

Вставьте данные:

INSERT INTO visits SELECT
    number,
    'test',
    1.5 * (number / 2),
    'Android'
FROM numbers(1, 100);

INSERT INTO visits SELECT
    number,
    'test',
    1. * (number / 2),
   'IOS'
FROM numbers(100, 500);

Выполните первый запрос с GROUP BY, используя поле user_agent. Этот запрос не будет использовать проекцию, так как предварительная агрегация ему не соответствует.

SELECT
    user_agent,
    count(DISTINCT user_id)
FROM visits
GROUP BY user_agent

Чтобы использовать проекцию, можно выполнять запросы, выбирающие часть или все поля предварительной агрегации и GROUP BY:

SELECT
    user_agent
FROM visits
WHERE user_id > 50 AND user_id < 150
GROUP BY user_agent

SELECT
    user_agent,
    sum(pages_visited)
FROM visits
GROUP BY user_agent

Как уже упоминалось, вы можете просмотреть таблицу system.query_log, чтобы понять, использовалась ли проекция. Поле projections показывает имя использованной проекции. Оно будет пустым, если проекция не использовалась:

SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'

Создание и использование индексов-проекций

Создание индекса-проекции:

CREATE TABLE events
(
    `event_time` DateTime,
    `event_id` UInt64,
    `user_id` UInt64,
    `huge_string` String,
    PROJECTION order_by_user_id INDEX user_id TYPE basic
)
ENGINE = MergeTree()
ORDER BY (event_id);

Вставим несколько примеров данных:

INSERT INTO events SELECT * FROM generateRandom() LIMIT 100000;

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

SELECT
    count()
FROM events
WHERE _part_starting_offset + _part_offset IN (
    SELECT _part_starting_offset + _part_offset
    FROM events
    WHERE user_id = 42
)
SETTINGS enable_shared_storage_snapshot_in_query = 1

Пример проекции с предложением WHERE

Проекции могут включать предложение WHERE, чтобы хранить только подмножество строк. Это полезно, когда запросы часто используют известный предикат фильтрации — проекция материализует только подходящие строки, уменьшая объём хранимых данных и повышая производительность запросов. Создание таблицы и добавление отфильтрованной проекции:

CREATE TABLE events
(
    `event_type` String,
    `time` DateTime,
    `message` String
)
ENGINE = MergeTree()
ORDER BY time;

ALTER TABLE events ADD PROJECTION proj_pageview (
    SELECT event_type, time, message
    WHERE event_type = 'pageview'
    ORDER BY time
);

ALTER TABLE events MATERIALIZE PROJECTION proj_pageview;

Вставка данных:

INSERT INTO events VALUES
    ('pageview', '2024-01-01', 'homepage'),
    ('click', '2024-01-02', 'button'),
    ('pageview', '2024-01-03', 'about');

Когда WHERE-предложение запроса подразумевает WHERE-предложение проекции (то есть каждое условие в фильтре проекции также присутствует в фильтре запроса), оптимизатор может автоматически использовать проекцию, если сочтёт это целесообразным:

-- This query implies the projection's WHERE, so the projection may be used:
SELECT time, message FROM events WHERE event_type = 'pageview';

-- A stricter query also implies the projection's WHERE:
SELECT time, message FROM events WHERE event_type = 'pageview' AND time > '2024-01-01';

-- This query does NOT imply the projection, so the base table is scanned:
SELECT time, message FROM events WHERE event_type = 'click';

Проверка импликации консервативна — она использует точное сопоставление конъюнктов в канонической форме выражения. Из-за этого она может пропустить некоторые допустимые возможности оптимизации (например, импликации для диапазонов), но никогда не приведёт к неверным результатам.

Управление проекциями

Доступны следующие операции с проекциями:

ADD PROJECTION

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

-- Normal projection (supports WHERE)
ALTER TABLE [db.]name [ON CLUSTER cluster] ADD PROJECTION [IF NOT EXISTS] name ( SELECT <COLUMN LIST EXPR> [WHERE <expr>] [ORDER BY] ) [WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)]

-- Aggregate projection (supports WHERE)
ALTER TABLE [db.]name [ON CLUSTER cluster] ADD PROJECTION [IF NOT EXISTS] name ( SELECT <COLUMN LIST EXPR> [WHERE <expr>] [GROUP BY] ) [WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)]

Когда проекция задаёт предложение WHERE, материализуются только строки, соответствующие предикату. Оптимизатор может использовать такую проекцию, если WHERE в запросе логически следует из WHERE проекции и эта проекция выгодна для плана запроса. Это относится и к обычным, и к агрегатным проекциям.

Конструкция `WITH SETTINGS`

WITH SETTINGS определяет настройки уровня проекции, которые задают, как проекция хранит данные (например, index_granularity или index_granularity_bytes). Они напрямую соответствуют настройкам таблиц семейства MergeTree, но применяются только к этой проекции. Пример:

ALTER TABLE t
ADD PROJECTION p (
    SELECT x ORDER BY x
) WITH SETTINGS (
    index_granularity = 4096,
    index_granularity_bytes = 1048576
);

Настройки проекции переопределяют действующие настройки таблицы для этой проекции с учетом правил проверки (например, недопустимые или несовместимые переопределения будут отклонены).

MODIFY PROJECTION

Используйте приведённый ниже оператор, чтобы изменить конструкцию WITH SETTINGS существующей проекции без пересоздания её данных:

ALTER TABLE [db.]name [ON CLUSTER cluster] MODIFY PROJECTION [IF EXISTS] name ( SELECT <COLUMN LIST EXPR> [WHERE <expr>] [GROUP BY] [ORDER BY] ) WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)

Для индекса-проекции вместо SELECT-запроса повторно укажите объявление INDEX:

ALTER TABLE [db.]name [ON CLUSTER cluster] MODIFY PROJECTION [IF EXISTS] name INDEX <index_expr> TYPE <index_type> WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)

Оператор повторно указывает полное определение проекции, но от существующего определения может отличаться только конструкция WITH SETTINGS. Сам запрос проекции (или, для индекса-проекции, выражение и тип индекса) должен оставаться прежним, поскольку существующие части проекции содержат построенные на его основе данные; чтобы изменить его, используйте DROP PROJECTION, а затем ADD PROJECTION. Команда изменяет только метаданные таблицы и не переписывает данные: существующие части проекции сохраняют настройки, с которыми были записаны, тогда как части проекции, созданные последующими вставками и слияниями, используют новые настройки. MATERIALIZE PROJECTION тоже не применяет новые настройки к существующим частям: она строит проекцию только в тех частях, где та отсутствует или повреждена, а уже существующие части проекции оставляет без изменений. Чтобы перестроить существующие части с новыми настройками, выполните CLEAR PROJECTION, а затем MATERIALIZE PROJECTION либо выполните слияние частей, например с помощью OPTIMIZE TABLE ... FINAL. Пример:

ALTER TABLE t
MODIFY PROJECTION p (
    SELECT x ORDER BY x
) WITH SETTINGS (
    index_granularity = 128
);

DROP PROJECTION

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

ALTER TABLE [db.]name [ON CLUSTER cluster] DROP PROJECTION [IF EXISTS] name

MATERIALIZE PROJECTION

Используйте приведённый ниже оператор, чтобы построить проекцию name в тех частях, где она отсутствует или повреждена (при необходимости — только в партиции partition_name). Части, в которых проекция уже есть, остаются без изменений, даже если настройки проекции были изменены с помощью MODIFY PROJECTION после записи этих частей; чтобы перестроить их, сначала выполните CLEAR PROJECTION. Это реализовано как мутация.

ALTER TABLE [db.]table [ON CLUSTER cluster] MATERIALIZE PROJECTION [IF EXISTS] name [IN PARTITION partition_name]

CLEAR PROJECTION

Используйте приведённый ниже оператор, чтобы удалить с диска файлы проекции, не удаляя её описание. Это реализовано как мутация.

ALTER TABLE [db.]table [ON CLUSTER cluster] CLEAR PROJECTION [IF EXISTS] name [IN PARTITION partition_name]

Команды ADD, MODIFY, DROP и CLEAR считаются легковесными, поскольку изменяют только метаданные или удаляют файлы. Кроме того, эти команды реплицируются и синхронизируют метаданные проекций через ClickHouse Keeper или ZooKeeper.

Операции с проекциями поддерживаются только для таблиц с движком таблица семейства *MergeTree (включая реплицируемые варианты).

Управление поведением слияния проекций

Когда вы выполняете запрос, ClickHouse выбирает, читать ли данные из исходной таблицы или из одной из её проекций. Это решение принимается отдельно для каждой части таблицы. Как правило, ClickHouse старается читать как можно меньше данных и использует несколько приёмов, чтобы определить, из какой части лучше читать, например сэмплирование по первичному ключу части. В некоторых случаях у частей исходной таблицы нет соответствующих частей проекций. Это может происходить, например, потому что создание проекции для таблицы в SQL по умолчанию выполняется «лениво»: оно затрагивает только вновь вставленные данные, а существующие части не изменяются. Поскольку одна из проекций уже содержит заранее вычисленные агрегированные значения, ClickHouse старается читать из соответствующих частей проекций, чтобы избежать повторной агрегации во время выполнения запроса. Если у конкретной части нет соответствующей части проекции, запрос выполняется по исходной части. Но что происходит, если строки в исходной таблице нетривиально изменяются из-за нетривиальных фоновых слияний частей данных? Например, предположим, что таблица использует движок таблицы ReplacingMergeTree. Если во время слияния в нескольких входных частях обнаруживается одна и та же строка, сохраняется только самая новая версия строки (из части, вставленной последней), а все более старые версии отбрасываются. Аналогично, если таблица использует движок таблицы AggregatingMergeTree, операция слияния может сворачивать одинаковые строки во входных частях (на основе значений первичного ключа) в одну строку, чтобы обновить промежуточные состояния агрегации. До ClickHouse v24.8 части проекций либо незаметно рассинхронизировались с основными данными, либо некоторые операции, такие как обновления и удаления, вообще нельзя было выполнять, поскольку база данных автоматически генерировала исключение, если у таблицы были проекции. Начиная с v24.8, новый параметр уровня таблицы deduplicate_merge_projection_mode управляет поведением в случае, если вышеупомянутые нетривиальные фоновые операции слияния происходят в частях исходной таблицы. Мутации удаления — ещё один пример операций слияния частей, при которых удаляются строки в частях исходной таблицы. Начиная с v24.7, также есть параметр для управления поведением в отношении мутаций удаления, запускаемых легковесными удалениями: lightweight_mutation_projection_mode. Ниже приведены возможные значения для deduplicate_merge_projection_mode и lightweight_mutation_projection_mode:

throw (по умолчанию): генерируется исключение, что не позволяет частям проекций рассинхронизироваться.
drop: Затронутые части таблицы проекций удаляются. Для таких частей запросы будут выполняться по исходной части таблицы.
rebuild: Затронутая часть проекции перестраивается, чтобы оставаться согласованной с данными в части исходной таблицы.

Ограничения

Нельзя использовать столбец ALIAS в предложении ORDER BY проекции. Например:

CREATE TABLE t
(
    id UInt64,
    a UInt32,
    ab_sum UInt64 ALIAS a + 1,
    PROJECTION p (SELECT a ORDER BY ab_sum)
)
ENGINE = MergeTree ORDER BY id;
-- Завершается с ошибкой UNKNOWN_IDENTIFIER

Столбцы ALIAS физически не хранятся и вычисляются на лету во время выполнения запроса, поэтому они недоступны при записи части проекции, когда вычисляется выражение сортировки. Вместо этого используйте столбцы MATERIALIZED или встроите выражение напрямую:

-- использование MATERIALIZED столбца
CREATE TABLE t
(
    id UInt64,
    a UInt32,
    ab_sum UInt64 MATERIALIZED a + 1,
    PROJECTION p (SELECT a ORDER BY ab_sum)
)
ENGINE = MergeTree ORDER BY id;

-- использование встроенного выражения
CREATE TABLE t
(
    id UInt64,
    a UInt32,
    PROJECTION p (SELECT a ORDER BY a + 1)
)
ENGINE = MergeTree ORDER BY id;

Справочник по SQL

Типы данных

Движки

Функции

Форматы

Настройки

Системные таблицы

Озера данных

Обзор проекций

Использование проекций

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

Пример запроса с предварительной агрегацией

Создание и использование индексов-проекций

Пример проекции с предложением WHERE

Управление проекциями

ADD PROJECTION

Конструкция `WITH SETTINGS`

MODIFY PROJECTION

DROP PROJECTION

MATERIALIZE PROJECTION

CLEAR PROJECTION

Управление поведением слияния проекций

Ограничения

См. также

​Обзор проекций

​Использование проекций

​Пример фильтрации без использования первичных ключей

​Пример запроса с предварительной агрегацией

​Создание и использование индексов-проекций

​Пример проекции с предложением WHERE

​Управление проекциями

​ADD PROJECTION

​Конструкция WITH SETTINGS

​MODIFY PROJECTION

​DROP PROJECTION

​MATERIALIZE PROJECTION

​CLEAR PROJECTION

​Управление поведением слияния проекций

​Ограничения

​См. также

Обзор проекций

Использование проекций

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

Пример запроса с предварительной агрегацией

Создание и использование индексов-проекций

Пример проекции с предложением WHERE

Управление проекциями

ADD PROJECTION

Конструкция `WITH SETTINGS`

MODIFY PROJECTION

DROP PROJECTION

MATERIALIZE PROJECTION

CLEAR PROJECTION

Управление поведением слияния проекций

Ограничения

См. также