INSERT SELECT

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

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

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

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

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

Вставка данных возможна в ADB, ADQM и ADP.

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

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

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

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

• источник данных и данные целевой таблицы находятся в одной СУБД хранилища,
• источник данных находится в ADB, а данные целевой таблицы — в ADB и (или) ADQM.

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

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

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

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

Записи выбираются из одной СУБД хранилища:

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

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

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

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

Синтаксис

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

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-подзапрос для выбора данных.

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

Ключевое слово перезапускает обработку незавершенной операции записи, созданной запросом INSERT SELECT. Пример запроса см. ниже. Список незавершенных операций можно получить с помощью запроса GET_WRITE_OPERATIONS.

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

Ключевое слово RETRY недоступно в запросах к standalone-таблицам.

Ограничения

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

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

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

• Вставка данных в ADG недоступна.

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

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

Ограничения столбцов

• Типы вставляемых значений должны соответствовать типам данных столбцов в целевой таблице.

Ограничения ключевых слов

• Ключевое слово RETRY недоступно в запросах к standalone-таблицам.

Примеры

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

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

-- создание логической таблицы sales_july_2021 с данными о продажах за июль 2021 (с размещением данных в ADB)
CREATE TABLE sales_july_2021 (
id INT NOT NULL,
transaction_date TIMESTAMP NOT NULL,
product_code VARCHAR(256) NOT NULL,
product_units INT NOT NULL,
store_id INT 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 с выборкой из таблицы stores (с размещением данных в ADQM)
CREATE TABLE current_stores (
  id INT 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;

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

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

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

-- создание логической таблицы sales в логической БД marketing_new (с размещением данных в ADP)
CREATE TABLE sales (
id INT NOT NULL,
transaction_date TIMESTAMP NOT NULL,
product_code VARCHAR(256) NOT NULL,
product_units INT NOT NULL,
store_id INT 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 1234 AND 4567 DATASOURCE_TYPE = '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 INT 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 INT NOT NULL,
transaction_date TIMESTAMP NOT NULL,
product_code VARCHAR(256),
product_units INT,
store_id INT,
description VARCHAR(256),
store_address VARCHAR(256),
PRIMARY KEY (id)
) DISTRIBUTED BY (id)
DATASOURCE_TYPE (adb, adqm);

-- открытие новой (горячей) дельты
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 INT NOT NULL,
  client_id INT 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-таблицей ADQM
CREATE WRITABLE EXTERNAL TABLE marketing.sales_ext_write_adqm (
  id INT NOT NULL,
  transaction_date TIMESTAMP NOT NULL,
  product_code VARCHAR(256) NOT NULL,
  product_units INT NOT NULL,
  store_id INT NOT NULL,
  description VARCHAR(256),
  PRIMARY KEY (id)
)
DISTRIBUTED BY (id)
LOCATION 'core:adqm://dtm__marketing.sales'
OPTIONS ('auto.create.table.enable=true');

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

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

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