Use PXF JDBC connector to read and write data between Greengage DB and Oracle Database

Anton Monakov

Contents

Create the source Oracle Database table
Configure the JDBC connector
Create a readable external table
Create a writable external table
Set parallel query session parameters

This topic describes how to configure and use the PXF JDBC connector for reading and writing data in Oracle Database by using external tables and provides practical examples. To try them out, make sure you have an Oracle Database server running and accessible in addition to the Greengage DB server.

Prerequisites

To try out the practical examples, connect to the Greengage DB master host as gpadmin using psql as described in Connect to Greengage DB via psql. Then create the customers test database and connect to it:

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

To be able to create an external table using the PXF protocol, enable the PXF extension in the database as described in Register PXF in a database in PXF documentation:

CREATE EXTENSION pxf;

Connect to Oracle Database as the system user:
```
$ sqlplus system
```
Create a user named pxfuser and assign the password password to it:
```
CREATE USER pxfuser IDENTIFIED BY password;
```
Assign the created pxfuser user the privileges to connect to the database and create and modify tables. Then disconnect from the database:
```
GRANT CREATE SESSION TO pxfuser;
GRANT CREATE TABLE TO pxfuser;
GRANT UNLIMITED TABLESPACE TO pxfuser;
exit
```
Connect to Oracle Database as the pxfuser user:
```
$ sqlplus pxfuser
```
When prompted, provide the configured password password.

Create a table named customers, insert test data into it, and commit the transaction:

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;

To have PXF connect to Oracle Database, you need to create a JDBC server configuration for Oracle Database as described in Configure a PXF server in PXF documentation and then synchronize it to the Greengage DB cluster.

On the Greengage DB master host, log in as gpadmin.
Download the Oracle JDBC driver that is suitable for the version of Oracle Database you are using from the Oracle website and place it in the $PXF_BASE/lib directory:
```
$ cd $PXF_BASE/lib
$ curl -L -O https://download.oracle.com/otn-pub/otn_software/jdbc/23262/ojdbc17.jar
```
Go to the $PXF_BASE/servers directory and create a JDBC server configuration directory (for example, named oracle):
```
$ cd $PXF_BASE/servers
$ mkdir oracle
```
Copy the jdbc-site.xml server configuration template file from the $PXF_HOME/templates/ directory to the created oracle directory:
```
$ cd $PXF_BASE/servers/oracle
$ cp $PXF_HOME/templates/jdbc-site.xml $PXF_BASE/servers/oracle
```

In the jdbc-site.xml file, provide the following configuration specifying jdbc.driver, jdbc.url, jdbc.user, and 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>

If necessary, you can adjust the parallel query execution session parameters as described in Set parallel query session parameters.

Synchronize the PXF configuration between the Greengage DB cluster hosts, and then restart PXF:
```
$ pxf cluster sync
$ pxf cluster restart
```

On the Greengage DB master host, create the readable external table that reads data from the customers table in Oracle Database. In the LOCATION clause, specify the PXF jdbc profile and the server configuration. In the FORMAT clause, specify pxfwritable_import, which is the built-in custom formatter function for read operations:
```
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');
```

Query the created external table:

SELECT * FROM customers_r;

The output should look as follows:

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

On the Greengage DB master host, create the writable external table that writes data to the customers table in Oracle Database. In the LOCATION clause, specify the PXF jdbc profile and the server configuration. In the FORMAT clause, specify pxfwritable_export, which is the built-in custom formatter function for write operations:
```
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');
```

Insert some data into the customers_w table:

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

Connect to Oracle Database and query the source customers table:

SELECT * FROM customers;

The output should look as follows:

 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 recognizes certain Oracle session parameters that control parallel query execution and sets them before running a query. These parameters are specified via the properties set in the jdbc-site.xml JDBC server configuration file.

The Oracle Database parallel query execution properties are named as follows:

jdbc.session.property.alter_session_parallel.<n>

where <n> is an ordinal number that identifies a session parameter setting, for example, jdbc.session.property.alter_session_parallel.1. You may specify multiple property settings, where <n> is unique in each case.

A value specified for an Oracle parallel query execution property must conform to the following format:

<action>.<statement_type>[.<degree_of_parallelism>]

where:

Keyword Values / Description

Keyword	Values / Description
<action>	`enable`, `disable`, `force`
<statement_type>	`query`, `ddl`, `dml`
<degree_of_parallelism>	The integer number of parallel sessions that you can force when `<action>` is set to `force`. For other `<action>` values, the setting is ignored

enable, disable, force

<statement_type>

query, ddl, dml

<degree_of_parallelism>

The integer number of parallel sessions that you can force when <action> is set to force. For other <action> values, the setting is ignored

The example parallel query execution property settings in the jdbc-site.xml configuration file look as follows:

<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>

With this configuration, PXF runs the following commands before submitting the query to Oracle Database:

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

For more information about parallel query execution in Oracle Database, refer to the Oracle documentation.

MySQL

Text

Use PXF JDBC connector to read and write data between Greengage DB and Oracle Database

Create the source Oracle Database table

Configure the JDBC connector

Create a readable external table

Create a writable external table

Set parallel query session parameters