Примеры использования Sqoop для импорта и экспорта данных между PostgreSQL и Hive

Елена Костюченко

Содержание

Обзор
Коннекторы PostgreSQL
- Коннектор PostgreSQL
- Коннектор PostgreSQL direct
Требования
Подготовка
Общие аргументы
Перенос данных из PostgreSQL в Hive
Перенос данных из Hive в PostgreSQL

Обзор

Сервис Sqoop обеспечивает упрощенную передачу данных между Hadoop, некоторыми реляционными базами данных и другими хранилищами. Он предоставляет следующие команды CLI с рядом опций:

sqoop import — копирует отдельную таблицу из базы данных с помощью MapReduce или YARN. Каждая строка таблицы представлена как отдельная запись в HDFS. Записи могут храниться в виде текстовых файлов (по одной записи в строке) или в двоичном представлении в виде файлов Avro или SequenceFiles. При импорте данных в Hive Sqoop параллельно считывает исходную таблицу построчно и копирует ее в целевую таблицу в Hive.
sqoop export — параллельно считывает набор текстовых файлов с разделителями из HDFS, используя MapReduce или YARN, представляет их в виде записей и вставляет их как новые строки в таблицу целевой базы данных.

Синтаксис команд выглядит следующим образом:

$ sqoop import [generic-args] [import-args]
$ sqoop export [generic-args] [import-args]

В этом руководстве описано, как использовать Sqoop для передачи данных между PostgreSQL и Hive.

Коннекторы PostgreSQL

Sqoop поддерживает несколько коннекторов PostgreSQL, которые предоставляют дополнительные возможности для импорта/экспорта данных: коннектор PostgreSQL по умолчанию, коннектор PostgreSQL direct и коннектор pg_bulkload. Коннектор pg_bulkload не поставляется в ADPG.

Коннектор PostgreSQL

По умолчанию команды импорта/экспорта Sqoop работают с таблицами, расположенными в общедоступных (public) схемах. Коннектор PostgreSQL позволяет выполнять операции импорта/экспорта данных, которые хранятся в других схемах.

Чтобы выбрать схему, необходимо использовать опцию --schema и указать имя желаемой схемы.

Коннектор PostgreSQL direct

Этот коннектор позволяет быстрее копировать данные с помощью команды PostgreSQL COPY.

Чтобы использовать коннектор PostgreSQL direct, укажите опцию --direct в запросе на импорт или экспорт.

При импорте из PostgreSQL с использованием этой опции можно разбить данные на файлы определенного размера. В этом случае размер файла необходимо указать с помощью опции --direct-split-size.

Коннектор PostgreSQL direct добавляет также другие опции, описанные в таблице ниже.

Дополнительные аргументы PostgreSQL direct
Опция	Описание
--direct	Опция, позволяющая использовать для копирования данных команду PostgreSQL `COPY`
--direct-split-size	Позволяет разбивать данные на отдельные файлы определенного размера
--boolean-true-string	Строка, которую неоходимо использовать для кодирования значения `TRUE` в столбцах, содержащих логические значения
--boolean-false-string	Строка, которую неоходимо использовать для кодирования значения `FALSE` в столбцах, содержащих логические значения

ПРИМЕЧАНИЕ

Коннектор PostgreSQL direct не поддерживает импорт столбцов больших объектов (BLOB и CLOB), представлений (views) и импорт в HBase и Accumulo.

Требования

При импорте или экспорте данных с помощью Sqoop обратите внимание на:

Типы данных

Убедитесь, что данные, импортированные из PostgreSQL, не содержат типы данных, которые не поддерживаются в Hive.
Имена таблиц и столбцов

Если в именах таблиц или столбцов содержатся символы, которые не допустимы идентификаторами Java, Avro или Parquet, Sqoop преобразует эти символы в _. Например, если имя столбца начинается с символа _, то он будет преобразован в __.

Подготовка

Примеры команд в этой статье были выполнены в Arenadata Postgres (ADPG), системе управления реляционными базами данных на основе PostgreSQL, а также с использованием сервисов Sqoop и Hive, доступных в Arenadata Hadoop (ADH).

Подготовка ADPG для тестовых примеров

На хосте ADPG запустите клиент psql от имени пользователя по умолчанию (postgres), используя следующие команды:
```
$ sudo su - postgres
$ psql
```
Теперь вы можете вводить команды в psql для работы с базами данных и таблицами PostgreSQL:
```
psql (14.1)
Type "help" for help.

postgres=#
```
Создайте пользователя для подключения к ADPG (в демонстрационных целях предоставьте ему права суперпользователя):
```
CREATE ROLE sqoop SUPERUSER LOGIN PASSWORD '<пароль-пользователя-sqoop>';
```
Создайте тестовую базу данных:
CREATE DATABASE books_store;
Переключитесь на эту базу данных:
\c books_store
Результат выполнения команды:
You are now connected to database "books_store" as user "postgres". books_store=#

Создайте таблицу:

CREATE TABLE books \
(id SERIAL PRIMARY KEY, \
title VARCHAR(255) NOT NULL, \
author VARCHAR(255) NOT NULL, \
public_year SMALLINT NULL);

Добавьте тестовые данные в таблицу:

INSERT INTO books(title, author, public_year) VALUES
    ('Crime and Punishment','Fyodor Dostoevsky',1866),
    ('Frankenstein','Mary Shelley',1823),
    ('The Master and Margarita','Mikhail Bulgakov',1966),
    ('Nineteen Eighty-Four','George Orwell',1949),
    ('Dracula','Bram Stoker',NULL);

Чтобы разрешить пользователю sqoop подключаться к тестовой базе данных, добавьте следующую запись в файл pg_hba.conf (для ADPG это можно сделать в интерфейсе ADCM — подробнее в статье Аутентификация по паролю):
```
host books_store sqoop <пароль-пользователя-sqoop> password
```
здесь <пароль-пользователя-sqoop> — это адрес хоста Sqoop. Описание поля address и других полей в файле pg_hba.conf можно найти в статье The pg_hba.conf File.

ПРИМЕЧАНИЕ

Обзор по работе с PostgreSQL-таблицами в psql можно посмотреть в статье документации ADPG Пример работы с таблицами через psql.

Подготовка Hive для тестовых примеров

На хосте с компонентом Hive Client выполните:
```
$ sudo -u hive hive
```
В конце вывода появится приглашение Hive CLI:
```
hive>
```

Создайте таблицу:

CREATE TABLE books(
    id INT,
    title STRING,
    author STRING,
    public_year INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

Пример вывода:

OK
Time taken: 0.074 seconds

ПРИМЕЧАНИЕ

Обзор по работе с Hive можно посмотреть в статье Работа с таблицами Hive.

Подготовка Sqoop для тестовых примеров

Установите Sqoop, следуя инструкциям из статьи Начало работы с Sqoop.

Проверьте подключение к тестовой базе данных в PostgreSQL:

$ sqoop list-tables --connect jdbc:postgresql://127.0.0.1:5432/books_store --username <имя-пользователя> -P

Вывод будет похож на этот:

2024-05-29 11:47:29,608 (main) [INFO - org.apache.sqoop.Sqoop.<init>(Sqoop.java:94)] Running Sqoop version: 1.4.7_arenadata2
2024-05-29 11:47:29,743 (main) [INFO - org.apache.sqoop.manager.SqlManager.initOptionDefaults(SqlManager.java:98)] Using default fetchSize of 1000
books

Общие аргументы

Общие аргументы для команд импорта и экспорта описаны в таблице ниже.

Общие аргументы для команд import/export
Опция	Описание
--connect	Строка JDBC для подключения к базе данных, содержащая полное имя хоста или IP-адрес базы данных, к которым имеют доступ все хосты в кластере. Не используйте адрес `localhost` в строке подключения, поскольку она будет использоваться на нодах TaskTracker в MapReduce. В случае, если будет указан `localhost`, ноды могут подключаться к разным БД или не подключаться вовсе
--connection-manager	Класс менеджера соединений, который нужно использовать
--driver	Используемый класс драйвера JDBC. Sqoop выбирает драйвер по умолчанию на основе предоставленного JDBC URI, но с помощью этой опции можно указать другой драйвер
--hadoop-mapred-home	Опция для указания произвольного `$HADOOP_MAPRED_HOME`
--password-file	Путь к файлу, содержащему пароль для аутентификации. Вы можете сохранить пароль в файле в домашней директории users с разрешениями `400` и указать путь к этому файлу в этой опции. Sqoop прочитает все содержимое файла и безопасно передаст его в MapReduce в качестве пароля. Файл, содержащий пароль, может находиться либо в локальной файловой системе, либо в HDFS. Например: `--password-file ${user.home}/.password` В пароль включаются любые символы пробелов и разделители, такие как символы новой строки, которые добавляются по умолчанию большинством текстовых редакторов
-P	Эта опция указывает, что пароль необходимо вводить в консоли. Это менее безопасный вариант, чем использование файла паролей
--password	Пароль аутентификации для подключения к БД. Этот способ предоставления учетных данных небезопасен, поскольку другие пользователи могут прочитать пароль из аргументов командной строки
--username	Имя пользователя, имеющего доступ к базе данных
-verbose	Включает дополнительную информацию в вывод команды. Это может быть полезно при отладке, но команды импорта/экспорта уже по умолчанию предоставляют подробный вывод
--connection-param-file	Файл, содержащий параметры соединения JDBC. Эта функция полезна, когда команда для подключения слишком длинная или для более быстрого переключения между разными соединениями
--relaxed-isolation	Устанавливает изоляцию транзакции подключения как `READ UNCOMMITTED` для mapper-задач. По умолчанию Sqoop использует изоляцию транзакций `READ COMMITTED`

Полный список опций для команд доступен в справочных материалах:

Чтобы получить информацию об этих командах в консоли, используйте опцию --help для нужной команды.

Перенос данных из PostgreSQL в Hive

Создание или перезапись таблиц

Чтобы копировать данные из PostgreSQL в Hive, используйте команду:

$ sqoop import \
--connect jdbc:postgresql://<хост-postgres>:5432/<имя-бд> \
--username <имя-пользователя> -P \
--table <имя-таблицы> \
--hive-import

Здесь:

<хост-postgres> — IP-адрес хоста БД;
<имя-бд> — имя базы данных, из которой необходимо копировать данные;
<имя-пользователя> — имя пользователя, имеющего доступ к нужной таблице;
<имя-таблицы> — имя таблицы, которую необходимо перенести в Hive.

Опция --hive-import позволяет сохранять данные в Hive.

Например:

$ sqoop import \
--connect jdbc:postgresql://127.0.0.1:5432/books_store \
--username sqoop -P \
--table books \
--hive-import

Успешный перенос данных закончится строкой:

[INFO - org.apache.sqoop.hive.HiveImport.importTable(HiveImport.java:212)] Hive import complete.

Когда Sqoop импортирует данные в Hive, он создает таблицу с тем же именем, что и исходная таблица. Если такая таблица уже существует, Sqoop добавит данные в нее.

Вы можете указать другое имя для таблицы, используя опцию --hive-table.

Например:

$ sqoop import \
--connect jdbc:postgresql://127.0.0.1:5432/books_store \
--username sqoop -P \
--table books \
--hive-import \
--hive-table books2

Чтобы перезаписать данные в целевой таблице, используйте опцию --hive-overwrite.

Например:

$ sqoop import \
--connect jdbc:postgresql://127.0.0.1:5432/books_store \
--username sqoop -P \
--table books \
--hive-import \
--hive-overwrite

Чтобы увидеть импортированные данные, выполните в Hive:

SELECT * FROM books;

Пример вывода:

OK
1       Crime and Punishment    Fyodor Dostoevsky       1866
2       Frankenstein    Mary Shelley    1823
3       The Master and Margarita        Mikhail Bulgakov        1966
4       Nineteen Eighty-Four    George Orwell   1949
5       Dracula Bram Stoker     NULL
Time taken: 2.255 seconds, Fetched: 5 row(s)

Тестовая таблица, которая используется в этом примере, содержит значение NULL. Sqoop копирует такие значения в Hive как строки NULL. Чтобы указать другое значение, используйте опцию --null-string для строк и опцию --null-non-string для других типов данных.

Например:

$ sqoop import \
--connect jdbc:postgresql://127.0.0.1:5432/books_store \
--username sqoop -P \
--table books \
--hive-import \
--null-non-string 0

Результат:

OK
1       Crime and Punishment    Fyodor Dostoevsky       1866
2       Frankenstein    Mary Shelley    1823
3       The Master and Margarita        Mikhail Bulgakov        1966
4       Nineteen Eighty-Four    George Orwell   1949
5       Dracula Bram Stoker     0

Импорт столбцов

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

Например:

$ sqoop import \
--connect jdbc:postgresql://127.0.0.1:5432/books_store \
--username sqoop -P \
--table books \
--hive-import
--columns "title,author"

Результат:

OK
Crime and Punishment    Fyodor Dostoevsky
Frankenstein    Mary Shelley
The Master and Margarita        Mikhail Bulgakov
Nineteen Eighty-Four    George Orwell
Dracula Bram Stoker
Time taken: 2.168 seconds, Fetched: 5 row(s)

Импорт строк

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

Например:

$ sqoop import \
--connect jdbc:postgresql://127.0.0.1:5432/books_store \
--username sqoop -P \
--table books \
--hive-import
--where "public_year < '1900'"

Приведенная выше команда import сгенерирует выражение вида SELECT <список столбцов> FROM <имя таблицы> с указанным запросом WHERE и скопирует результат запроса в Hive. В приведенном примере будут скопированы только книги, написанные в 19 веке.

Результат:

OK
1       Crime and Punishment    Fyodor Dostoevsky       1866
2       Frankenstein    Mary Shelley    1823
Time taken: 2.07 seconds, Fetched: 2 row(s)

Импорт результатов произвольного запроса

Sqoop также может импортировать результаты произвольного SQL-запроса. Вместо аргументов --table, --columns и --where вы можете передать выражение SQL в опции --query.

При импорте результатов SQL-запроса необходимо:

Указать директорию для данных в HDFS с помощью опции --target-dir.
Включить токен $CONDITIONS в выражение.
Выбрать разделительный столбец для данных с помощью опции --split-by или ограничить количество mapper-задач до 1.

Например:

$ sqoop import \
--connect jdbc:postgresql://127.0.0.1:5432/books_store \
--username sqoop -P \
--hive-import
--query 'SELECT MIN(public_year) FROM books WHERE $CONDITIONS' \
--target-dir /user/hdfs/books2
--split-by id

Результат:

OK
1823
Time taken: 2.051 seconds, Fetched: 1 row(s)

Управление параллельными задачами

При импорте из PostgreSQL каждое задание MapReduce выполняет запрос для получения нужных данных. Вы можете ограничить количество запросов до одного и импортировать результат последовательно, ограничив количество mapper-задач с помощью опции --m 1.

Например:

$ sqoop import \
--connect jdbc:postgresql://127.0.0.1:5432/books_store \
--username sqoop -P \
--hive-import
--query 'SELECT MIN(public_year) FROM books WHERE $CONDITIONS' \
--target-dir /user/hdfs/books2
--m 1

Перенос данных из Hive в PostgreSQL

Для передачи данных из Hive в PostgreSQL нет специальной опции экспорта Hive, но вы можете копировать данные непосредственно из директории хранилища Hive (warehouse) в HDFS.

Таблица, в которую необходимо перенести данные, уже должна быть создана в базе данных и иметь соответствующую структуру.

Синтаксис команды экспорта:

$ sqoop export \
--connect jdbc:postgresql://<хост-postgres>:5432/<имя-бд> \
--username <имя-пользователя> -P \
--table <имя-таблицы> \
--export-dir <hdfs-директория>

Здесь:

<хост-postgres> — IP-адрес хоста с Postgres;
<имя-бд> — имя базы данных, в которую необходимо скопировать данные;
<имя-пользователя> — имя пользователя, имеющего доступ к нужной таблице;
<имя-таблицы> — имя таблицы для экспорта в PostgreSQL;
<hdfs-директория> — директория копируемой таблицы в HDFS.

Например:

$ sqoop export \
--connect jdbc:postgresql://127.0.0.1:5432/books_store \
--username sqoop -P \
--table exportbooks \
--export-dir /apps/hive/warehouse/books

В этом примере данные из файлов таблицы в директории /apps/hive/warehouse/books добавляются в таблицу exportbooks базы данных book_store. Sqoop выполняет серию операций INSERT INTO, не проверяя данные на соответствие структуре целевой таблицы. Если Sqoop попытается вставить строки, которые нарушают ограничения базы данных (например, такое значение ключа уже существует), экспорт завершится с ошибкой.

Как и в команде import, для экспорта данных можно указать отдельные столбцы с помощью опции --columns. Например, --columns "<столбец1,столбец2,столбец3>".

ПРИМЕЧАНИЕ

Для столбцов, которые не включены в параметр --columns, должно быть задано значение по умолчанию или разрешены значения NULL в целевой БД. В противном случае экспорт данных завершится с ошибкой.

Чтобы проверить, правильно ли скопированы данные, выполните в psql:

SELECT * FROM exportbooks;

Пример вывода:

 id |          title           |      author       | public_year
----+--------------------------+-------------------+-------------
  1 | Crime and Punishment     | Fyodor Dostoevsky |        1866
  2 | Frankenstein             | Mary Shelley      |        1823
  3 | The Master and Margarita | Mikhail Bulgakov  |        1966
  4 | Nineteen Eighty-Four     | George Orwell     |        1949
  5 | Dracula                  | Bram Stoker       |
(5 rows)

Более подробно о команде export читайте в документации Sqoop.

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней