ALTER TABLE ... PROJECTION

Esta página aborda o que são projeções, como usá-las e as várias opções para manipulá-las.

As projeções armazenam dados em um formato que otimiza a execução de consultas. Esse recurso é útil para:

• Executar consultas em uma coluna que não faz parte da chave primária
• Pré-agregar colunas, reduzindo tanto o processamento quanto a E/S

Você pode definir uma ou mais projeções para uma tabela e, durante a análise da consulta, o ClickHouse selecionará a projeção com a menor quantidade de dados a serem examinados, sem modificar a consulta fornecida pelo usuário.

Você pode ver mais detalhes técnicos sobre como as projeções funcionam internamente nesta página.

Criando a tabela:

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

Com ALTER TABLE, podemos adicionar a projeção a uma tabela existente:

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

ALTER TABLE visits_order MATERIALIZE PROJECTION user_name_projection

Inserindo os dados:

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

A projeção permitirá filtrar por user_name rapidamente, mesmo que, na tabela original, user_name não tenha sido definido como PRIMARY_KEY. No momento da consulta, o ClickHouse determina que menos dados serão processados se a projeção for usada, pois os dados estão ordenados por user_name.

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

Para verificar se uma consulta está usando a projeção, podemos consultar a tabela system.query_log. No campo projections, temos o nome da projeção usada, ou ele fica vazio se nenhuma tiver sido usada:

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

Crie a tabela com a projeção 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

Insira os dados:

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

Execute uma primeira consulta com GROUP BY usando o campo user_agent. Esta consulta não usará a projeção definida, pois a pré-agregação não é compatível.

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

Para usar a projeção, você pode executar consultas que selecionem parte ou a totalidade dos campos de pré-agregação e de 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

Como mencionado anteriormente, você pode consultar a tabela system.query_log para verificar se uma projeção foi usada. O campo projections mostra o nome da projeção usada. Ele ficará vazio se nenhuma projeção tiver sido usada:

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

Criando um índice de projeção:

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

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

Inserindo alguns dados de exemplo:

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

O campo _part_offset mantém seu valor mesmo após mesclagens e mutações, o que o torna valioso para indexação secundária. Podemos aproveitar isso em consultas:

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

As projeções podem incluir uma cláusula WHERE para armazenar apenas um subconjunto de linhas. Isso é útil quando as consultas filtram com frequência por um predicado conhecido — a projeção materializa apenas as linhas correspondentes, reduzindo o uso de armazenamento e melhorando o desempenho das consultas.

Criando uma tabela e adicionando uma projeção filtrada:

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;

Inserindo dados:

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

Quando a cláusula WHERE de uma consulta implica a cláusula WHERE da projeção (ou seja, toda condição no filtro da projeção também está presente no filtro da consulta), o otimizador pode usar a projeção automaticamente quando determinar que isso é vantajoso:

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

A verificação de implicação é conservadora — usa correspondência exata de conjunções na forma canônica da expressão. Ela pode deixar de identificar algumas oportunidades válidas de otimização (por exemplo, implicações de intervalo), mas nunca produzirá resultados incorretos.

As seguintes operações com projeções estão disponíveis:

Use a instrução abaixo para adicionar uma descrição de projeção aos metadados de uma tabela:

-- 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, ...)]

WITH SETTINGS define configurações da projeção, que personalizam como a projeção armazena os dados (por exemplo, index_granularity ou index_granularity_bytes). Elas correspondem diretamente às configurações de tabela do MergeTree, mas se aplicam somente a esta projeção.

Exemplo:

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

As configurações da projeção prevalecem sobre as configurações efetivas da tabela nessa projeção, sujeitas às regras de validação (por exemplo, substituições inválidas ou incompatíveis serão rejeitadas).

Use a instrução abaixo para alterar a cláusula WITH SETTINGS de uma projeção existente sem recriar seus dados:

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, ...)

Para um índice de projeção, especifique novamente a declaração INDEX em vez da consulta SELECT:

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, ...)

A instrução repete a definição completa da projeção, mas somente a cláusula WITH SETTINGS pode ser diferente da definição existente. A consulta da projeção em si (ou, no caso de um índice de projeção, a expressão e o tipo do índice) deve permanecer a mesma, pois as partes de projeção existentes armazenam dados gerados a partir dela. Para alterá-la, use DROP PROJECTION seguido de ADD PROJECTION.

O comando altera apenas os metadados da tabela e não reescreve dados: as partes de projeção existentes mantêm as configurações com as quais foram gravadas, enquanto as partes de projeção gravadas por inserts e mesclagens futuras usam as novas configurações. Para recriar as partes existentes com as novas configurações, execute MATERIALIZE PROJECTION.

Exemplo:

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

Use a instrução abaixo para remover a descrição de uma projeção dos metadados de uma tabela e excluir os arquivos da projeção do disco. Isso é implementado como uma mutação.

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

Use a instrução abaixo para reconstruir a projeção name na partição partition_name. Isso é implementado como uma mutação.

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

Use a instrução abaixo para excluir os arquivos de projeção do disco sem remover a descrição. Isso é implementado por meio de uma mutação.

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

Os comandos ADD, MODIFY, DROP e CLEAR são leves, no sentido de que apenas alteram metadados ou removem arquivos. Além disso, esses comandos são replicados e sincronizam os metadados de projeção por meio do ClickHouse Keeper ou do ZooKeeper.

Quando você executa uma consulta, o ClickHouse escolhe entre ler da tabela original ou de uma de suas projeções. A decisão de ler da tabela original ou de uma de suas projeções é tomada individualmente para cada parte da tabela. Em geral, o ClickHouse procura ler o mínimo possível de dados e emprega alguns recursos para identificar a melhor parte a ser lida, por exemplo, amostrando a chave primária de uma parte. Em alguns casos, partes da tabela de origem não têm partes de projeção correspondentes. Isso pode acontecer, por exemplo, porque a criação de uma projeção para uma tabela em SQL é “lazy” por padrão: ela afeta apenas os dados inseridos posteriormente, mas mantém as partes existentes inalteradas.

Como uma das projeções já contém os valores agregados pré-computados, o ClickHouse tenta ler das partes de projeção correspondentes para evitar agregar novamente em tempo de execução da consulta. Se uma parte específica não tiver a parte de projeção correspondente, a execução da consulta recorre à parte original.

Mas o que acontece se as linhas da tabela original mudarem de forma não trivial devido a mesclagens em segundo plano não triviais de partes de dados? Por exemplo, suponha que a tabela seja armazenada usando o mecanismo de tabela ReplacingMergeTree. Se a mesma linha for detectada em múltiplas partes de entrada durante a mesclagem, apenas a versão mais recente da linha (da parte inserida mais recentemente) será mantida, enquanto todas as versões mais antigas serão descartadas.

Da mesma forma, se a tabela for armazenada usando o mecanismo de tabela AggregatingMergeTree, a operação de mesclagem pode consolidar linhas iguais nas partes de entrada (com base nos valores da chave primária) em uma única linha para atualizar estados de agregação parciais.

Antes do ClickHouse v24.8, as partes de projeção ou ficavam silenciosamente dessincronizadas em relação aos dados principais, ou determinadas operações, como atualizações e exclusões, simplesmente não podiam ser executadas, já que o banco de dados lançava automaticamente uma exceção se a tabela tivesse projeções.

Desde a v24.8, uma nova configuração no nível da tabela deduplicate_merge_projection_mode controla o comportamento caso as operações de mesclagem em segundo plano não triviais mencionadas acima ocorram em partes da tabela original.

Mutações de exclusão são outro exemplo de operações de mesclagem de partes que removem linhas nas partes da tabela original. Desde a v24.7, também temos uma configuração para controlar o comportamento em relação às mutações de exclusão acionadas por exclusões leves: lightweight_mutation_projection_mode.

Abaixo estão os valores possíveis para deduplicate_merge_projection_mode e lightweight_mutation_projection_mode:

• throw (padrão): uma exceção é lançada, impedindo que as partes de projeção fiquem dessincronizadas.
• drop: As partes afetadas da tabela de projeção são descartadas. As consultas recorrerão à parte da tabela original nos casos em que as partes de projeção forem afetadas.
• rebuild: A parte de projeção afetada é recriada para permanecer consistente com os dados da parte da tabela original.

Não é possível usar uma coluna ALIAS na cláusula ORDER BY de uma projeção. Por exemplo:

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;
-- Falha com UNKNOWN_IDENTIFIER

As colunas ALIAS não são armazenadas fisicamente e são calculadas em tempo de consulta, portanto não ficam disponíveis durante o caminho de gravação da parte de projeção, quando a expressão de ordenação é avaliada.

Em vez disso, use colunas MATERIALIZED ou insira a expressão diretamente:

-- usando coluna 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;

-- usando uma expressão inline
CREATE TABLE t
(
    id UInt64,
    a UInt32,
    PROJECTION p (SELECT a ORDER BY a + 1)
)
ENGINE = MergeTree ORDER BY id;

• "Controle de projeções durante mesclagens" (post do blog)
• "Projeções" (guia)
• "Visões materializadas versus projeções"