PXF访问Oracle

1. Oracle版本与表信息

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. 安装

2.1 Java安装

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

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

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

3. 配置

下载oracle对应版本的驱动：

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

初始化PXF：

pxf cluster init

将驱动放入pxf库目录（所有节点都需要）：

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

配置oracle连接串：

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

同步配置到其它节点：

[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. 启动并查看集群状态

启动节点：

pxf cluster start

查看集群状态：

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

5. 访问

创建插件：

postgres=# create extension pxf_fdw ;
CREATE EXTENSION

定义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 USER MAPPING FOR mxadmin SERVER oracle_server OPTIONS ( user 'scott' , pass 'tiger' );

创建外部表：

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

通过外部表访问Oracle数据：

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