OPTIMIZE
Запрос пытается запустить внеплановое слияние кусков данных для таблиц.
OPTIMIZE не устраняет причину появления ошибки Too many parts.
Синтаксис
OPTIMIZE TABLE [db.]name [ON CLUSTER cluster] [PARTITION partition | PARTITION ID 'partition_id'] [FINAL] [DEDUPLICATE [BY expression]]
Может применяться к таблицам семейства MergeTree, MaterializedView и Buffer. Другие движки таблиц не поддерживаются.
Если запрос OPTIMIZE применяется к таблицам семейства ReplicatedMergeTree, ClickHouse создаёт задачу на слияние и ожидает её исполнения на всех репликах (если значение настройки alter_sync равно 2) или на текущей реплике (если значение настройки alter_sync равно 1).
- По умолчанию, если запросу
OPTIMIZEне удалось выполнить слияние, то ClickHouse не оповещает клиента. Чтобы включить оповещения, используйте настройку optimize_throw_if_noop. - Если указать
PARTITION, то оптимизация выполняется только для указанной партиции. Как задавать имя партиции в запросах. - Если указать
FINAL, то оптимизация выполняется даже в том случае, если все данные уже лежат в одном куске данных. Можно контролировать с помощью настройки optimize_skip_merged_partitions. Кроме того, слияние является принудительным, даже если выполняются параллельные слияния. - Если указать
DEDUPLICATE, то произойдет схлопывание полностью одинаковых строк (сравниваются значения во всех столбцах), имеет смысл только для движка MergeTree.
Вы можете указать время ожидания (в секундах) выполнения запросов OPTIMIZE для неактивных реплик с помощью настройки replication_wait_for_inactive_replica_timeout.
Если значение настройки alter_sync равно 2 и некоторые реплики не активны больше времени, заданного настройкой replication_wait_for_inactive_replica_timeout, то генерируется исключение UNFINISHED.
Выражение BY
Чтобы выполнить дедупликацию по произвольному набору столбцов, вы можете явно указать список столбцов или использовать любую комбинацию подстановки *, выражений COLUMNS и EXCEPT.
Список столбцов для дедупликации должен включать все столбцы, указанные в условиях сортировки (первичный ключ и ключ сортировки), а также в условиях партиционирования (ключ партиционирования).
Обратите внимание, что символ подстановки * обрабатывается так же, как и в запросах SELECT: столбцы MATERIALIZED и ALIAS не включаются в результат.
Если указать пустой список или выражение, которое возвращает пустой список, то сервер вернет ошибку. Запрос вида DEDUPLICATE BY aliased_value также вернет ошибку.
Синтаксис
OPTIMIZE TABLE table DEDUPLICATE; -- по всем столбцам
OPTIMIZE TABLE table DEDUPLICATE BY *; -- исключаются столбцы MATERIALIZED и ALIAS
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);
Примеры
Рассмотрим таблицу:
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);
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);
SELECT * FROM example;
Результат:
┌─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 │
└─────────────┴───────────────┴───────┴───────────────┘
Если в запросе не указаны столбцы, по которым нужно дедуплицировать, то учитываются все столбцы таблицы. Строка удаляется только в том случае, если все значения во всех столбцах равны соответствующим значениям в другой строке.
OPTIMIZE TABLE example FINAL DEDUPLICATE;
SELECT * FROM example;
Результат:
┌─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 │
└─────────────┴───────────────┴───────┴───────────────┘
Если столбцы в запросе указаны через *, то дедупликация пройдет по всем столбцам, кроме ALIAS и MATERIALIZED. Для таблицы example будут учтены: primary_key, secondary_key, value и partition_key.
OPTIMIZE TABLE example FINAL DEDUPLICATE BY *;
SELECT * FROM example;
Результат:
┌─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 │
└─────────────┴───────────────┴───────┴───────────────┘
Дедупликация по всем столбцам, кроме ALIAS и MATERIALIZED (BY *), и с исключением столбца value: primary_key, secondary_key и partition_key.
OPTIMIZE TABLE example FINAL DEDUPLICATE BY * EXCEPT value;
SELECT * FROM example;
Результат:
┌─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 │
└─────────────┴───────────────┴───────┴───────────────┘
Дедупликация по столбцам primary_key, secondary_key и partition_key.
OPTIMIZE TABLE example FINAL DEDUPLICATE BY primary_key, secondary_key, partition_key;
SELECT * FROM example;
Результат:
┌─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 │
└─────────────┴───────────────┴───────┴───────────────┘
Дедупликация по любому столбцу, который соответствует регулярному выражению .*_key: primary_key, secondary_key и partition_key.
OPTIMIZE TABLE example FINAL DEDUPLICATE BY COLUMNS('.*_key');
SELECT * FROM example;
Результат:
┌─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 │
└─────────────┴───────────────┴───────┴───────────────┘