Example: Reading From and Writing to an Oracle Table

In this example, you:

  • Create an Oracle user and assign all privileges on the table to the user
  • Create an Oracle table, and insert data into the table
  • Configure the PXF JDBC connector to access the Oracle database
  • Create a PXF readable external table that references the Oracle table
  • Read the data in the Oracle table using PXF
  • Create a PXF writable external table that references the Oracle table
  • Write data to the Oracle table using PXF
  • Read the data in the Oracle table again

Create an Oracle Table

Perform the following steps to create an Oracle table named countries in the schema oracleuser, and grant a user named oracleuser all the necessary privileges:

  1. Identify the host name and port of your Oracle server.

  2. Connect to the Oracle database as the system user:

    $ sqlplus system

  3. Create a user named oracleuser and assign the password mypassword to it:

    > CREATE USER oracleuser IDENTIFIED BY mypassword;

  4. Assign user oracleuser enough privileges to login, create and modify a table:

    > GRANT CREATE SESSION TO oracleuser; 
> GRANT CREATE TABLE TO oracleuser;
> GRANT UNLIMITED TABLESPACE TO oracleuser;
> exit

  5. Log in as user oracleuser:

    $ sqlplus oracleuser

  6. Create a table named countries, insert some data into this table, and commit the transaction:

    > CREATE TABLE countries (country_id int, country_name varchar(40), population float);
> INSERT INTO countries (country_id, country_name, population) values (3, 'Portugal', 10.28);
> INSERT INTO countries (country_id, country_name, population) values (24, 'Zambia', 17.86);
> COMMIT;

Configure the Oracle Connector

You must create a JDBC server configuration for Oracle, download the Oracle driver JAR file to your system, copy the JAR file to the PXF user configuration directory, synchronize the PXF configuration, and then restart PXF.

This procedure will typically be performed by the Greenplum Database administrator.

  1. Download the Oracle JDBC driver and place it under $PXF_BASE/lib of your Greenplum Database master host. If you relocated $PXF_BASE, make sure you use the updated location. You can download a Oracle JDBC driver from your preferred download location. The following example places a driver downloaded from Oracle webiste under $PXF_BASE/lib of the Greenplum Database master:

    $ scp ojdbc10.jar gpadmin@gpmaster:/usr/local/pxf-gp<version>/lib/

  2. Synchronize the PXF configuration, and then restart PXF: 

    gpadmin@gpmaster$ pxf cluster sync
gpadmin@gpmaster$ pxf cluster restart

  3. Create a JDBC server configuration for Oracle as described in Example Configuration Procedure, naming the server directory oracle. The jdbc-site.xml file contents should look similar to the following (substitute your Oracle host system for oracleserverhost, and the value of your Oracle service name for orcl):

    <?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:@oracleserverhost:1521/orcl</value>
        <description>The URL that the JDBC driver can use to connect to the database</description>
    </property>
    <property>
        <name>jdbc.user</name>
        <value>oracleuser</value>
        <description>User name for connecting to the database</description>
    </property>
    <property>
        <name>jdbc.password</name>
        <value>mypassword</value>
        <description>Password for connecting to the database</description>
    </property>
</configuration>

  4. Synchronize the PXF server configuration to the Greenplum Database cluster:

    gpadmin@gpmaster$ pxf cluster sync

Read from the Oracle Table

Perform the following procedure to create a PXF external table that references the countries Oracle table that you created in the previous section, and read the data in the table:

  1. Create the PXF external table specifying the jdbc profile. For example:

    gpadmin=# CREATE EXTERNAL TABLE oracle_countries (country_id int, country_name varchar, population float)
          LOCATION('pxf://oracleuser.countries?PROFILE=jdbc&SERVER=oracle')
          FORMAT 'CUSTOM' (formatter='pxfwritable_import');

  2. Display all rows of the oracle_countries table:

    gpadmin=# SELECT * FROM oracle_countries ;
country_id | country_name | population 
-----------+--------------+------------
         3 | Portugal     |      10.28
        24 | Zambia       |      17.86
(2 rows)

Write to the Oracle Table

Perform the following procedure to insert some data into the countries Oracle table and then read from the table. You must create a new external table for the write operation.

  1. Create a writable PXF external table specifying the jdbc profile. For example:

    gpadmin=# CREATE WRITABLE EXTERNAL TABLE oracle_countries_write (country_id int, country_name varchar, population float)
          LOCATION('pxf://oracleuser.countries?PROFILE=jdbc&SERVER=oracle')
          FORMAT 'CUSTOM' (formatter='pxfwritable_export');

  2. Insert some data into the oracle_countries_write table. For example:

    gpadmin=# INSERT INTO oracle_countries_write VALUES (66, 'Colombia', 50.34);

  3. Use the oracle_countries readable external table that you created in the previous section to view the new data in the countries Oracle table:

    gpadmin=#  SELECT * FROM oracle_countries;
country_id | country_name | population
------------+--------------+------------
         3 | Portugal     |      10.28
        24 | Zambia       |      17.86
        66 | Colombia     |      50.34
(3 rows)