通过jdbc连接其他数据库

pxf通过jdbc访问数据库

使用说明

• 通过jdbc访问seaboxmpp、postgres、greenplum、mysql等数据库

• 创建文件夹${SDHOME}/pxf_conf/servers/${server_name}

• 拷贝${SDHOME}/pxf_conf/template/jdbc-site.xml到${server_name}目录

• 编辑jdbc-site.xml编辑数据库的连接信息

<configuration>
    <property>
        <name>jdbc.driver</name>
        <value>org.seaboxsql.Driver</value>
        <description>Class name of the JDBC driver (e.g. org.seaboxsql.Driver)</description>
    </property>
    <property>
        <name>jdbc.url</name>
        <value>jdbc:seaboxsql://localhost:7300/seaboxsql</value>
        <description>The URL that the JDBC driver can use to connect to the database (e.g. jdbc:seaboxsql://localhost/seaboxsql)</description>
    </property>
    <property>
        <name>jdbc.user</name>
        <value>seabox</value>
        <description>User name for connecting to the database (e.g. seaboxsql)</description>
    </property>
    <property>
        <name>jdbc.password</name>
        <value>seabox</value>
        <description>Password for connecting to the database (e.g. seaboxsql)</description>
    </property>
</configuration>

以下是创建外部表和COPY命令的选项说明

选项	说明
pxf_server	对应${SDHOME}/pxf_conf/servers/${server_name}下的server_name文件夹
partition_by	切分查询用的列名称, 必须是int, date或者enum类型,格式为column_name:datatype
range	切分查询的范围，用冒号分割，例如int, 1:3, 表示范围从1到3
interval	切分查询的间隔

使用举例

1. 根据配置文件创建 server

create extension pxf_fdw ;
create server jdbc foreign data wrapper jdbc_pxf_fdw options (pxf_server 'seabox');
create user mapping for public server jdbc;

1. seabox中的数据表

seaboxsql=# \d test
                Table "public.test"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | text    |           |          | 
Distributed by: (a)

seaboxsql=# select * from test;
  a  |    b    
-----+---------
 300 | string3
 200 | string2
 100 | string1
(3 rows)

1. 创建jdbc读外部表

seaboxsql=# create foreign table t_jdbc(a int, b text)server jdbc options(resource 'test', partition_by 'a:int', range '100:300', interval '100');
CREATE FOREIGN TABLE
seaboxsql=# select * from t_jdbc;
  a  |    b    
-----+---------
 100 | string1
 200 | string2
 300 | string3
(3 rows)

1. 使用COPY语法导入数据

seaboxsql=# copy test from 'fdw://jdbct/test' (partition_by 'a:int', range '100:300', interval '100');
COPY 3

1. 创建jdbc可写外部表

可写入数据的外部表

seaboxsql=# create foreign table t_jdbc_write(a int, b text)server jdbc_test options(resource 'test', pxf_server 'seabox', is_custom_write 'true', formatter 'pxfwritable_export');
CREATE FOREIGN TABLE
seaboxsql=# truncate test;
TRUNCATE TABLE
seaboxsql=# insert into t_jdbc_write values(110,'string4'),(120,'string5'),(130,'string6');
INSERT 0 3
seaboxsql=# select * from test;
  a  |    b    
-----+---------
 110 | string4
 120 | string5
 130 | string6
(3 rows)

1. 自定义查询

除了支持表意外，jdbc也可以支持自定义查询，使用方法与自定义service类似。在${server_name}文件夹下放.sql文件，注意要使用AS关键字给列名字，名字和最终定义的外部表中的名字保持一致。

--- 文件名为test.sql, 必须有结尾的分号，$PXF_CONF/servers/seabox下面
select a*a  as c1, substring(b, 2) as c2 from test;

1. 外部表访问

seaboxsql=# create foreign table t_jdbc_query(c1 int, c2 varchar(10)) server jdbc_test options(resource 'query:test');
CREATE FOREIGN TABLE
seaboxsql=# select * from t_jdbc_query ;
  c1   |   c2   
-------+--------
 14400 | tring5
 16900 | tring6
 12100 | tring4
(3 rows)

1. 使用copy导入数据

bash seaboxsql=# create table t_query(c1 int, c2 varchar(10)); CREATE TABLE seaboxsql=# copy t_query from 'fdw://jdbc_test/query:test'; COPY 3 seaboxsql=# select * from t_query; c1 | c2 -------+-------- 14400 | tring5 16900 | tring6 12100 | tring4 (3 rows)