In this example, you:
For information about controlling parallel execution in Oracle, refer to About Setting Parallel Query Session Parameters located at the end of this topic.
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 password mypassword
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;
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/lib
of your Greenplum Database 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/lib
of the Greenplum Database coordinator:
If you did not relocate $PXF_BASE
, run the following from the Greenplum coordinator:
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:
gpadmin@coordinator$ scp ojdbc10.jar gpadmin@coordinator:$PXF_BASE/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
. 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>
Synchronize the PXF server configuration to the Greenplum Database cluster:
gpadmin@coordinator$ pxf cluster sync
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)
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 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)
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;