UPSERT VALUES

Содержание раздела
  1. Поддерживаемые сущности
  2. Поддерживаемые СУБД
  3. Особенности вставки данных в standalone-таблицы
  4. Как работает запрос
    1. Добавление и обновление записей
    2. Особенности вставки в партиции
    3. Заполнение столбцов таблицы
    4. Выполнение при отключенном датасорсе
    5. Статистика
  5. Перезапуск и отмена операций
  6. Синтаксис
    1. Ключевое слово VALUES (column_value[, ... ]) [, ... ]
    2. Ключевое слово RETRY
  7. Варианты ответа
  8. Ограничения
    1. Ограничения выполнения
    2. Ограничения СУБД
    3. Ограничения сущностей
    4. Ограничения партиционирования
    5. Другие ограничения
  9. Примеры
    1. Вставка данных во все столбцы логической таблицы
    2. Вставка данных в указанные столбцы снапшот-таблицы
    3. Вставка данных во все столбцы прокси-таблицы
    4. Вставка данных во все столбцы standalone-таблицы
    5. Вставка данных в указанные столбцы standalone-таблицы
    6. Вставка данных в указанные столбцы партиционированной таблицы
    7. Вставка данных в указанные столбцы партиции
    8. Перезапуск операции по вставке записей

Поддерживается в версиях: 7.6 / 7.5 / 7.4 / 7.3 / 7.2 / 7.1 / 7.0 / 6.12 / 6.11 / 6.10 / 6.9 / 6.8 / 6.7 / 6.6 / 6.5 / 6.4 / 6.3 / 6.2 / 6.1 / 6.0 / 5.8 / 5.7 / 5.6 / 5.5 / 5.4.

Запрос обновляет текущие или вставляет новые записи в таблицу поддерживаемого типа.

Чтобы вставить большой объем данных, используйте загрузку данных.

Поддерживаемые сущности

Вставка и обновление записей доступны в следующие таблицы:

При вставке данных в прокси-таблицы и standalone-таблицы учитывайте ограничения таблиц целевой СУБД.

Поддерживаемые СУБД

Запрос поддерживается для СУБД ADB и ADP.

Особенности вставки данных в standalone-таблицы

Вставка данных в standalone-таблицы выполняется через внешние writable-таблицы.

Вставка недоступна, если запрос содержит внешнюю writable-таблицу без первичного ключа.

Как работает запрос

Добавление и обновление записей

Обработка каждой вставляемой записи зависит от наличия/отсутствия в таблице записи с таким же первичным ключом (PK) и значения опции set.on.conflict.do таблицы (для снапшот-таблиц), как показано ниже.

Таблица PK новый или отсутствует PK совпадает
Логическая таблица Добавление новой записи Обновление текущей записи
Снапшот-таблица Добавление новой записи
  • [Опция set.on.conflict.do=update] обновление текущей записи;
  • [Опция set.on.conflict.do=error] ошибка;
  • [Опция set.on.conflict.do=nothing] пропуск записи
Прокси-, standalone-таблица Добавление новой записи Обновление текущей записи

Особенности вставки в партиции

Вставка в партиции имеет особенности:

  • [через партиционированную таблицу] записи распределяются по партициям; записи вне диапазонов партиций игнорируются;
  • [напрямую в партиции] записи вне диапазонов партиции игнорируются.

Заполнение столбцов таблицы

При вставке данных все столбцы, указанные в запросе, заполняются значениями из запроса, а пропущенные nullable-столбцы:

  • [в новой записи] заполняются значениями, используемыми по умолчанию в СУБД;
  • [в текущей записи] остаются без изменений.

Пропущенные обязательные столбцы (NOT NULL) остаются без изменений при обновлении записей в логических, прокси- и standalone-таблицах. В остальных случаях пропуск таких столбцов приводит к ошибке.

Выполнение при отключенном датасорсе

Отключенный датасорс пропускается при исполнении запроса. Вставка данных считается успешной, если данные сохранены в необходимые датасорсы.

Статистика

Запросы UPSERT VALUES учитываются в категории WRITE статистики. Подробнее о категориях запросов в статистике см. в разделе GET_ENTITY_STATISTICS, о способах просмотра статистики — в разделе Управление статистикой.

Перезапуск и отмена операций

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

Возможности по ручному управлению операциями описаны в разделе Управление операциями записи.

Синтаксис 

[RETRY] UPSERT INTO [db_name.]table_name [(column_name[, ... ])] 
VALUES (column_value[, ... ]) [, ... ]

Параметры:

db_name

Имя логической базы данных. Опционально, если выбрана логическая БД, используемая по умолчанию.

table_name

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

  • имя логической таблицы,
  • имя снапшот-таблицы,
  • имя прокси-таблицы,
  • имя внешней writable-таблицы, указывающей на нужную standalone-таблицу.

Логическая таблица может быть любого вида.

column_name

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

Про обработку пропущенных столбцов см. в секции Заполнение столбцов таблицы.

Ключевое слово VALUES (column_value[, ... ]) [, ... ]

Задает список значений, вставляемых в соответствующие столбцы таблицы. Каждый набор значений в круглых скобках — это одна строка, вставляемая в таблицу.

Ключевое слово RETRY

При типовых сбоях (перебои в сети, сбой ноды и т.п.) операции отменяются автоматически и не требуют ручного управления с помощью RETRY или других команд.

Перезапускает обработку операции записи со статусом 0 (Выполняется) в логической таблице. Если указано, после него должна следовать копия исходного запроса, создавшего перезапускаемую операцию.

Не поддерживается для других таблиц:

  • игнорируется для прокси- и standalone-таблиц;
  • приводит к ошибке для снапшот-таблиц.

Если ключевое слово не указано, система обрабатывает запрос как новый, а не перезапущенный.

Варианты ответа

В ответе возвращается:

  • объект ResultSet c одной записью при успешном выполнении запроса;
  • исключение при неуспешном выполнении запроса.

Успешный ответ содержит столбцы:

  • sysCn:
    • номер выполненной операции записи — при вставке в логическую или снапшот-таблицу;
    • пустое значение — при вставке в прокси-таблицу или во внешнюю writable-таблицу;
  • ts:
    • дата и время завершения операции записи в формате YYYY-MM-DD hh:mm:ss.SSSSSS — при выполнении операции вне дельты;
    • пустое значение — при выполнении операции в дельте;
  • rowsAffected — количество затронутых (добавленных, измененных и удаленных) строк. Расчет значения поддерживается для СУБД ADB и ADP.

Запрос с RETRY возвращает в rowsAffected количество строк, затронутых перезапущенной операцией записи. Строки, обработанные до перезапуска операции, не учитываются.

Ограничения

Ограничения выполнения

  • Выполнение запроса к логической или снапшот-таблице недоступно, если она участвует в незавершенной операции по изменению схемы.
  • Выполнение запроса вне дельты недоступно, если последняя дельта закрыта с меткой времени, превышающей серверное время.
  • Не допускается параллельное выполнение идентичных запросов.

Ограничения СУБД

  • Запрос не поддерживает вставку данных в СУБД ADQM и ADG.

Ограничения сущностей

  • Запрос недоступен для логических и материализованных представлений.
  • Обновление текущих записей невозможно в снапшот-таблицах с опциями set.on.conflict.do=error (возвращается ошибка) и set.on.conflict.do=nothing (пропускаются записи с дубликатами первичного ключа).

Ограничения партиционирования

  • При вставке данных в партиционированную таблицу все записи, для которых нет подходящей партиции, игнорируются.
  • Одновременная запись данных (загрузка, вставка и удаление) в партиционированную таблицу и ее партиции недоступна.

Другие ограничения

  • Ключевое слово RETRY доступно только для логических таблиц; для других таблиц игнорируется или приводит к ошибке.
  • Значения всех обязательных столбцов (NOT NULL) снапшот-таблицы должны быть указаны в запросе.
  • При обработке запроса отключенные (Датасорс, отключенный системой из-за сбоя или администратором
    )     датасорсы (СУБД или кластер СУБД хранилища
    )     пропускаются без возврата ошибки. Ошибка возвращается, если не осталось включенных (Датасорс, работающий в штатном режиме
    )     датасорсов, необходимых для записи данных.

Примеры

Вставка данных во все столбцы логической таблицы

Вставка данных в дельте:

-- выбор логической базы данных marketing_new в качестве базы данных по умолчанию
USE marketing_new;

-- открытие новой дельты
BEGIN DELTA;

-- вставка трех записей в логическую таблицу sales (данные таблицы расположены в датасорсе adp)
UPSERT INTO sales 
VALUES (200011, '2021-08-21 23:34:10', 'ABC0001', 2, 123, 'Покупка по акции "1+1"'), 
       (200012, '2021-08-22 10:05:56', 'ABC0001', 1, 234, 'Покупка без акций'), 
       (200013, '2021-08-22 13:17:47', 'ABC0002', 4, 123, 'Покупка по акции "Лето"');

-- закрытие дельты
COMMIT DELTA;

Вставка данных вне дельты:

-- вставка двух записей в логическую таблицу sales вне дельты
INSERT INTO marketing.sales 
VALUES (800011, '2021-07-11 23:34:10', 'ABC0004', 2, 123, 'Покупка по акции "1+1"'), 
       (800012, '2021-07-23 10:05:56', 'ABC0017', 5, 234, 'Покупка без акций')

Вставка данных в указанные столбцы снапшот-таблицы 

-- вставка двух записей в снапшот-таблицу sales_snapshot (без опционального значения description)
UPSERT INTO sales 
       (id, transaction_date, product_code, product_units, store_id)
VALUES (200014, '2025-08-23 09:34:10', 'ABC0003', 3, 123), 
       (200012, '2025-08-23 20:05:56', 'ABC0001', 6, 234);

Вставка данных во все столбцы прокси-таблицы 

-- вставка одной новой записи с id=103 и обновление существующей записи с id=100 
UPSERT INTO marketing.payments_proxy 
VALUES (103, 444444, 'AG', 1023, 'RUB', 'Номер чека 8352'),
       (100, 111111, 'AC', 12002.53, 'RUB', 'Номер чека 1234500')

Вставка данных во все столбцы standalone-таблицы 

-- вставка записей в standalone-таблицу, на которую указывает внешняя writable-таблица agreements_ext_write_adp
UPSERT INTO marketing.agreements_ext_write_adp 
VALUES (200, 444444, 'AB22222', '2022-02-08', '2022-02-09', '2024-02-09', ''), 
       (201, 555555, 'AB33333', '2022-02-10', '2022-02-11', '2025-02-11', 'Договор с ООО "Овал"');

Вставка данных в указанные столбцы standalone-таблицы 

-- вставка записей в standalone-таблицу, на которую указывает внешняя writable-таблица agreements_ext_write_adp,
--  без некоторых опциональных значений
UPSERT INTO marketing.agreements_ext_write_adp (id, client_id, number, signature_date)
VALUES (202, 999999, 'AB44444', '2022-01-01');

Вставка данных в указанные столбцы партиционированной таблицы 

-- выбор логической базы данных marketing в качестве базы данных по умолчанию
USE marketing;

-- открытие новой дельты
BEGIN DELTA;

-- вставка трех записей в партиционированную таблицу sales_partitioned
UPSERT INTO sales_partitioned (id, transaction_date, store_id)
VALUES (500011, '2023-01-11 23:34:10', 124), 
       (600012, '2023-01-18 10:05:56', 234), 
       (600113, '2023-02-22 13:17:47', 123);

-- закрытие дельты
COMMIT DELTA;

Вставка данных в указанные столбцы партиции 

-- выбор логической базы данных marketing в качестве базы данных по умолчанию
USE marketing;

-- открытие новой дельты
BEGIN DELTA;

-- вставка трех записей в партицию sales_jan_2023
---- последняя запись будет проигнорирована, так как дата транзакции в ней не принадлежит партиции sales_jan_2023
UPSERT INTO sales_jan_2023 (id, transaction_date, store_id)
VALUES (500011, '2023-01-11 23:34:10', 124), 
       (600012, '2023-01-18 10:05:56', 234), 
       (600113, '2023-02-22 13:17:47', 123);

-- закрытие дельты
COMMIT DELTA;

Перезапуск операции по вставке записей 

-- выбор логической базы данных marketing_new в качестве базы данных по умолчанию
USE marketing_new;

-- открытие новой дельты
BEGIN DELTA;

-- вставка записи в логическую таблицу sales (без опционального значения description)       
UPSERT INTO sales
       (id, transaction_date, product_code, product_units, store_id)
VALUES (200015, '2021-10-15 10:11:01', 'ABC0003', 1, 123);

-- перезапуск обработки операции по вставке записи
RETRY UPSERT INTO sales
       (id, transaction_date, product_code, product_units, store_id)
VALUES (200015, '2021-10-15 10:11:01', 'ABC0003', 1, 123); 

-- закрытие дельты
COMMIT DELTA;