Instrução OPTIMIZE - ClickHouse Documentation

Esta consulta tenta iniciar uma mesclagem não agendada de partes de dados em tabelas. Observe que, em geral, não recomendamos o uso de OPTIMIZE TABLE ... FINAL (consulte esta documentação), pois esse caso de uso se destina à administração, não às operações diárias.

OPTIMIZE não pode corrigir o erro Too many parts.

Sintaxe

OPTIMIZE TABLE [db.]name [ON CLUSTER cluster] [PARTITION partition | PARTITION ID 'partition_id'] [FINAL | FORCE] [DEDUPLICATE [BY expression]]

OPTIMIZE TABLE [db.]name DRY RUN PARTS 'part_name1', 'part_name2' [, ...] [DEDUPLICATE [BY expression]] [CLEANUP]

A consulta OPTIMIZE é compatível com a família MergeTree (incluindo visões materializadas) e com os motores Buffer. Outros motores de tabela não são compatíveis. Quando OPTIMIZE é usado com a família ReplicatedMergeTree de motores de tabela, o ClickHouse cria uma tarefa de mesclagem e aguarda sua execução em todas as réplicas (se a configuração alter_sync estiver definida como 2) ou na réplica atual (se a configuração alter_sync estiver definida como 1).

Se OPTIMIZE não executar uma mesclagem por qualquer motivo, o cliente não será notificado. Para ativar notificações, use a configuração optimize_throw_if_noop.
Se você especificar uma PARTITION, apenas a partição especificada será otimizada. Como definir a expressão de partição.
Se você especificar FINAL ou FORCE, a otimização será executada mesmo quando todos os dados já estiverem em uma única parte. Você pode controlar esse comportamento com optimize_skip_merged_partitions. Além disso, a mesclagem é forçada mesmo se houver mesclagens simultâneas em execução.
Se você especificar DEDUPLICATE, linhas completamente idênticas (a menos que a cláusula BY seja especificada) serão desduplicadas (todas as colunas são comparadas); isso só faz sentido para o motor MergeTree.

Você pode especificar por quanto tempo (em segundos) esperar que réplicas inativas executem consultas OPTIMIZE por meio da configuração replication_wait_for_inactive_replica_timeout.

Se alter_sync estiver definido como 2 e algumas réplicas permanecerem inativas por mais tempo do que o especificado pela configuração replication_wait_for_inactive_replica_timeout, então será gerada uma exceção UNFINISHED.

DRY RUN

A cláusula DRY RUN simula uma mesclagem das partes especificadas sem efetivar o resultado. A parte mesclada é gravada em um local temporário, validada e depois descartada. As partes originais e os dados da tabela permanecem inalterados. Isso é útil para:

Testar a correção da mesclagem entre versões do ClickHouse.
Reproduzir de forma determinística bugs relacionados à mesclagem.
Fazer benchmarking do desempenho da mesclagem.

DRY RUN tem suporte apenas para tabelas da família MergeTree. A palavra-chave PARTS, com uma lista de nomes de partes, é obrigatória. Todas as partes especificadas devem existir, estar ativas e pertencer à mesma partição. DRY RUN é incompatível com FINAL e PARTITION. Pode ser combinado com DEDUPLICATE (com especificação opcional de colunas) e CLEANUP (para tabelas ReplacingMergeTree). Sintaxe

OPTIMIZE TABLE [db.]name DRY RUN PARTS 'part_name1', 'part_name2' [, ...] [DEDUPLICATE [BY expression]] [CLEANUP]

Por padrão, a parte resultante da mesclagem é validada de forma semelhante à consulta CHECK TABLE. Esse comportamento é controlado pela configuração optimize_dry_run_check_part (habilitada por padrão). Desabilitá-la faz com que a validação seja ignorada, o que pode ser útil para benchmarking da própria mesclagem. Exemplo

CREATE TABLE dry_run_example (key UInt64, value String) ENGINE = MergeTree ORDER BY key;

INSERT INTO dry_run_example VALUES (1, 'a'), (2, 'b');
INSERT INTO dry_run_example VALUES (1, 'c'), (4, 'd');

-- Simular a mesclagem usando duas partes
OPTIMIZE TABLE dry_run_example DRY RUN PARTS 'all_1_1_0', 'all_2_2_0';

-- Simular a mesclagem com desduplicação
OPTIMIZE TABLE dry_run_example DRY RUN PARTS 'all_1_1_0', 'all_2_2_0' DEDUPLICATE;

-- As partes e os dados permanecem inalterados após DRY RUN
SELECT name, rows FROM system.parts
WHERE database = currentDatabase() AND table = 'dry_run_example' AND active
ORDER BY name;

┌─name────────┬─rows─┐
│ all_1_1_0   │    2 │
│ all_2_2_0   │    2 │
└─────────────┴──────┘

expressão BY

Se você quiser realizar a desduplicação em um conjunto personalizado de colunas, em vez de em todas elas, poderá especificar explicitamente uma lista de colunas ou usar qualquer combinação das expressões *, COLUMNS ou EXCEPT. A lista de colunas escrita explicitamente ou expandida implicitamente deve incluir todas as colunas especificadas na expressão de ordenação das linhas (tanto a chave primária quanto a chave de ordenação) e na expressão de particionamento (chave de particionamento).

Observe que * se comporta exatamente como em SELECT: colunas MATERIALIZED e ALIAS não são consideradas na expansão.Além disso, é um erro especificar uma lista vazia de colunas, escrever uma expressão que resulte em uma lista vazia de colunas ou fazer a desduplicação por uma coluna ALIAS.

Sintaxe

OPTIMIZE TABLE table DEDUPLICATE; -- all columns
OPTIMIZE TABLE table DEDUPLICATE BY *; -- excludes MATERIALIZED and ALIAS columns
OPTIMIZE TABLE table DEDUPLICATE BY colX,colY,colZ;
OPTIMIZE TABLE table DEDUPLICATE BY * EXCEPT colX;
OPTIMIZE TABLE table DEDUPLICATE BY * EXCEPT (colX, colY);
OPTIMIZE TABLE table DEDUPLICATE BY COLUMNS('column-matched-by-regex');
OPTIMIZE TABLE table DEDUPLICATE BY COLUMNS('column-matched-by-regex') EXCEPT colX;
OPTIMIZE TABLE table DEDUPLICATE BY COLUMNS('column-matched-by-regex') EXCEPT (colX, colY);

Exemplos Considere a tabela:

Query

CREATE TABLE example (
    primary_key Int32,
    secondary_key Int32,
    value UInt32,
    partition_key UInt32,
    materialized_value UInt32 MATERIALIZED 12345,
    aliased_value UInt32 ALIAS 2,
    PRIMARY KEY primary_key
) ENGINE=MergeTree
PARTITION BY partition_key
ORDER BY (primary_key, secondary_key);

Query

INSERT INTO example (primary_key, secondary_key, value, partition_key)
VALUES (0, 0, 0, 0), (0, 0, 0, 0), (1, 1, 2, 2), (1, 1, 2, 3), (1, 1, 3, 3);

Query

SELECT * FROM example;

Response

┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           0 │             0 │     0 │             0 │
│           0 │             0 │     0 │             0 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           1 │             1 │     2 │             2 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           1 │             1 │     2 │             3 │
│           1 │             1 │     3 │             3 │
└─────────────┴───────────────┴───────┴───────────────┘

Todos os exemplos a seguir são executados nesse estado com 5 linhas.

`DEDUPLICATE`

Quando as colunas para desduplicação não são especificadas, todas são consideradas. A linha é removida apenas se todos os valores de todas as colunas forem iguais aos valores correspondentes da linha anterior:

Query

OPTIMIZE TABLE example FINAL DEDUPLICATE;

Query

SELECT * FROM example;

Response

┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           1 │             1 │     2 │             2 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           0 │             0 │     0 │             0 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           1 │             1 │     2 │             3 │
│           1 │             1 │     3 │             3 │
└─────────────┴───────────────┴───────┴───────────────┘

`DEDUPLICATE BY *`

Quando as colunas são especificadas de forma implícita, a tabela é deduplicada com base em todas as colunas que não são ALIAS nem MATERIALIZED. Considerando a tabela acima, essas colunas são primary_key, secondary_key, value e partition_key:

Query

OPTIMIZE TABLE example FINAL DEDUPLICATE BY *;

Query

SELECT * FROM example;

Response

┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           1 │             1 │     2 │             2 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           0 │             0 │     0 │             0 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           1 │             1 │     2 │             3 │
│           1 │             1 │     3 │             3 │
└─────────────┴───────────────┴───────┴───────────────┘

`DEDUPLICATE BY * EXCEPT`

Remova duplicatas com base em todas as colunas que não são ALIAS nem MATERIALIZED, excluindo explicitamente value: as colunas primary_key, secondary_key e partition_key.

Query

OPTIMIZE TABLE example FINAL DEDUPLICATE BY * EXCEPT value;

Query

SELECT * FROM example;

Response

┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           1 │             1 │     2 │             2 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           0 │             0 │     0 │             0 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           1 │             1 │     2 │             3 │
└─────────────┴───────────────┴───────┴───────────────┘

`DEDUPLICATE BY <list of columns>`

Remova duplicatas explicitamente com base nas colunas primary_key, secondary_key e partition_key:

Query

OPTIMIZE TABLE example FINAL DEDUPLICATE BY primary_key, secondary_key, partition_key;

Query

SELECT * FROM example;

Response

┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           1 │             1 │     2 │             2 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           0 │             0 │     0 │             0 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           1 │             1 │     2 │             3 │
└─────────────┴───────────────┴───────┴───────────────┘

`DEDUPLICATE BY COLUMNS(<regex>)`

Remova duplicatas com base em todas as colunas que correspondam a uma expressão regular: as colunas primary_key, secondary_key e partition_key:

Query

OPTIMIZE TABLE example FINAL DEDUPLICATE BY COLUMNS('.*_key');

Query

SELECT * FROM example;

Response

┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           0 │             0 │     0 │             0 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           1 │             1 │     2 │             2 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           1 │             1 │     2 │             3 │
└─────────────┴───────────────┴───────┴───────────────┘

​DRY RUN

​expressão BY

​DEDUPLICATE

​DEDUPLICATE BY *

​DEDUPLICATE BY * EXCEPT

​DEDUPLICATE BY <list of columns>

​DEDUPLICATE BY COLUMNS(<regex>)

DRY RUN

expressão BY

`DEDUPLICATE`

`DEDUPLICATE BY *`

`DEDUPLICATE BY * EXCEPT`

`DEDUPLICATE BY <list of columns>`

`DEDUPLICATE BY COLUMNS(<regex>)`