Проекции

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

• Выполнения запросов по столбцу, который не является частью первичного ключа
• Предварительной агрегации столбцов, что снижает как вычислительные затраты, так и нагрузку на операции ввода-вывода (I/O)

Вы можете определить одну или несколько проекций для таблицы, и во время анализа запроса 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>'

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

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

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>'

Обычная проекция с полем _part_offset

Создание таблицы с обычной проекцией, использующей поле _part_offset:

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

Добавление тестовых данных:

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

Использование _part_offset в качестве вторичного индекса

Поле _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

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

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

ДОБАВИТЬ ПРОЕКЦИЮ

ALTER TABLE [db.]name [ON CLUSTER cluster] ADD PROJECTION [IF NOT EXISTS] name ( SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY] ) [WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)] — добавляет в метаданные таблицы описание проекции.

Предложение 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
);

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

DROP PROJECTION

ALTER TABLE [db.]name [ON CLUSTER cluster] DROP PROJECTION [IF EXISTS] name — удаляет из метаданных таблицы описание проекции и соответствующие файлы проекции на диске. Реализовано как мутация.

MATERIALIZE PROJECTION

ALTER TABLE [db.]table [ON CLUSTER cluster] MATERIALIZE PROJECTION [IF EXISTS] name [IN PARTITION partition_name] — запрос, который перестраивает проекцию name в партиции partition_name. Реализован как мутация.

CLEAR PROJECTION

ALTER TABLE [db.]table [ON CLUSTER cluster] CLEAR PROJECTION [IF EXISTS] name [IN PARTITION partition_name] — удаляет файлы проекции с диска, не удаляя её описания. Эта операция реализована как мутация.

Команды ADD, DROP и CLEAR являются «лёгкими» в том смысле, что они только изменяют метаданные или удаляют файлы.

Также они реплицируются, синхронизируя метаданные проекций через ClickHouse Keeper или ZooKeeper.

Примечание

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