UPSERT VALUES

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

Поддерживается в версиях: 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-таблицы, которая указывает на standalone-таблицу.

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

Отличие от INSERT VALUES

В отличие от INSERT VALUES, запрос UPSERT VALUES обновляет существующую запись только теми значениями, которые указаны в запросе.

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

Порядок обработки вставляемых записей

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

Вставка в логические таблицы

Записи вставляются в логические таблицы так:

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

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

При вставке данных в партиционированную таблицу система распределяет записи по соответствующим партициям. Если для записи не нашлось партиции с подходящим диапазоном партиционирования, эта запись игнорируется.

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

Вставка в прокси-таблицы и standalone-таблицы

Записи вставляются в прокси- и 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 («Выполняется») в логической таблице. После него должна следовать копия исходного запроса, создавшего перезапускаемую операцию.

Если ключевое слово не указано, создается новая операция записи.

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

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

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

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

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

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

Ограничения

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

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

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

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

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

  • Вставка данных в логические и материализованные представления недоступна.

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

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

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

  • Ключевое слово RETRY недоступно в запросах к прокси-таблицам и standalone-таблицам.
  • При обработке запроса неактивные датасорсы пропускаются без возврата ошибки. Ошибка возвращается, если не осталось ни одного активного датасорса, подходящего для исполнения запроса.

Примеры

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

-- выбор логической базы данных 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;

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

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

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

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

-- закрытие дельты
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, 'Покупка без акций')

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

-- вставка одной новой записи с 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;