INSERT SELECT

Содержание раздела
  1. Поддерживаемые сущности
  2. Датасорс-источник
  3. Поддерживаемые сочетания датасорсов
  4. Особенности вставки данных в прокси- и standalone-таблицы
  5. Как работает запрос
    1. Добавление и обновление записей
    2. Особенности вставки в партиции
    3. Заполнение столбцов таблицы
    4. Выполнение при отключенном датасорсе
    5. Статистика
  6. Перезапуск и отмена операций
  7. Синтаксис
    1. Ключевое слово SELECT query
    2. Ключевое слово RETRY
  8. Варианты ответа
  9. Ограничения
    1. Ограничения выполнения
    2. Ограничения сущностей
    3. Ограничения партиционирования
    4. Другие ограничения
  10. Примеры
    1. Вставка данных во все столбцы логической таблицы
    2. Вставка данных в некоторые столбцы снапшот-таблицы
    3. Вставка данных из логической таблицы другой логической БД
    4. Вставка данных из логической таблицы, размещенной в другом датасорсе типа ADP
    5. Вставка данных из логического представления в логическую таблицу
    6. Заполнение столбца снапшот-таблицы данными логической таблицы
    7. Вставка данных из standalone-таблицы в логическую таблицу
    8. Вставка данных из логической таблицы в standalone-таблицу
    9. Вставка данных из обычной логической таблицы в партиционированную таблицу
    10. Вставка данных из обычной логической таблицы в партицию
    11. Вставка данных из партиционированной таблицы в обычную логическую таблицу
    12. Перезапуск операции по вставке записей

Поддерживается в версиях: 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 / 5.3 / 5.2.

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

По умолчанию система выбирает наиболее подходящий датасорс (или датасорсы) в качестве источника данных. Указать определенный датасорс-источник можно с помощью ключевого слова DATASOURCE_TYPE в SELECT-подзапросе.

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

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

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

Выбор данных для вставки и обновления доступен из любых сущностей и их соединений, для которых поддерживаются SELECT-запросы.

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

Датасорс-источник

Источником данных служит датасорс (по убыванию приоритета):

  1. указанный в DATASOURCE_TYPE;
  2. подходящий для партиционированных и снапшот-таблиц подзапроса:
    1. первый по алфавиту среди подходящих для снапшот-таблиц со строгой консистентностью данных;
    2. наиболее подходящий для чтения/трансфера данных партиционированной или снапшот-таблицы, служащей источником/приемником;
  3. каждый целевой датасорс сам для себя (при наличии в них данных SELECT);
  4. подходящий для всех сущностей SELECT-подзапроса:
    1. [HTTP] вычисленный по queryId из подходящих;
    2. случайный из подходящих.

Поддерживаемые сочетания датасорсов

Вставка доступна в любом из следующих случаев:

  • [локальное исполнение] все целевые датасорсы содержат выбираемые данные;
  • [трансфер между датасорсами] выбранный датасорс-источник поддерживает трансфер данных во все целевые датасорсы (см. таблицу ниже).

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

Сущности-источники Таблица-приемник Датасорс-источник Целевой датасорс
ADP ADG ADQM ADB
Любые, кроме снапшот-таблиц Любая из поддерживаемых ADP + + – –
ADB + + + –
Снапшот-таблицы Логическая или снапшот-таблица ADP + – – –
Прокси- или standalone-таблица ADP + + – –

Датасорсы таблицы можно указать при ее создании. Датасорсы существующей таблицы можно изменять для логических таблиц и снапшот-таблиц командами ALTER TABLE ADD DATASOURCE (добавляет датасорс) и DROP TABLE … DATASOURCE_TYPE (удаляет датасорс).

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

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

  • таблицы могут иметь ограничения на вставку, вызванные особенностями целевой СУБД (см. в документации соответствующей СУБД);
  • вставка в standalone-таблицы выполняется через внешние writable-таблицы.

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

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

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

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

Обновить записи прокси- и standalone-таблиц можно запросом UPSERT VALUES.

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

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

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

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

При вставке данных столбцы заполняются:

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

Пропуск в запросе обязательных столбцов (NOT NULL) таблицы не поддерживается: возвращается ошибка.

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

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

Статистика

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

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

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

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

Синтаксис 

[RETRY] INSERT INTO [db_name.]table_name [(column_name[, ... ])] SELECT query

Параметры:

db_name

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

table_name

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

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

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

column_name

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

Пропущенные столбцы заполняются, как описано в секции Заполнение столбцов таблицы.

Ключевое слово SELECT query

Задает SELECT-подзапрос для выбора данных из источника.

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

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

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

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

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

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

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

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

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

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

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

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

Ограничения

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

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

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

  • Запрос недоступен для логических и материализованных представлений.
  • Обновление текущих записей невозможно в таблицах:
    • в снапшот-таблицах с опциями set.on.conflict.do=error (возвращается ошибка) и set.on.conflict.do=nothing (пропускаются записи с дубликатами первичного ключа);
    • в прокси- и standalone-таблицах (возвращается ошибка или добавляются записи с дубликатами первичного ключа — в зависимости от СУБД).
  • Если данные вставляются из прокси-таблицы или standalone-таблицы в логическую или снапшот-таблицу, данные таблицы-источника должны оставаться неизменными во время работы запроса.

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

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

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

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

Примеры

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

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

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

-- создание логической таблицы sales_july_2021 с данными о продажах за июль 2021 (с размещением данных в датасорсе adb)
CREATE TABLE sales_july_2021 (
id BIGINT NOT NULL,
transaction_date TIMESTAMP NOT NULL,
product_code VARCHAR(256) NOT NULL,
product_units BIGINT NOT NULL,
store_id BIGINT NOT NULL,
description VARCHAR(256),
PRIMARY KEY (id)
) DISTRIBUTED BY (id)
DATASOURCE_TYPE ('adb');

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

-- вставка данных из таблицы sales в таблицу sales_july_2021 
INSERT INTO sales_july_2021 
SELECT * FROM sales WHERE CAST(EXTRACT(MONTH FROM transaction_date) AS INT) = 7 AND 
  CAST(EXTRACT(YEAR FROM transaction_date) AS INT) = 2021 DATASOURCE_TYPE = 'adb';

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

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

-- вставка данных в таблицу current_stores вне дельты (столбец description пропущен)
INSERT INTO marketing.current_stores (id, category, region, address)
SELECT id, category, region, address FROM marketing.stores FOR SYSTEM_TIME AS OF DELTA_NUM 11 DATASOURCE_TYPE = 'adqm';

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

-- вставка данных в таблицу sales_snapshot (столбец description пропущен)
INSERT INTO marketing.sales_snapshot (id, transaction_date, product_code, product_units, store_id)
SELECT id, transaction_date, product_code, product_units, store_id
 FROM marketing.sales FOR SYSTEM_TIME AS OF DELTA_NUM 0

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

-- создание новой логической БД marketing_new
CREATE DATABASE marketing_new;

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

-- создание логической таблицы sales в логической БД marketing_new с размещением данных в датасорсе adp
CREATE TABLE sales (
id BIGINT NOT NULL,
transaction_date TIMESTAMP NOT NULL,
product_code VARCHAR(256) NOT NULL,
product_units BIGINT NOT NULL,
store_id BIGINT NOT NULL,
description VARCHAR(256),
PRIMARY KEY (id)
) DISTRIBUTED BY (id)
DATASOURCE_TYPE ('adp');

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

-- вставка данных в таблицу sales из аналогичной таблицы логической БД marketing
INSERT INTO sales SELECT * FROM marketing.sales WHERE store_id BETWEEN 100 AND 200 DATASOURCE_TYPE = 'adp';

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

Вставка данных из логической таблицы, размещенной в другом датасорсе типа ADP 

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

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

-- вставка данных в таблицу clients_adp2 из таблицы clients_adp
INSERT INTO marketing.clients_adp2 SELECT * FROM marketing.clients_adp;

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

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

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

-- создание логического представления basic_stores с данными о магазинах категории basic
CREATE VIEW basic_stores AS SELECT * FROM stores WHERE category = 'basic';

-- создание таблицы basic_stores_table с данными о магазинах категории basic (с размещением данных в датасорсах adb и adqm)
CREATE TABLE basic_stores_table (
id BIGINT NOT NULL,
category VARCHAR(256),
region VARCHAR(256),
address VARCHAR(256),
description VARCHAR(256),
PRIMARY KEY (id)
) DISTRIBUTED BY (id)
DATASOURCE_TYPE ('adb', 'adqm');

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

-- вставка данных в таблицу basic_stores_table
INSERT INTO basic_stores_table SELECT * FROM basic_stores DATASOURCE_TYPE = 'adb';

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

Заполнение столбца снапшот-таблицы данными логической таблицы 

--- вставка адресов из логической таблицы stores в те строки снапшот-таблицы sales_snapshot, где адрес не заполнен
INSERT INTO marketing.sales_snapshot
SELECT s.id, s.transaction_date, s.product_code, s.product_units, s.store_id, s.description, 
st.region || ', ' || st.address as store_address
FROM marketing.stores AS st 
JOIN marketing.sales_snapshot AS s ON s.store_id = st.id 
WHERE s.store_address IS NULL OR s.store_address = '';

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

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

-- создание логической таблицы agreements_adp с размещением данных в датасорсе adp
CREATE TABLE agreements_adp (
  id BIGINT NOT NULL,
  client_id BIGINT NOT NULL,
  number VARCHAR NOT NULL,
  signature_date DATE,
  effective_date DATE,
  closing_date DATE,
  description VARCHAR,
  PRIMARY KEY(id)
)
DISTRIBUTED BY (id)
DATASOURCE_TYPE ('adp');

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

-- вставка данных в логическую таблицу agreements_adp из standalone-таблицы, 
--   на которую указывает внешняя readable-таблица agreements_ext_read_adp
INSERT INTO agreements_adp SELECT * FROM agreements_ext_read_adp;

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

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

-- создание внешней writable-таблицы, связанной со standalone-таблицей датасорса adg
CREATE WRITABLE EXTERNAL TABLE marketing.sales_ext_write_adg (
  id BIGINT NOT NULL,
  transaction_date TIMESTAMP NOT NULL,
  product_code VARCHAR(256) NOT NULL,
  product_units BIGINT NOT NULL,
  store_id BIGINT NOT NULL,
  description VARCHAR(256),
  PRIMARY KEY (id)
)
DISTRIBUTED BY (id)
LOCATION 'core:adg://dtm__marketing__sales'
OPTIONS ('auto.create.table.enable=true');

-- вставка данных в standalone-таблицу, на которую указывает внешняя writable-таблица sales_ext_write_adg,
--   из логической таблицы sales
INSERT INTO marketing.sales_ext_write_adg SELECT * FROM marketing.sales DATASOURCE_TYPE = 'adg';

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

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

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

-- вставка данных в партиционированную таблицу sales_partitioned
INSERT INTO sales_partitioned SELECT * FROM sales 
WHERE store_id <> 123
DATASOURCE_TYPE = 'adp';

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

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

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

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

-- вставка данных в партицию sales_feb_2023
INSERT INTO sales_feb_2023 SELECT * FROM sales 
WHERE store_id >= 123 AND product_code IN ('ABC0001', 'ABC0003', 'ABC0002')
DATASOURCE_TYPE = 'adp2';

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

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

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

-- создание обычной логической таблицы, которая будет содержать данные арендных договоров магазинов, 
--   выбранные из партиций по условию и собранные в одном месте
CREATE TABLE rent_agreements_centralized (
  id BIGINT NOT NULL,
  store_id BIGINT NOT NULL,
  number VARCHAR NOT NULL,
  signature_date DATE,
  effective_date DATE,
  closing_date DATE,
  region_code INTEGER NOT NULL,
  description VARCHAR,
  PRIMARY KEY(id)
)
DISTRIBUTED BY (id)
DATASOURCE_TYPE ('adp', 'adp2', 'adg');

-- вставка данных в логическую таблицу из всех партиций партиционированной таблицы rent_agreements_partitioned_by_regions,
--  записи которых соответствуют условию
INSERT INTO rent_agreements_centralized SELECT * from rent_agreements_partitioned_by_regions
WHERE TIMESTAMPDIFF(DAY, CURRENT_DATE, closing_date) > 1;

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

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

-- открытие новой дельты
BEGIN DELTA;
   
-- вставка записей в таблицу current_stores без указания значения столбца description
INSERT INTO current_stores 
       (id, category, region, address)
SELECT id, category, region, address 
FROM stores FOR SYSTEM_TIME AS OF DELTA_NUM 12 DATASOURCE_TYPE = 'adqm';

-- перезапуск обработки операции по вставке записи
RETRY INSERT INTO current_stores 
       (id, category, region, address)
SELECT id, category, region, address 
FROM stores FOR SYSTEM_TIME AS OF DELTA_NUM 12 DATASOURCE_TYPE = 'adqm';

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