Обновление и удаление строк

Елена Дворядкина

Концептуально ADQM не предназначен для обновления и удаления данных, однако поддерживает возможность выполнения мутаций — операций изменения/удаления данных ALTER TABLE … UPDATE/DELETE. Область применения мутаций — изменения, затрагивающие большое количество строк в таблице. Эти операции являются тяжеловесными и не подходят для частого использования (по возможности их лучше избегать).

Обновление данных

Обновить данные в таблице можно с помощью запроса ALTER TABLE…​UPDATE:

ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>] UPDATE <column_name> = <expression>[, ...] [IN PARTITION <partition_expr>] WHERE <filter_expression>;

В этом запросе:

  • [<db_name>.]<table_name> — имя таблицы, в которой будут обновляться данные. Если имя базы данных не указано, таблица ищется в текущей базе данных.

  • [ON CLUSTER <cluster_name>] — имя кластера, на всех серверах которого будет выполняться запрос. Если выражение ON CLUSTER не используется, запрос на обновление данных в таблице выполняется только на одном сервере.

  • <column_name> = <expression> — команда обновления значений в столбце. Значения столбца <column_name> заменяются на вычисленное значение выражения <expression> в каждой строке, для которой выражение <filter_expression> возвращает ненулевое значение.

     
    Значение выражения <expression> должно быть того же типа данных, что и столбец <column_name>, или должно быть приведено к тому же типу с помощью оператора CAST.

    Один запрос может содержать несколько команд <column_name> = <expression>, разделенных запятыми. Изменение значений нескольких столбцов одним запросом может быть более эффективным, чем выполнение нескольких запросов, каждый из которых обновляет один столбец.

    Изменение столбцов, которые используются при вычислении первичного ключа или ключа сортировки, не поддерживается.

  • [IN PARTITION <partition_expr>] — партиция, в которой будут обновляться данные. Использование выражения IN PARTITION помогает ускорить выполнение операции UPDATE, когда нужно точечно обновить данные в таблице, которая разбита на множество партиций.

  • <filter_expression> — выражение, возвращающее значение типа UInt8 для каждой строки таблицы, по которому будет определяться, в каких строках нужно обновить данные.

Пример

 
Исходная таблица:

--a---b----c---------date--
│ 1 │ a │ 10 │ 2022-12-10 │
│ 2 │ b │ 20 │ 2022-12-11 │
│ 3 │ c │ 30 │ 2022-12-12 │
│ 4 │ d │ 40 │ 2022-12-13 │
│ 5 │ e │ 50 │ 2022-12-14 │
│ 6 │ f │ 60 │ 2022-12-15 │
---------------------------

Cледующий запрос заменяет значения столбцов b и c в строках, в которых значение столбца date отвечает заданному условию:

ALTER TABLE test_table UPDATE b = repeat(b, 3), c = c/10 WHERE date > '2022-12-13';

Результат выполнения запроса обновления данных:

--a---b------c---------date--
│ 1 │ a   │ 10 │ 2022-12-10 │
│ 2 │ b   │ 20 │ 2022-12-11 │
│ 3 │ c   │ 30 │ 2022-12-12 │
│ 4 │ d   │ 40 │ 2022-12-13 │
│ 5 │ eee │  5 │ 2022-12-14 │
│ 6 │ fff │  6 │ 2022-12-15 │
-----------------------------

Для реплицируемых таблиц запросы ALTER TABLE…​UPDATE выполняются асинхронно. При необходимости синхронизировать обновление данных на репликах можно с помощью настройки mutations_sync.

Удаление данных

Для удаления строк данных из таблицы по заданному условию могут использоваться следующие запросы:

В большинстве случаев запросы DELETE FROM выполняются значительно быстрее, чем ALTER TABLE…​DELETE (эта операция требует слияния данных перед их удалением). DELETE FROM считается легковесной операцией, которая эффективна для удаления небольшого количества строк. Для некоторых сценариев (например, для нечастого выполнения массового удаления данных) лучше подходит ALTER TABLE…​DELETE.

ALTER TABLE…​DELETE

Синтаксис запроса:

ALTER TABLE [<db_name>.]<table_name> [ON CLUSTER <cluster_name>] DELETE [IN PARTITION <partition_expr>] WHERE <filter_expression>;

Запрос удаляет из таблицы <table_name> строки, для которых выражение <filter_expression> возвращает ненулевое значение (типа UInt8). Чтобы удалить строки только из определенной партиции, необходимо указать нужную партицию с помощью выражения IN PARTITION.

Пример

 
Исходная таблица:

--a---b------c---------date--
│ 1 │ a   │ 10 │ 2022-12-10 │
│ 2 │ b   │ 20 │ 2022-12-11 │
│ 3 │ c   │ 30 │ 2022-12-12 │
│ 4 │ d   │ 40 │ 2022-12-13 │
│ 5 │ eee │  5 │ 2022-12-14 │
│ 6 │ fff │  6 │ 2022-12-15 │
-----------------------------

Следующий запрос удаляет из таблицы test_table строки, в которых значения столбцов date и a удовлетворяют заданным условиям:

ALTER TABLE test_table DELETE WHERE date < '2022-12-13' AND a = 2;

Результат выполнения запроса:

--a---b------c---------date--
│ 1 │ a   │ 10 │ 2022-12-10 │
│ 3 │ c   │ 30 │ 2022-12-12 │
│ 4 │ d   │ 40 │ 2022-12-13 │
│ 5 │ eee │  5 │ 2022-12-14 │
│ 6 │ fff │  6 │ 2022-12-15 │
-----------------------------

Для реплицируемых таблиц запросы ALTER TABLE…​DELETE выполняются асинхронно. Если необходимо синхронизировать операцию удаления на репликах, измените значение настройки mutations_sync.

DELETE FROM

Синтаксис запроса:

DELETE FROM [<db_name>.]<table_name> [WHERE <filter_expression>];

Запрос удаляет из таблицы <table_name> строки, для которых выражение <filter_expression> возвращает ненулевое значение (типа UInt8). Удаляемые строки сразу помечаются как удаленные и автоматически исключаются из всех последующих запросов. Удаление данных происходит асинхронно в фоновом режиме (синхронность выполнения запросов определяется параметром mutations_sync).

Чтобы использовать запрос DELETE FROM, необходимо предварительно включить настройку allow_experimental_lightweight_delete:

SET allow_experimental_lightweight_delete = true;
Пример

 
Исходная таблица:

--a---b-------c---------date--
│ 1 │ a    │ 10 │ 2022-12-10 │
│ 3 │ c    │ 30 │ 2022-12-12 │
│ 4 │ d    │ 40 │ 2022-12-13 │
│ 5 │ eee  │  5 │ 2022-12-14 │
│ 6 │ fff  │  6 │ 2022-12-15 │
│ 7 │ aabb │ 10 │ 2022-12-16 │
│ 8 │ bbaa │ 20 │ 2022-12-17 │
------------------------------

Следующий запрос удаляет из таблицы test_table строки, в которых значения в столбце b содержат текст aa:

DELETE FROM test_table WHERE b LIKE '%aa%';

Результат выполнения запроса:

--a---b------c---------date--
│ 1 │ a   │ 10 │ 2022-12-10 │
│ 3 │ c   │ 30 │ 2022-12-12 │
│ 4 │ d   │ 40 │ 2022-12-13 │
│ 5 │ eee │  5 │ 2022-12-14 │
│ 6 │ fff │  6 │ 2022-12-15 │
-----------------------------
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней