UPDATE в модели Unique Key

После записи данных в Doris часто возникает необходимость изменить существующие данные: меняются статусы заказов, заданиям ETL нужно дозаполнить определённые поля, а в исторических записях обнаруживаются ошибки, которые требуется исправить. Для таких задач Doris предоставляет стандартный оператор UPDATE для модели Unique Key, позволяющий обновлять данные на уровне строк по заданному условию.

В этом документе описаны сценарии применения, основные принципы работы и типичные способы использования команды UPDATE в Doris. Примечание: команда UPDATE применима только к таблицам, использующим модель данных Unique.

Сценарии применения

Команда UPDATE предназначена прежде всего для следующих двух типичных сценариев:

Тип сценария	Описание	Рекомендуемая частота
Обновление небольшого объёма данных	Исправление ошибочных полей в небольшом числе записей или обновление статуса небольшого числа записей (например, изменение статусов заказов).	Частота сопоставима с INSERT.
Пакетная обработка ETL	Массовое обновление определённого поля, характерное для сценариев ETL, в которых некоторые столбцы нужно дозаполнить или пересчитать.	Подходит только для редких вызовов.

Основные принципы

Команда UPDATE выполняется следующим образом:

1. С помощью встроенной в движок запросов логики фильтрации WHERE из целевой таблицы выбираются строки, которые требуется обновить.
2. Целевые столбцы этих строк изменяются в памяти.
3. Изменённые строки повторно вставляются в таблицу с использованием механизма модели Unique, при котором новые данные в столбцах Value заменяют старые. Так выполняется обновление на уровне строк.

Синхронное выполнение

UPDATE в Doris является синхронным оператором: когда оператор UPDATE возвращает результат об успешном выполнении, обновление уже завершено, а новые данные сразу доступны.

Характеристики производительности

Производительность оператора UPDATE зависит главным образом от двух факторов:

• Число обновляемых строк: чем больше строк нужно обновить, тем медленнее выполняется оператор UPDATE.

  • Обновление небольшого объёма данных: Doris поддерживает частоту, сопоставимую с INSERT INTO.
  • Обновление большого объёма данных: один вызов UPDATE занимает много времени и подходит только для редкого выполнения.

• Эффективность поиска по условию запроса: при выполнении UPDATE сначала считываются строки, соответствующие условию запроса, поэтому чем эффективнее поиск по условию, тем быстрее выполняется UPDATE.

  • Рекомендуется: по возможности использовать в условии столбцы, позволяющие задействовать индексы или отсекать разделы либо бакеты, чтобы избежать полного сканирования таблицы.
  • Настоятельно не рекомендуется: включать столбцы Value в условие.

Пример использования

Следующий пример на основе сценария управления финансовыми рисками показывает, как с помощью команды UPDATE массово заполнить уровни риска в таблице с деталями транзакций.

1. Создание таблицы

Создайте таблицу с деталями транзакций, использующую модель Unique Key, с включённым режимом MOW (Merge-on-Write):

CREATE TABLE transaction_details (
    transaction_id BIGINT NOT NULL,        -- Unique transaction ID
    user_id BIGINT NOT NULL,               -- User ID
    transaction_date DATE NOT NULL,        -- Transaction date
    transaction_time DATETIME NOT NULL,    -- Transaction time
    transaction_amount DECIMAL(18, 2),     -- Transaction amount
    transaction_device STRING,             -- Transaction device
    transaction_region STRING,             -- Transaction region
    average_daily_amount DECIMAL(18, 2),   -- Average daily transaction amount in the last 3 months
    recent_transaction_count INT,          -- Number of transactions in the last 7 days
    has_dispute_history BOOLEAN,           -- Whether there is a chargeback record
    risk_level STRING                      -- Risk level
)
UNIQUE KEY(transaction_id)
DISTRIBUTED BY HASH(transaction_id) BUCKETS 16
PROPERTIES (
    "replication_num" = "3",                     -- Number of replicas, default 3
    "enable_unique_key_merge_on_write" = "true"  -- Enable MOW mode to support merge updates
);

2. Исходные данные

Таблица уже содержит следующие данные о транзакциях, при этом поле risk_level ещё не заполнено:

+----------------+---------+------------------+---------------------+--------------------+--------------------+--------------------+----------------------+--------------------------+---------------------+------------+
| transaction_id | user_id | transaction_date | transaction_time    | transaction_amount | transaction_device | transaction_region | average_daily_amount | recent_transaction_count | has_dispute_history | risk_level |
+----------------+---------+------------------+---------------------+--------------------+--------------------+--------------------+----------------------+--------------------------+---------------------+------------+
|           1001 |    5001 | 2024-11-24       | 2024-11-24 14:30:00 |             100.00 | iPhone 12          | New York           |               100.00 |                       10 |                   0 | NULL       |
|           1002 |    5002 | 2024-11-24       | 2024-11-24 03:30:00 |             120.00 | iPhone 12          | New York           |               100.00 |                       15 |                   0 | NULL       |
|           1003 |    5003 | 2024-11-24       | 2024-11-24 10:00:00 |             150.00 | Samsung S21        | Los Angeles        |               100.00 |                       30 |                   0 | NULL       |
|           1004 |    5004 | 2024-11-24       | 2024-11-24 16:00:00 |             300.00 | MacBook Pro        | high_risk_region1  |               200.00 |                        5 |                   0 | NULL       |
|           1005 |    5005 | 2024-11-24       | 2024-11-24 11:00:00 |            1100.00 | iPad Pro           | Chicago            |               200.00 |                       10 |                   0 | NULL       |
+----------------+---------+------------------+---------------------+--------------------+--------------------+--------------------+----------------------+--------------------------+---------------------+------------+

3. Правила оценки риска

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

№	Правило	Уровень риска
1	Есть запись о возврате платежа	высокий
2	Транзакция в регионе с высоким риском	высокий
3	Аномальная сумма транзакции (превышает среднедневную более чем в 5 раз)	высокий
4	Более 50 транзакций за последние 7 дней	высокий
5	От 20 до 50 транзакций за последние 7 дней	средний
6	Транзакция в нерабочее время (между 2 и 4 часами утра)	средний
7	Прочие случаи (по умолчанию)	низкий

4. Выполнение UPDATE

Используйте выражение CASE WHEN, чтобы применить все приведённые выше правила к записям за день одним оператором:

UPDATE transaction_details
SET risk_level = CASE
    -- Transactions with a chargeback record or in a high-risk region
    WHEN has_dispute_history = TRUE THEN 'high'
    WHEN transaction_region IN ('high_risk_region1', 'high_risk_region2') THEN 'high'

    -- Sudden abnormal transaction amount
    WHEN transaction_amount > 5 * average_daily_amount THEN 'high'

    -- Very high transaction frequency in the last 7 days
    WHEN recent_transaction_count > 50 THEN 'high'
    WHEN recent_transaction_count BETWEEN 20 AND 50 THEN 'medium'

    -- Off-hours transactions
    WHEN HOUR(transaction_time) BETWEEN 2 AND 4 THEN 'medium'

    -- Default risk
    ELSE 'low'
END
WHERE transaction_date = '2024-11-24';

5. Результат обновления

После успешного выполнения UPDATE запрос к таблице показывает, что поле risk_level заполнено в соответствии с правилами:

+----------------+---------+------------------+---------------------+--------------------+--------------------+--------------------+----------------------+--------------------------+---------------------+------------+
| transaction_id | user_id | transaction_date | transaction_time    | transaction_amount | transaction_device | transaction_region | average_daily_amount | recent_transaction_count | has_dispute_history | risk_level |
+----------------+---------+------------------+---------------------+--------------------+--------------------+--------------------+----------------------+--------------------------+---------------------+------------+
|           1001 |    5001 | 2024-11-24       | 2024-11-24 14:30:00 |             100.00 | iPhone 12          | New York           |               100.00 |                       10 |                   0 | low        |
|           1002 |    5002 | 2024-11-24       | 2024-11-24 03:30:00 |             120.00 | iPhone 12          | New York           |               100.00 |                       15 |                   0 | medium     |
|           1003 |    5003 | 2024-11-24       | 2024-11-24 10:00:00 |             150.00 | Samsung S21        | Los Angeles        |               100.00 |                       30 |                   0 | medium     |
|           1004 |    5004 | 2024-11-24       | 2024-11-24 16:00:00 |             300.00 | MacBook Pro        | high_risk_region1  |               200.00 |                        5 |                   0 | high       |
|           1005 |    5005 | 2024-11-24       | 2024-11-24 11:00:00 |            1100.00 | iPad Pro           | Chicago            |               200.00 |                       10 |                   0 | high       |
+----------------+---------+------------------+---------------------+--------------------+--------------------+--------------------+----------------------+--------------------------+---------------------+------------+

Дополнительная справка

Полный синтаксис команды UPDATE приведён в руководстве по команде UPDATE. Дополнительную информацию также можно получить, выполнив HELP UPDATE в командной строке клиента MySQL.