PXF Access Oracle

1. Oracle version and table information

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0  Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> SELECT * FROM dept;

    DEPTNO DNAME       LOC
----------------------------------------------------------------------------------------------------------------------------------
        10 ACCOUNTING      NEW YORK
        20 RESEARCH        DALLAS
        30 SALES           CHICAGO
        40 OPERATIONS      BOSTON

2. Install

2.1 Java installation

export JAVA_HOME=/usr/local/jdk1805
export JRE_HOME=$JAVA_HOME/jre
export CLASS_PATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib
export PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin

2.2 PXF Installation

sudo yum install pxf-matrixdb3-5.16.1-1.el7.x86_64.rpm -y
sudo mkdir /usr/local/pxfconf
sudo chown mxadmin.mxadmin -R /usr/local/pxfconf
sudo chown mxadmin.mxadmin -R /usr/local/pxf-matrixdb4

export PXF_HOME=/usr/local/pxf-matrixdb4
export PXF_CONF=/usr/local/pxfconf
export PATH=$PXF_HOME/bin:$PATH

3. Configuration

Download the corresponding version of Oracle driver:

wget https://repo1.maven.org/maven2/com/oracle/database/jdbc/ojdbc6/11.2.0.4/ojdbc6-11.2.0.4.jar

Initialize PXF:

pxf cluster init

Put the driver into the PXF library directory (required for all nodes):

cp ojdbc6-11.2.0.4.jar /usr/local/pxfconf/lib/

Configure Oracle connection string:

[mxadmin@sdw2 oracle11g]$ pwd
/usr/local/pxfconf/servers/oracle11g


[mxadmin@sdw2 oracle11g]$ cat jdbc-site.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <property>
        <name>jdbc.driver</name>
        <value>oracle.jdbc.driver</value>
        <description>Class name of the JDBC driver (e.g. org.postgresql.Driver)</description>
    </property>
    <property>
        <name>jdbc.url</name>
        <value>jdbc:oracle:thin:@sdw7:1521/orcl</value>
        <description>The URL that the JDBC driver can use to connect to the database (e.g. jdbc:postgresql://localhost/postgres)</description>
    </property>
    <property>
        <name>jdbc.user</name>
        <value>scott</value>
        <description>User name for connecting to the database (e.g. postgres)</description>
    </property>
    <property>
        <name>jdbc.password</name>
        <value>tiger</value>
        <description>Password for connecting to the database (e.g. postgres)</description>
    </property>

Synchronous configuration to other nodes:

[mxadmin@sdw2 ~]$ pxf cluster sync
Syncing PXF configuration files from master host to 1 segment host...
PXF configs synced successfully on 1 out of 1 host

4. Start and view cluster status

Start the node:

pxf cluster start

Check the cluster status:

pxf cluster status
Checking status of PXF servers on 2 segment hosts...
PXF is running on 2 out of 2 hosts

5. Visit

Create a plugin:

postgres=# CREATE EXTENSION pxf_fdw ;
CREATE EXTENSION

Definition SERVER:

DROP SERVER oracle_server CASCADE;
CREATE SERVER oracle_server
     FOREIGN DATA WRAPPER jdbc_pxf_fdw
     OPTIONS (
         jdbc_driver 'oracle.jdbc.driver.OracleDriver',
         db_url 'jdbc:oracle:thin:@sdw7:1521:ORCL',
         batch_size '10000',
         fetch_size '2000'
     );

Create a user map:

CREATE USER MAPPING FOR mxadmin SERVER oracle_server OPTIONS ( user 'scott' , pass 'tiger' );

Create an external table:

drop FOREIGN TABLE ft_dept;
CREATE FOREIGN TABLE ft_dept(
 deptno integer,
 dname character varying,
 loc character varying
) SERVER oracle_server
OPTIONS (resource 'DEPT');

Access Oracle data through external tables:

postgres=# SELECT * FROM ft_dept ;
 deptno |   dname    |   loc
-------+--------------------------------------------------------------------------------------------------------------------------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)