Esta página aborda o que são projeções, como usá-las e as várias opções para manipulá-las.
Visão geral das projeções
- 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.
Uso de discoAs projeções criam internamente uma nova tabela oculta, o que significa que será necessário mais E/S e mais espaço em disco.
Por exemplo, se a projeção tiver uma chave primária diferente, todos os dados da tabela original serão duplicados.
Exemplo de filtragem sem usar chaves primárias
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, 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
INSERT INTO visits_order SELECT
number,
'test',
1.5 * (number / 2),
'Android'
FROM numbers(1, 100);
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
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>'
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 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
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 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 e usando índices 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);
INSERT INTO events SELECT * FROM generateRandom() LIMIT 100000;
_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
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 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
);
ALTER TABLE [db.]name [ON CLUSTER cluster] DROP PROJECTION [IF EXISTS] name
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]
ALTER TABLE [db.]table [ON CLUSTER cluster] CLEAR PROJECTION [IF EXISTS] name [IN PARTITION partition_name]
ADD, 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.
A manipulação de projeções tem suporte apenas em tabelas com o motor *MergeTree (incluindo variantes replicadas). Controlando o comportamento da mesclagem de projeções
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
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;
