INSERT SELECT
Содержание раздела
- Источники данных
- Поддерживаемые СУБД
- Особенности вставки данных в standalone-таблицы
- Порядок обработки вставляемых записей
- Перезапуск и отмена операций
- Статистика
- Синтаксис
- Варианты ответа
- Ограничения
- Примеры
- Вставка данных во все столбцы логической таблицы
- Вставка данных в некоторые столбцы логической таблицы
- Вставка данных в логическую таблицу вне дельты
- Вставка данных из логической таблицы другой логической БД
- Вставка данных из логической таблицы, размещенной в другом датасорсе типа ADP
- Вставка данных из логического представления в логическую таблицу
- Заполнение столбца логической таблицы данными другой таблицы
- Вставка данных из standalone-таблицы в логическую таблицу
- Вставка данных из логической таблицы в standalone-таблицу
- Вставка данных из обычной логической таблицы в партиционированную таблицу
- Вставка данных из обычной логической таблицы в партицию
- Вставка данных из партиционированной таблицы в обычную логическую таблицу
- Перезапуск операции по вставке записей
Поддерживается в версиях: 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.
Запрос вставляет записи в указанную таблицу — логическую таблицу, прокси-таблицу или 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-таблицы так:
- если в таблице уже есть запись с таким значением первичного ключа, как в результатах SELECT-подзапроса, результат зависит от типа датасорса — возможна ошибка или вставка записи с дублирующимся ключом;
- если такой записи нет или первичный ключ таблицы не задан, вставляется новая запись.
Перезапуск и отмена операций
Кластер с кворумом нод автоматически отменяет сбойные операции записи при типовых сбоях, исключая необходимость ручного вмешательства.
Возможности по ручному управлению операциями описаны в разделе Управление операциями записи.
Статистика
Запросы 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 («Выполняется») в логической таблице. После него должна следовать копия исходного запроса, создавшего перезапускаемую операцию.
Если ключевое слово не указано, создается новая операция записи.
Варианты ответа
В ответе возвращается:
- объект 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;