Многотабличная вставка данных в Hive

Константин Алпашкин

Hive поддерживает многотабличную вставку данных, что позволяют записывать данные в несколько таблиц Hive или в несколько HDFS-файлов одновременно. Это эффективный с точки зрения оптимизации прием, поскольку исходная таблица сканируется лишь один раз, а полученные данные используются для записи в несколько таблиц/файлов.

Тестовая база данных

Для демонстрации примеров в данной статье используется Hive-таблица transactions, структура которой описана ниже.

SELECT * FROM transactions;
+----------------------+----------------------+--------------------------+------------------------+
| transactions.txn_id  | transactions.acc_id  | transactions.txn_amount  | transactions.txn_date  |
+----------------------+----------------------+--------------------------+------------------------+
| 1                    | 1002                 | 10.00                    | 2023-01-01             |
| 8                    | 1003                 | 50.00                    | 2023-01-01             |
| 3                    | 1002                 | 30.00                    | 2023-01-02             |
| 4                    | 1001                 | 100.50                   | 2023-01-02             |
| 2                    | 1002                 | 20.00                    | 2023-01-03             |
| 6                    | 1001                 | 200.50                   | 2023-01-03             |
| 7                    | 1003                 | 50.00                    | 2023-01-03             |
| 5                    | 1001                 | 150.50                   | 2023-01-04             |
| 9                    | 1003                 | 75.00                    | 2023-01-04             |
+----------------------+----------------------+--------------------------+------------------------+
SQL для создания таблицы

 
Для создания и наполнения тестовой таблицы Hive выполните следующий SQL с помощью /bin/beeline.

CREATE DATABASE IF NOT EXISTS test_multi_inserts;
USE test_multi_inserts;

DROP TABLE IF EXISTS transactions;

CREATE TABLE transactions(
    txn_id int,
    acc_id int,
    txn_amount decimal(10,2),
    txn_date date)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

INSERT INTO transactions VALUES
(1, 1002, 10.00, '2023-01-01'),
(2, 1002, 20.00, '2023-01-03'),
(3, 1002, 30.00, '2023-01-02'),
(4, 1001, 100.50, '2023-01-02'),
(5, 1001, 150.50, '2023-01-04'),
(6, 1001, 200.50, '2023-01-03'),
(7, 1003, 50.00, '2023-01-03'),
(8, 1003, 50.00, '2023-01-01'),
(9, 1003, 75.00, '2023-01-04');

Синтаксис

Существует две формы синтаксиса многотабличной вставки, оба варианта показаны ниже.

FROM <src_tbl>
INSERT OVERWRITE
    (TABLE <target_tbl> [PARTITION(<partition_spec>)] | DIRECTORY [LOCAL] '<path/to/hdfs>')
<SELECT ... >


INSERT OVERWRITE
    (TABLE <target_tbl> [PARTITION(<partition_spec>)] | DIRECTORY [LOCAL] '<path/to/hdfs')
<SELECT ... FROM <src_tbl>. ... >

Где:

  • <src_tbl> — исходная Hive-таблица для чтения данных.

  • <SELECT …​ > — предложение SELECT для выборки данных из <src_tbl>.

  • <target_tbl> — целевая таблица, в которую будут записаны данные. Целевые таблицы не создаются автоматически и должны быть предварительно созданы вручную.

  • <partition_spec> — указывает партицию таблицы Hive, в которую необходимо записать данные.

  • <path/to/hdfs> — путь к HDFS-файлу, в который необходимо записать данные. Если указан относительный путь, Hive сохраняет файлы внутри HDFS-директории /user/hive/. С помощью ключевого слова LOCAL можно записать данные в локальной файловой системе.

Примеры

Запись в несколько таблиц Hive

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

ПРИМЕЧАНИЕ
Целевые таблицы для записи должны быть созданы вручную перед выполнением INSERT. 
CREATE TABLE max_txn_per_acc (account integer, max_txn_value decimal(10,2));
CREATE TABLE min_txn_per_acc (account integer, min_txn_value decimal(10,2));

FROM transactions
    INSERT INTO TABLE max_txn_per_acc
        SELECT acc_id, MAX(txn_amount)
        GROUP BY acc_id
    INSERT INTO TABLE min_txn_per_acc
        SELECT acc_id, MIN(txn_amount)
        GROUP BY acc_id;

Для проверки результатов вставки используйте запросы:

SELECT * FROM max_txn_per_acc;
SELECT * FROM min_txn_per_acc;

Результат:

SELECT * FROM max_txn_per_acc;
+--------------------------+--------------------------------+
| max_txn_per_acc.account  | max_txn_per_acc.max_txn_value  |
+--------------------------+--------------------------------+
| 1001                     | 200.50                         |
| 1002                     | 30.00                          |
| 1003                     | 75.00                          |
+--------------------------+--------------------------------+

SELECT * FROM min_txn_per_acc;
+--------------------------+--------------------------------+
| min_txn_per_acc.account  | min_txn_per_acc.min_txn_value  |
+--------------------------+--------------------------------+
| 1001                     | 100.50                         |
| 1002                     | 10.00                          |
| 1003                     | 50.00                          |
+--------------------------+--------------------------------++

Запись в партиции

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

CREATE TABLE txns_partitioned_acc_date(txn_id int, txn_amount decimal(10, 2))
PARTITIONED BY (account_id int, txn_date date);

FROM transactions txns
    INSERT OVERWRITE TABLE txns_partitioned_acc_date
    PARTITION(account_id='1001', txn_date='2023-01-02')
SELECT txn_id, txn_amount
WHERE txns.acc_id=1001 AND txns.txn_date='2023-01-02';

SELECT * FROM txns_partitioned_acc_date;

Результат:

+-----------------------------------+---------------------------------------+---------------------------------------+-------------------------------------+
| txns_partitioned_acc_date.txn_id  | txns_partitioned_acc_date.txn_amount  | txns_partitioned_acc_date.account_id  | txns_partitioned_acc_date.txn_date  |
+-----------------------------------+---------------------------------------+---------------------------------------+-------------------------------------+
| 4                                 | 100.50                                | 1001                                  | 2023-01-02                          |
+-----------------------------------+---------------------------------------+---------------------------------------+-------------------------------------+

По умолчанию в предложении PARTITION(<spec>) должна быть указана минимум одна статическая партиция (PARTITION(account_id='1001') как в примере выше). Чтобы использовать динамические партиции при многотабличной вставке (например, PARTITION('acount_id')) и тем самым позволить Hive автоматически определять необходимую партицию для записи, установите следующие параметры:

SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;

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

CREATE TABLE txns_partitioned_acc(txn_amount decimal(10, 2))
PARTITIONED BY (account_id int);

SET hive.exec.dynamic.partition.mode=nonstrict;

FROM transactions txns
    INSERT OVERWRITE TABLE txns_partitioned_acc
    PARTITION(account_id)
SELECT txn_amount, acc_id;

SELECT * FROM txns_partitioned_acc;

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

+----------------------------------+----------------------------------+
| txns_partitioned_acc.txn_amount  | txns_partitioned_acc.account_id  |
+----------------------------------+----------------------------------+
| 100.50                           | 1001                             |
| 150.50                           | 1001                             |
| 200.50                           | 1001                             |
| 10.00                            | 1002                             |
| 20.00                            | 1002                             |
| 30.00                            | 1002                             |
| 50.00                            | 1003                             |
| 50.00                            | 1003                             |
| 75.00                            | 1003                             |
+----------------------------------+----------------------------------+

Запись в несколько файлов

Hive позволяет записывать данные из таблицы в несколько HDFS/локальных файлов в рамках одного запроса. Ниже показана запись в несколько HDFS-файлов.

FROM transactions
    INSERT OVERWRITE DIRECTORY 'multi-insert-demo/transactions_20230101'
    SELECT acc_id, txn_amount WHERE txn_date='2023-01-01'
    INSERT OVERWRITE DIRECTORY 'multi-insert-demo/transactions_20230102'
    SELECT acc_id, txn_amount WHERE txn_date='2023-01-02';

Для проверки HDFS-директории на наличие новых файлов используйте команду:

$ hdfs dfs -ls /user/hive/multi-insert-demo

В выводе отображаются две новых HDFS-директории с данными каждой выборки.

...
drwxr-xr-x   - hive hadoop          0 2023-11-29 10:51 /user/hive/multi-insert-demo/transactions_20230101
drwxr-xr-x   - hive hadoop          0 2023-11-29 10:51 /user/hive/multi-insert-demo/transactions_20230102

Для записи в файлы, расположенные в локальной файловой системе, а не в HDFS, используйте ключевое слово LOCAL как показано ниже.

FROM transactions
    INSERT OVERWRITE LOCAL DIRECTORY '/tmp/test.out'
SELECT transactions.*;
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней