INSERT SELECT

Содержание раздела

Источники данных
Поддерживаемые СУБД
Особенности вставки данных в standalone-таблицы
Как работает запрос
Перезапуск и отмена операций
Статистика
Синтаксис
1. Ключевое слово SELECT
2. Ключевое слово RETRY
Варианты ответа
Ограничения
Примеры

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

Запрос вставляет записи в указанную таблицу — логическую таблицу, прокси-таблицу или standalone-таблицу — из другой логической сущности.

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

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

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

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

Источники данных

Источниками данных могут быть следующие сущности и их соединения:

логическая таблица,
логическое представление представление,
материализованное представление,
прокси-таблица,
standalone-таблица.

Данные сущностей выбираются из датасорса:

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

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

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

источник данных в любом датасорсе → приемник данных в том же датасорсе;
источник в датасорсе типа ADB → приемник в датасорсах типа ADP, ADG и (или) ADQM;
источник в датасорсе типа ADP → приемник в любых датасорсах типа ADP и (или) ADG.

Расположением данных логической таблицы можно управлять с помощью запросов CREATE TABLE и DROP TABLE с ключевым словом DATASOURCE_TYPE. Первый запрос позволяет выбрать датасорсы для размещения данных таблицы, второй — удалить данные таблицы из выбранного датасорса.

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

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

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

Данные выбираются в соответствии с указанным SELECT-подзапросом и вставляются в указанные таблицы. При вставке новых записей и обновлении существующих записей все поля, указанные в запросе, заполняются значениями из выборки, а пропущенные поля — значениями по умолчанию.

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

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

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

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

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

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

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

Записи вставляются в прокси-таблицу или standalone-таблицу так:

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

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

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

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

Статистика

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

Синтаксис

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

INSERT INTO [db_name.]table_name SELECT query

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

INSERT INTO [db_name.]table_name (column_list) SELECT query

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

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

Параметры:

db_name

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

table_name

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

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

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

column_list

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

query

SELECT-подзапрос для выбора данных.

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

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

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

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

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

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

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

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

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

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

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

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

Ограничения

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

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

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

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

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

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

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

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

Примеры

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

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

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

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

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

-- открытие новой дельты
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 10 DATASOURCE_TYPE = 'adqm';

-- закрытие дельты
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';

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

-- создание новой логической БД 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_adp1
INSERT INTO marketing.clients_adp2 SELECT * FROM marketing.clients_adp1;

-- закрытие дельты
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;

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

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

-- создание логической таблицы с данными покупок и адресов магазинов, где были совершены покупки 
CREATE TABLE sales_with_address (
id BIGINT NOT NULL,
transaction_date TIMESTAMP NOT NULL,
product_code VARCHAR(256),
product_units BIGINT,
store_id BIGINT,
description VARCHAR(256),
store_address VARCHAR(256),
PRIMARY KEY (id)
) DISTRIBUTED BY (id)
DATASOURCE_TYPE ('adb', 'adg', 'adqm', 'adp');

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

-- вставка данных из таблицы sales (заполнение всех столбцов, кроме store_address)
INSERT INTO sales_with_address (id, transaction_date, product_code, product_units, store_id, description)
SELECT * FROM sales DATASOURCE_TYPE = 'adb';

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

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

--- вставка данных адресов из таблицы stores в те строки, где адрес не заполнен
INSERT INTO sales_with_address
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 stores AS st 
JOIN sales_with_address AS s ON s.store_id = st.id 
WHERE s.store_address IS NULL OR s.store_address = ''
DATASOURCE_TYPE = 'adb';

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

Вставка данных из 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;