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 VMware Tanzu Greenplum platform extension framework (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
For information about controlling parallel execution in Oracle, refer to About Setting Parallel Query Session Parameters located at the end of this topic.
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:
-
Identify the host name and port of your Oracle server.
-
Connect to the Oracle database as the
system
user:$ sqlplus system
-
Create a user named
oracleuser
and assign the passwordmypassword
to it:> CREATE USER oracleuser IDENTIFIED BY mypassword;
-
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
-
Log in as user
oracleuser
:$ sqlplus oracleuser
-
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.
-
Download the Oracle JDBC driver and place it under
$PXF_BASE/clusters/default/lib
of your Greenplum coordinator 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/clusters/default/lib
of the Greenplum coordinator:-
If you did not relocate
$PXF_BASE
, run the following from the Greenplum coordinator host:gpadmin@coordinator$ scp ojdbc10.jar gpadmin@coordinator:/usr/local/pxf-gp<version>/lib/
-
If you relocated
$PXF_BASE
, run the following from the Greenplum coordinator host:gpadmin@coordinator$ scp ojdbc10.jar gpadmin@coordinator:$PXF_BASE/clusters/default/lib/
-
-
Synchronize the PXF configuration, and then restart PXF:
gpadmin@coordinator$ pxf cluster sync gpadmin@coordinator$ pxf cluster restart
-
Create a JDBC server configuration for Oracle as described in Example Configuration Procedure, naming the server directory
oracle
. Thejdbc-site.xml
file contents should look similar to the following (substitute your Oracle host system fororacleserverhost
, and the value of your Oracle service name fororcl
):<?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>
-
Synchronize the PXF server configuration to the Greenplum cluster:
gpadmin@coordinator$ 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 reads the data in the table:
-
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');
-
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.
-
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');
-
Insert some data into the
oracle_countries_write
table. For example:gpadmin=# INSERT INTO oracle_countries_write VALUES (66, 'Colombia', 50.34);
-
Use the
oracle_countries
readable external table that you created in the previous section to view the new data in thecountries
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)
About Setting Oracle Parallel Query Session Parameters
PXF recognizes certain Oracle session parameters that control parallel query execution, and will set these parameters before it runs a query. You specify these session parameters via properties that you set in the jdbc-site.xml
configuration file for the Oracle PXF server.
For more information about parallel query execution in Oracle databases, refer to the Oracle documentation.
PXF names an Oracle parallel query session property as follows:
jdbc.session.property.alter_session_parallel.<n>
<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.
A value that you specify for an Oracle parallel query execution property must conform to the following format:
<action>.<statement_type>[.<degree_of_parallelism>]
where:
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> specifies force . PXF ignores this value for other <action> settings. |
Example parallel query execution property settings in the jdbc-site.xml
configuration file for an Oracle PXF server follow:
<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 it submits the query to the Oracle database:
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
ALTER SESSION DISABLE PARALLEL DDL;
ALTER SESSION ENABLE PARALLEL DML;
Content feedback and comments