ApsaraDB for ClickHouse:Motores de tabela

MergeTree

O MergeTree é o motor base para cargas de trabalho de inserção com alta demanda. Os dados são inseridos em partes e mesclados em segundo plano com base na ordem de classificação definida por ORDER BY.

Exemplo: MergeTree com particionamento

1. Crie uma tabela particionada por create_time e ordenada por (id, create_time).

   CREATE TABLE test_tbl ON CLUSTER default (
     id UInt16,
     create_time Date,
     comment Nullable(String)
   ) ENGINE = MergeTree()
      PARTITION BY create_time
      ORDER BY (id, create_time)
      PRIMARY KEY (id, create_time)
      SETTINGS index_granularity=8192;

2. Insira linhas com chaves primárias duplicadas.

   INSERT INTO test_tbl VALUES (1, '2019-12-13', null);
   INSERT INTO test_tbl VALUES (1, '2019-12-13', null);
   INSERT INTO test_tbl VALUES (2, '2019-12-14', null);
   INSERT INTO test_tbl VALUES (3, '2019-12-15', null);
   INSERT INTO test_tbl VALUES (3, '2019-12-15', null);

3. Consulte a tabela.

   SELECT * FROM test_tbl;

   Resultado — todas as cinco linhas retornadas, incluindo duplicatas:

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

4. Force a compactação.

   OPTIMIZE TABLE test_tbl FINAL;

5. Consulte novamente.

   SELECT * FROM test_tbl;

   Resultado — as duplicatas ainda estão presentes. O MergeTree não as remove.

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

Para mais informações, consulte MergeTree.

ReplacingMergeTree

O ReplacingMergeTree remove linhas duplicadas com a mesma chave primária durante a compactação em segundo plano. Utilize este motor quando a deduplicação eventual for aceitável, pois ele não garante unicidade no momento da consulta.

Limitações

• Em um cluster distribuído, linhas com a mesma chave primária podem ser armazenadas em shards diferentes. A deduplicação ocorre apenas dentro de um único shard, e não entre shards.

• Antes da conclusão da compactação, algumas duplicatas ainda podem estar presentes. A compactação é executada em segundo plano em intervalos imprevisíveis.

• Em grandes conjuntos de dados, a execução manual de OPTIMIZE ... FINAL pode levar muito tempo, tornando a deduplicação em tempo real impraticável.

Exemplo: Deduplicação com ReplacingMergeTree

1. Crie uma tabela.

   CREATE TABLE test_tbl_replacing (
     id UInt16,
     create_time Date,
     comment Nullable(String)
   ) ENGINE = ReplacingMergeTree()
      PARTITION BY create_time
      ORDER BY (id, create_time)
      PRIMARY KEY (id, create_time)
      SETTINGS index_granularity=8192;

2. Insira linhas com chaves primárias duplicadas.

   INSERT INTO test_tbl_replacing VALUES (1, '2019-12-13', null);
   INSERT INTO test_tbl_replacing VALUES (1, '2019-12-13', null);
   INSERT INTO test_tbl_replacing VALUES (2, '2019-12-14', null);
   INSERT INTO test_tbl_replacing VALUES (3, '2019-12-15', null);
   INSERT INTO test_tbl_replacing VALUES (3, '2019-12-15', null);

3. Consulte antes da compactação — as duplicatas ainda estão presentes.

   SELECT * FROM test_tbl_replacing;

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

4. Force a compactação.

   OPTIMIZE TABLE test_tbl_replacing FINAL;

5. Consulte novamente — duplicatas removidas.

   SELECT * FROM test_tbl_replacing;

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

CollapsingMergeTree

O CollapsingMergeTree rastreia alterações de estado das linhas usando uma coluna Sign em vez de atualizar as linhas diretamente, já que atualizações são custosas no modelo de armazenamento append-only do ClickHouse. Cada linha é marcada como uma linha de estado (Sign = 1) ou uma linha de cancelamento (Sign = -1). Durante a compactação, pares de linhas de estado e de cancelamento com a mesma chave primária são colapsados e removidos.

Como funciona o colapso

Para atualizar o estado de uma linha:

1. Insira uma linha de cancelamento com Sign = -1 e os mesmos valores de chave primária da linha que você deseja substituir.

2. Insira uma nova linha de estado com Sign = 1 e os valores atualizados.

Para excluir o estado de uma linha: insira uma linha de cancelamento que corresponda à linha de estado original em todas as colunas, exceto Sign.

Observações de uso

• Se as linhas de estado e de cancelamento forem inseridas fora de ordem — por exemplo, se uma linha de cancelamento for inserida e sua linha de estado correspondente chegar separadamente — elas podem não ser colapsadas corretamente. Considere usar o VersionedCollapsingMergeTree para lidar com inserções fora de ordem.

• O colapso ocorre apenas dentro de um único shard. Em um cluster distribuído, linhas com a mesma chave primária em nós diferentes não são colapsadas.

• Antes da conclusão da compactação, as linhas de estado e de cancelamento coexistem. Ao executar consultas agregadas antes da compactação, ajuste seu SQL:

  • Substitua COUNT() por SUM(Sign)

  • Substitua SUM(col) por SUM(col * Sign)

Exemplo: Atualizações de estado com CollapsingMergeTree

1. Crie uma tabela com uma coluna Sign.

   CREATE TABLE test_tbl_collapsing
   (
       UserID UInt64,
       PageViews UInt8,
       Duration UInt8,
       Sign Int8
   )
   ENGINE = CollapsingMergeTree(Sign)
   ORDER BY UserID;

2. Insira a linha de estado inicial.

   INSERT INTO test_tbl_collapsing VALUES (4324182021466249494, 5, 146, 1);

3. Atualize o estado: insira uma linha de cancelamento para o estado antigo e uma nova linha de estado.

   Nota

   Insira a linha de cancelamento e a nova linha de estado no mesmo lote. Se você inserir a linha de cancelamento e depois a linha de estado em operações separadas, elas podem chegar fora de ordem. Nesse caso, mesmo que a compactação seja forçada, as linhas com a mesma chave primária não poderão ser colapsadas ou excluídas.

   INSERT INTO test_tbl_collapsing VALUES
     (4324182021466249494, 5, 146, -1),  -- cancel old state
     (4324182021466249494, 6, 185, 1);   -- new state

4. Consulte antes da compactação — todas as três linhas estão presentes.

   SELECT * FROM test_tbl_collapsing;

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign──┐
   │ 4324182021466249494 │    5      │    146   │   1   │
   │ 4324182021466249494 │    5      │    146   │  -1   │
   │ 4324182021466249494 │    6      │    185   │   1   │
   └─────────────────────┴───────────┴──────────┴───────┘

   Para obter resultados agregados corretos antes da compactação, use expressões que considerem a coluna Sign:

   SELECT
       UserID,
       SUM(PageViews * Sign) AS PageViews,
       SUM(Duration * Sign) AS Duration
   FROM test_tbl_collapsing
   GROUP BY UserID
   HAVING SUM(Sign) > 0;

   ┌────────UserID───────┬─PageViews─┬─Duration──┐
   │ 4324182021466249494 │    6      │    185    │
   └─────────────────────┴───────────┴───────────┘

5. Force a compactação.

   OPTIMIZE TABLE test_tbl_collapsing FINAL;

6. Consulte novamente — apenas a linha de estado mais recente permanece.

   SELECT * FROM test_tbl_collapsing;

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign──┐
   │ 4324182021466249494 │    6      │    185   │   1   │
   └─────────────────────┴───────────┴──────────┴───────┘

VersionedCollapsingMergeTree

O VersionedCollapsingMergeTree estende o CollapsingMergeTree com uma coluna Version que permite ao motor corresponder corretamente as linhas de estado e de cancelamento, independentemente da ordem de inserção. Durante a compactação, linhas com a mesma chave primária, o mesmo valor de Version e valores opostos de Sign são colapsadas e removidas.

Utilize este motor quando as linhas puderem chegar fora de ordem — por exemplo, em pipelines orientados a eventos onde eventos de cancelamento podem ser processados antes dos eventos de estado aos quais se referem.

Assim como no CollapsingMergeTree, ajuste as consultas agregadas para usar SUM(Sign) em vez de COUNT() e SUM(col * Sign) em vez de SUM(col).

Exemplo: VersionedCollapsingMergeTree com inserções fora de ordem

1. Crie uma tabela com as colunas Sign e Version.

   CREATE TABLE test_tbl_Versioned
   (
       UserID UInt64,
       PageViews UInt8,
       Duration UInt8,
       Sign Int8,
       Version UInt8
   )
   ENGINE = VersionedCollapsingMergeTree(Sign, Version)
   ORDER BY UserID;

2. Insira primeiro uma linha de cancelamento (fora de ordem).

   INSERT INTO test_tbl_Versioned VALUES (4324182021466249494, 5, 146, -1, 1);

3. Insira a linha de estado correspondente e uma nova linha de estado.

   INSERT INTO test_tbl_Versioned VALUES
     (4324182021466249494, 5, 146, 1, 1),   -- state row matching the cancel above
     (4324182021466249494, 6, 185, 1, 2);   -- new state

4. Consulte antes da compactação.

   SELECT * FROM test_tbl_Versioned;

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign───┬Version─┐
   │ 4324182021466249494 │    5      │    146   │  -1    │    1   │
   │ 4324182021466249494 │    5      │    146   │   1    │    1   │
   │ 4324182021466249494 │    6      │    185   │   1    │    2   │
   └─────────────────────┴───────────┴──────────┴────────┴────────┘

   Use agregação ciente da coluna Sign para obter resultados corretos antes da compactação:

   SELECT
       UserID,
       SUM(PageViews * Sign) AS PageViews,
       SUM(Duration * Sign) AS Duration
   FROM test_tbl_Versioned
   GROUP BY UserID
   HAVING SUM(Sign) > 0;

   ┌────────UserID───────┬─PageViews─┬─Duration─┐
   │ 4324182021466249494 │    6      │    185   │
   └─────────────────────┴───────────┴──────────┘

5. Force a compactação.

   OPTIMIZE TABLE test_tbl_Versioned FINAL;

6. Consulte novamente — a coluna Version correspondeu corretamente às linhas de cancelamento e de estado, apesar da inserção fora de ordem.

   SELECT * FROM test_tbl_Versioned;

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign───┬Version─┐
   │ 4324182021466249494 │    6      │    185   │   1    │    2   │
   └─────────────────────┴───────────┴──────────┴────────┴────────┘

SummingMergeTree

Recomendamos usar o SummingMergeTree em conjunto com o MergeTree. Armazene os dados detalhados brutos em uma tabela MergeTree e utilize o SummingMergeTree para resultados pré-agregados. Isso evita perda de dados caso sua chave primária seja composta incorretamente.

O SummingMergeTree pré-agrega linhas com a mesma chave primária em uma única linha somando colunas numéricas. Isso reduz o armazenamento e acelera consultas agregadas.

Observações de uso

• A pré-agregação ocorre durante a compactação em segundo plano, e não no momento da inserção. Alguns dados podem ainda não estar agregados, portanto, inclua sempre uma cláusula GROUP BY com SUM() em suas consultas.

• Apenas colunas numéricas são somadas. Colunas de string e outras colunas não numéricas que não fazem parte da chave primária recebem um valor arbitrário após a agregação.

Exemplo: Pré-agregação com SummingMergeTree

1. Crie uma tabela.

   CREATE TABLE test_tbl_summing
   (
       key UInt32,
       value UInt32
   )
   ENGINE = SummingMergeTree()
   ORDER BY key;

2. Insira linhas — algumas compartilham a mesma chave.

   INSERT INTO test_tbl_summing VALUES (1, 1), (1, 2), (2, 1);

3. Consulte antes da compactação — linhas ainda não agregadas.

   SELECT * FROM test_tbl_summing;

   ┌─key─┬value─┐
   │  1  │  1   │
   │  1  │  2   │
   │  2  │  1   │
   └─────┴──────┘

4. Force a compactação.

   OPTIMIZE TABLE test_tbl_summing FINAL;

5. Consulte com GROUP BY e SUM() para obter resultados corretos.

   SELECT key, SUM(value) FROM test_tbl_summing GROUP BY key;

   ┌─key─┬value─┐
   │  1  │  3   │
   │  2  │  1   │
   └─────┴──────┘

AggregatingMergeTree

O AggregatingMergeTree é um motor de pré-agregação mais flexível que suporta qualquer função de agregação, não apenas SUM. Ele utiliza o tipo de dados especial AggregateFunction e um padrão de consulta em duas fases:

• Fase de escrita: use funções com o sufixo -State (por exemplo, sumState, uniqState) para armazenar estados intermediários de agregação.

• Fase de consulta: use funções com o sufixo -Merge (por exemplo, sumMerge, uniqMerge) para finalizar a agregação.

O AggregatingMergeTree é utilizado de duas formas: com uma visualização materializada (que automatiza a fase de escrita) ou diretamente com colunas AggregateFunction.

Exemplo 1: AggregatingMergeTree com uma visualização materializada

1. Crie uma tabela de detalhes.

   CREATE TABLE visits
   (
       UserID UInt64,
       CounterID UInt8,
       StartDate Date,
       Sign Int8
   )
   ENGINE = CollapsingMergeTree(Sign)
   ORDER BY UserID;

2. Crie uma visualização materializada que pré-agregue a tabela de detalhes usando funções -State.

   CREATE MATERIALIZED VIEW visits_agg_view
   ENGINE = AggregatingMergeTree()
   PARTITION BY toYYYYMM(StartDate)
   ORDER BY (CounterID, StartDate)
   AS SELECT
       CounterID,
       StartDate,
       sumState(Sign)    AS Visits,
       uniqState(UserID) AS Users
   FROM visits
   GROUP BY CounterID, StartDate;

3. Insira dados na tabela de detalhes. A visualização materializada é atualizada automaticamente.

   INSERT INTO visits VALUES (0, 0, '2019-11-11', 1);
   INSERT INTO visits VALUES (1, 1, '2019-11-12', 1);

4. Consulte a visualização materializada usando funções -Merge.

   Nota

   Use sumMerge e uniqMerge — as funções simples sum e uniq não conseguem processar colunas AggregateFunction e retornarão um erro.

   SELECT
       StartDate,
       sumMerge(Visits) AS Visits,
       uniqMerge(Users) AS Users
   FROM visits_agg_view
   GROUP BY StartDate
   ORDER BY StartDate;

   ┌──StartDate──┬─Visits─┬─Users──┐
   │  2019-11-11 │   1    │   1    │
   │  2019-11-12 │   1    │   1    │
   └─────────────┴────────┴────────┘

Exemplo 2: AggregatingMergeTree com colunas AggregateFunction

1. Crie uma tabela de detalhes.

   CREATE TABLE detail_table
   (
       CounterID UInt8,
       StartDate Date,
       UserID UInt64
   ) ENGINE = MergeTree()
   PARTITION BY toYYYYMM(StartDate)
   ORDER BY (CounterID, StartDate);

2. Insira dados.

   INSERT INTO detail_table VALUES (0, '2019-11-11', 1);
   INSERT INTO detail_table VALUES (1, '2019-11-12', 1);

3. Crie uma tabela de agregação com uma coluna AggregateFunction.

   CREATE TABLE agg_table
   (
       CounterID UInt8,
       StartDate Date,
       UserID AggregateFunction(uniq, UInt64)
   ) ENGINE = AggregatingMergeTree()
   PARTITION BY toYYYYMM(StartDate)
   ORDER BY (CounterID, StartDate);

4. Insira dados agregados usando uniqState.

   Nota

   Insira usando um SELECT com funções -State. Um comando INSERT INTO ... VALUES direto com valores brutos falhará com um erro de tipo.

   INSERT INTO agg_table
   SELECT CounterID, StartDate, uniqState(UserID)
   FROM detail_table
   GROUP BY CounterID, StartDate;

5. Consulte a tabela de agregação usando uniqMerge.

   SELECT uniqMerge(UserID) AS state
   FROM agg_table
   GROUP BY CounterID, StartDate;

   ┌─state─┐
   │   1   │
   │   1   │
   └───────┘