Проекции

Прогнозы (Projections) хранят данные в формате, оптимизирующем выполнение запросов, эта функция полезна для:

• Выполнения запросов по колонке, которая не является частью первичного ключа.
• Предагрегации колонок, что позволит уменьшить как вычисления, так и ввод-вывод (IO).

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

Использование диска

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

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

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

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

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

Управление прогнозами

Следующие операции с прогнозами доступны:

ADD PROJECTION

ALTER TABLE [db.]name [ON CLUSTER cluster] ADD PROJECTION [IF NOT EXISTS] name ( SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY] ) - Добавляет описание прогноза в метаданные таблиц.

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 (включая реплицированные варианты).