UPSERT VALUES

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

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

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

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

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

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

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

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

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

При записи данных напрямую в партицию другие партиции таблицы остаются доступны для записи данных.

Подробнее о партиционировании см. в разделе Партиционирование данных.

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

Синтаксис вставки в standalone-таблицу подразумевает использование внешней writable-таблицы, которая указывает на standalone-таблицу.

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

Отличие от INSERT VALUES

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

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

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

Запрос обрабатывается в порядке, описанном в разделе Порядок обработки запросов на обновление данных.

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

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

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

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

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

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

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

Записи вставляются в прокси-таблицу или standalone-таблицу следующим образом:

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

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

Незавершенную операцию по вставке данных можно перезапустить, повторив исходный запрос с ключевым словом RETRY, или отменить.

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

Статистика обработки запросов

Запросы UPSERT VALUES учитываются в категории WRITE статистики обработки запросов. Статистика доступна с помощью запроса GET_ENTITY_STATISTICS и GET-методов получения статистики.

Синтаксис

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

UPSERT INTO [db_name.]table_name VALUES (value_list_1), (value_list_2), ...

Вставка данных в некоторые столбцы таблицы (с сохранением значений остальных столбцов без изменений):

UPSERT INTO [db_name.]table_name (column_list) VALUES (value_list_1), (value_list_2), ...

Перезапуск операции по вставке данных:

RETRY UPSERT INTO [db_name.]table_name [(column_list)] VALUES (value_list_1), (value_list_2), ...

Параметры:

db_name

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

table_name

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

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

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

column_list

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

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

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

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

Ключевое слово RETRY перезапускает обработку незавершенной операции записи со статусом «Выполняется» и поддерживается только для логических таблиц. Подробнее обо всех способах перезапуска и отмены операций см. в разделе Управление операциями записи.

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

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

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

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

  • объект 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', 'Check number 8352'),
       (100, 111111, 'AC', 12002.53, 'RUB', 'Check number 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;