Instruction OPTIMIZE - ClickHouse Documentation

Cette requête tente de lancer une fusion non planifiée de parts de données pour des tables. Notez que nous déconseillons généralement l’utilisation de OPTIMIZE TABLE ... FINAL (voir cette documentation), car son usage relève de l’administration et non des opérations quotidiennes.

OPTIMIZE ne peut pas corriger l’erreur Too many parts.

Syntaxe

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]

La requête OPTIMIZE est prise en charge pour la famille MergeTree (y compris les vues matérialisées) ainsi que pour les moteurs Buffer. Les autres moteurs de table ne sont pas pris en charge. Lorsque OPTIMIZE est utilisé avec la famille de moteurs de table ReplicatedMergeTree, ClickHouse crée une tâche de fusion et attend son exécution sur toutes les répliques (si le paramètre alter_sync est défini sur 2) ou sur la réplique actuelle (si le paramètre alter_sync est défini sur 1).

Si OPTIMIZE n’effectue pas de fusion pour quelque raison que ce soit, il n’en informe pas le client. Pour activer les notifications, utilisez le paramètre optimize_throw_if_noop.
Si vous spécifiez un PARTITION, seule la partition indiquée est optimisée. Comment définir l’expression de partition.
Si vous spécifiez FINAL ou FORCE, l’optimisation est effectuée même lorsque toutes les données se trouvent déjà dans une seule part. Vous pouvez contrôler ce comportement avec optimize_skip_merged_partitions. De plus, la fusion est forcée même si des fusions concurrentes sont en cours.
Si vous spécifiez DEDUPLICATE, les lignes complètement identiques (sauf si une clause BY est spécifiée) seront dédupliquées (toutes les colonnes sont comparées) ; cela n’a de sens que pour le moteur MergeTree.

Vous pouvez définir combien de temps (en secondes) attendre que les répliques inactives exécutent les requêtes OPTIMIZE à l’aide du paramètre replication_wait_for_inactive_replica_timeout.

Si alter_sync est défini sur 2 et que certaines répliques restent inactives au-delà du délai spécifié par le paramètre replication_wait_for_inactive_replica_timeout, une exception UNFINISHED est levée.

DRY RUN

La clause DRY RUN simule une fusion des parts spécifiées sans enregistrer le résultat. La part fusionnée est écrite dans un emplacement temporaire, validée, puis supprimée. Les parts d’origine et les données de la table restent inchangées. Cela est utile pour :

Tester la validité des fusions entre différentes versions de ClickHouse.
Reproduire de manière déterministe des bogues liés aux fusions.
Évaluer les performances des fusions.

DRY RUN n’est pris en charge que pour les tables de la famille MergeTree. Le mot-clé PARTS, accompagné d’une liste de noms de parts, est requis. Toutes les parts spécifiées doivent exister, être actives et appartenir à la même partition. DRY RUN est incompatible avec FINAL et PARTITION. Il peut être combiné avec DEDUPLICATE (avec spécification facultative des colonnes) et CLEANUP (pour les tables ReplacingMergeTree). Syntaxe

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

Par défaut, la part fusionnée obtenue est validée d’une manière similaire à la requête CHECK TABLE. Ce comportement est contrôlé par le paramètre optimize_dry_run_check_part (activé par défaut). Le désactiver permet d’ignorer cette validation, ce qui peut être utile pour évaluer les performances de la fusion elle-même. Exemple

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

-- Simulate merging using two parts
OPTIMIZE TABLE dry_run_example DRY RUN PARTS 'all_1_1_0', 'all_2_2_0';

-- Simulate merging with deduplication
OPTIMIZE TABLE dry_run_example DRY RUN PARTS 'all_1_1_0', 'all_2_2_0' DEDUPLICATE;

-- Parts and data remain unchanged after 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 │
└─────────────┴──────┘

Expression BY

Si vous souhaitez effectuer une déduplication sur un ensemble personnalisé de colonnes plutôt que sur l’ensemble des colonnes, vous pouvez spécifier explicitement une liste de colonnes ou utiliser n’importe quelle combinaison d’expressions *, COLUMNS ou EXCEPT. La liste de colonnes, qu’elle soit explicitement écrite ou implicitement développée, doit inclure toutes les colonnes spécifiées dans l’expression d’ordonnancement des lignes (à la fois la clé primaire et la clé de tri), ainsi que dans l’expression de partitionnement (clé de partitionnement).

Notez que * se comporte exactement comme dans SELECT : les colonnes MATERIALIZED et ALIAS ne sont pas utilisées lors de l’expansion.De plus, spécifier une liste vide de colonnes, écrire une expression qui aboutit à une liste vide de colonnes ou effectuer une déduplication sur une colonne ALIAS constitue une erreur.

Syntaxe

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

Exemples Prenons la table suivante :

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 │
└─────────────┴───────────────┴───────┴───────────────┘

Tous les exemples suivants sont exécutés à partir de cet état, qui comporte 5 lignes.

`DEDUPLICATE`

Lorsqu’aucune colonne de déduplication n’est spécifiée, elles sont toutes prises en compte. La ligne n’est supprimée que si toutes les valeurs de toutes les colonnes sont égales aux valeurs correspondantes de la ligne précédente :

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 *`

Lorsque les colonnes sont spécifiées de manière implicite, la table est dédupliquée sur toutes les colonnes qui ne sont ni ALIAS ni MATERIALIZED. Dans la table ci-dessus, il s’agit des colonnes primary_key, secondary_key, value et 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`

Déduplique sur toutes les colonnes qui ne sont ni ALIAS ni MATERIALIZED, à l’exclusion explicite de value : les colonnes primary_key, secondary_key et 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>`

Dédupliquez explicitement à l’aide des colonnes primary_key, secondary_key et 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>)`

Dédupliquer selon toutes les colonnes correspondant à une expression régulière : les colonnes primary_key, secondary_key et 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

​Expression BY

​DEDUPLICATE

​DEDUPLICATE BY *

​DEDUPLICATE BY * EXCEPT

​DEDUPLICATE BY <list of columns>

​DEDUPLICATE BY COLUMNS(<regex>)

DRY RUN

Expression BY

`DEDUPLICATE`

`DEDUPLICATE BY *`

`DEDUPLICATE BY * EXCEPT`

`DEDUPLICATE BY <list of columns>`

`DEDUPLICATE BY COLUMNS(<regex>)`