Использование коннектора PXF JDBC для чтения и записи данных между Greengage DB и Oracle Database

Антон Монаков

Contents

Создание исходной таблицы Oracle Database
Конфигурирование коннектора JDBC
Создание читающей внешней таблицы
Создание пишущей внешней таблицы
Установка параметров сессии для параллельного выполнения запросов

В этой статье описывается настройка и использование коннектора PXF JDBC для чтения и записи данных в Oracle Database с использованием внешних таблиц, а также приводятся практические примеры. Для их выполнения убедитесь, что у вас работает и доступен сервер Oracle Database в дополнение к серверу Greengage DB.

Предварительные требования

Для выполнения практических примеров подключитесь к мастер-хосту Greengage DB как gpadmin с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql. Затем создайте тестовую базу данных customers и подключитесь к ней:

DROP DATABASE IF EXISTS customers;
CREATE DATABASE customers;
\c customers

Чтобы создать внешнюю таблицу с использованием протокола PXF, предварительно зарегистрируйте в БД расширение PXF, как описано в разделе Регистрация PXF в БД документации PXF:

CREATE EXTENSION pxf;

Подключитесь к Oracle Database как пользователь system:
```
$ sqlplus system
```
Создайте пользователя pxfuser и назначьте ему пароль password:
```
CREATE USER pxfuser IDENTIFIED BY password;
```
Выдайте созданному пользователю pxfuser привилегии на подключение к базе данных, создание и изменение таблиц. Затем отключитесь от базы данных:
```
GRANT CREATE SESSION TO pxfuser;
GRANT CREATE TABLE TO pxfuser;
GRANT UNLIMITED TABLESPACE TO pxfuser;
exit
```
Подключитесь к Oracle Database как пользователь pxfuser:
```
$ sqlplus pxfuser
```
При появлении запроса введите заданный пароль password.

Создайте таблицу с именем customers, вставьте в нее тестовые данные и подтвердите транзакцию:

CREATE TABLE customers (first_name VARCHAR(20), last_name VARCHAR(20), email VARCHAR(30));
INSERT INTO customers (first_name, last_name, email) VALUES ('John','Doe','john.doe@example.com');
INSERT INTO customers (first_name, last_name, email) VALUES ('Jane','Smith','jane.smith@example.com');
COMMIT;

Для подключения к базе данных Oracle Database с помощью PXF необходимо создать конфигурацию сервера JDBC, как описано в статье Настройка PXF-сервера документации PXF, а затем синхронизировать ее с кластером Greengage DB.

На мастер-хосте Greengage DB войдите под пользователем gpadmin.
Загрузите драйвер JDBC, подходящий для используемой версии Oracle Database, с сайта Oracle и поместите его в каталог $PXF_BASE/lib:
```
$ cd $PXF_BASE/lib
$ curl -L -O https://download.oracle.com/otn-pub/otn_software/jdbc/23262/ojdbc17.jar
```
Измените текущий каталог на $PXF_BASE/servers и создайте каталог конфигурации сервера JDBC (например, с именем oracle):
```
$ cd $PXF_BASE/servers
$ mkdir oracle
```
Скопируйте файл шаблона конфигурации сервера jdbc-site.xml из каталога $PXF_HOME/templates/ в созданный каталог oracle:
```
$ cd $PXF_BASE/servers/oracle
$ cp $PXF_HOME/templates/jdbc-site.xml $PXF_BASE/servers/oracle
```

В файле jdbc-site.xml укажите следующую конфигурацию с помощью параметров jdbc.driver, jdbc.url, jdbc.user и jdbc.password:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <property>
        <name>jdbc.driver</name>
        <value>oracle.jdbc.driver.OracleDriver</value>
        <description>Class name of the JDBC driver</description>
    </property>
    <property>
        <name>jdbc.url</name>
        <value>jdbc:oracle:thin:@oraclehost:1521/XE</value>
        <description>The URL that the JDBC driver can use to connect to the database</description>
    </property>
    <property>
        <name>jdbc.user</name>
        <value>pxfuser</value>
        <description>User name for connecting to the database</description>
    </property>
    <property>
        <name>jdbc.password</name>
        <value>password</value>
        <description>Password for connecting to the database</description>
    </property>
</configuration>

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

Синхронизируйте конфигурацию между хостами кластера Greengage DB, а затем перезапустите PXF:
```
$ pxf cluster sync
$ pxf cluster restart
```

На мастер-хосте Greengage DB создайте читающую внешнюю таблицу, ссылающуюся на таблицу customers в Oracle Database. В выражении LOCATION укажите профиль PXF jdbc и конфигурацию сервера. В выражении FORMAT укажите pxfwritable_import — встроенную кастомную функцию форматирования для операций чтения:
```
CREATE EXTERNAL TABLE customers_r (
    first_name TEXT,
    last_name TEXT,
    email TEXT
    )
    LOCATION ('pxf://pxfuser.customers?PROFILE=jdbc&SERVER=oracle')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
```

Выполните запрос к созданной внешней таблице:

SELECT * FROM customers_r;

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

 first_name | last_name |         email
------------+-----------+------------------------
 John       | Doe       | john.doe@example.com
 Jane       | Smith     | jane.smith@example.com
(2 rows)

На мастер-хосте Greengage DB создайте пишущую внешнюю таблицу, ссылающуюся на ранее созданную таблицу customers в Oracle Database. В выражении LOCATION укажите профиль PXF jdbc и конфигурацию сервера. В выражении FORMAT укажите pxfwritable_export — встроенную кастомную функцию форматирования для операций записи:
```
CREATE WRITABLE EXTERNAL TABLE customers_w (
    first_name TEXT,
    last_name TEXT,
    email TEXT
    )
    LOCATION ('pxf://pxfuser.customers?PROFILE=jdbc&SERVER=oracle')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');
```

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

INSERT INTO customers_w (first_name, last_name, email)
VALUES ('Bob', 'Brown', 'bob.brown@example.com'),
       ('Alice', 'Green', 'alice.green@example.com');

Подключитесь к Oracle Database и выполните запрос к таблице customers:

SELECT * FROM customers;

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

 FIRST_NAME           LAST_NAME            EMAIL
-------------------- -------------------- ------------------------------
Alice                Green                alice.green@example.com
Bob                  Brown                bob.brown@example.com
John                 Doe                  john.doe@example.com
Jane                 Smith                jane.smith@example.com

PXF поддерживает ряд параметров сессии Oracle Database, которые позволяют контролировать параллельное исполнение запросов, и автоматически устанавливает их перед выполнением запроса. Эти параметры указываются в виде свойств в файле конфигурации сервера JDBC.

Настройки параметров сессии для параллельного выполнения запросов Oracle Database называются следующим образом:

jdbc.session.property.alter_session_parallel.<n>

где <n> — порядковый номер настройки параметра сессии, например, jdbc.session.property.alter_session_parallel.1. Можно указать несколько настроек параметров сессии с уникальным номером <n> для каждой настройки.

Значение параметра сессии для параллельного выполнения запросов Oracle Database должно соответствовать следующему формату:

<действие>.<тип выражения>[.<степень параллелизма>]

где:

Ключевое слово Значения / Описание

Ключевое слово	Значения / Описание
<действие>	`enable`, `disable`, `force`
<тип выражения>	`query`, `ddl`, `dml`
<степень параллелизма>	Целое число, обозначающее количество параллельных сессий, которые можно принудительно задать при установке параметру `<действие>` значения `force`. Для других значений параметра `<действие>` настройка игнорируется

<действие>

enable, disable, force

<тип выражения>

query, ddl, dml

<степень параллелизма>

Целое число, обозначающее количество параллельных сессий, которые можно принудительно задать при установке параметру <действие> значения force. Для других значений параметра <действие> настройка игнорируется

Пример настроек параметров сессии для параллельного выполнения запросов Oracle Database в файле конфигурации сервера JDBC jdbc-site.xml выглядит следующим образом:

<property>
    <name>jdbc.session.property.alter_session_parallel.1</name>
    <value>force.query.4</value>
</property>
<property>
    <name>jdbc.session.property.alter_session_parallel.2</name>
    <value>disable.ddl</value>
</property>
<property>
    <name>jdbc.session.property.alter_session_parallel.3</name>
    <value>enable.dml</value>
</property>

При применении данной конфигурации PXF выполняет следующие команды перед отправкой запроса к Oracle Database:

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
ALTER SESSION DISABLE PARALLEL DDL;
ALTER SESSION ENABLE PARALLEL DML;

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

MySQL

Текст

Использование коннектора PXF JDBC для чтения и записи данных между Greengage DB и Oracle Database

Создание исходной таблицы Oracle Database

Конфигурирование коннектора JDBC

Создание читающей внешней таблицы

Создание пишущей внешней таблицы

Установка параметров сессии для параллельного выполнения запросов