400-800-0824
info@ymatrix.cn
400-800-0824
info@ymatrix.cn
400-800-0824
info@ymatrix.cn
400-800-0824
info@ymatrix.cn
400-800-0824
info@ymatrix.cn
YMatrix 文档
关于 YMatrix
标准集群部署
数据写入
数据迁移
数据查询
运维监控
参考指南
工具指南
数据类型
存储引擎
执行引擎
系统配置参数
SQL 参考
常见问题(FAQ)
新架构 FAQ
集群部署 FAQ
SQL 查询 FAQ
MatrixGate FAQ
运维 FAQ
监控告警 FAQ
PXF FAQ
PLPython FAQ
性能 FAQ
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
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
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
下载 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
启动节点:
pxf cluster start
查看集群状态:
pxf cluster status
Checking status of PXF servers on 2 segment hosts...
PXF is running on 2 out of 2 hosts
创建插件:
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)