VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

DATABASE LINK

功能描述

当用户需要跨越本地数据库,访问远程数据库的数据时,可以通过DATABASE LINK像访问本地数据库一样访问远程数据库表中的数据。Vastbase在Oracle兼容模式下支持如下功能:

  • Vastbase G100支持使用tablename@dblink_name语法进行表的增删改查功能。

  • Vastbase支持postgres_fdw类型的dblink同义词(包含Vastbase到Vastbase和Vastbase到Oracle两种场景)。

  • Vastbase使用DATABASE LINK(jdbc_fdw)时,支持子查询下推功能,如果子查询能生成一个完整的ForeignScan,并且可以和上层的其他与之join的同server的ForeignScan,生成一个一起下推到远端做join的路径 ,则无需将子查询的结果拉到本地再进行join。该功能仅在数据库版本为V2.2 Build 15(Patch No.3)及以上版本支持。

可以创建 Public 与 Private 属性的DATABASE LINK。

  • 指定为 Public 表示所创建的 DATABASE LINK 所有用户都可以使用;指定 Private 表示仅创建者可以使用。

  • 不同的用户可以创建同名的 Private DATABASE LINK。

  • 同一用户可以创建同名的 Private DATABASE LINK和 Public DATABASE LINK。在访问时优先访问 Private DATABASE LINK。

  • 为支持不同用户创建同名DATABASE LINK,需禁用GRANT FOREIGN SERVER,不再允许将 SERVER 权限给予其他用户,以防一个用户能访问多个同名 SERVER,造成混乱。

支持的数据源

Vastbase 对 DATABASE LINK 的兼容建立在外部数据封装器(Foreign Data Wrapper,FDW)上,因此,在创建 DATABASE LINK时需指定使用的封装器。

  • 要连接 Vastbase 或 PostgreSQL 数据源,使用 postgres_fdw

    postgres_fdw 连接串格式为 postgres_fdw(host 'remote_host',port 'reomte_port',dbname 'db_remote')

  • 要连接 Oracle 数据源,使用 jdbc_fdw

    jdbc_fdw 连接串格式为 jdbc_fdw(url 'jdbc_url',jarfile 'jarfile')

注意事项

  • 该功能仅在数据库兼容模式为Oracle时支持(即数据库实例初始化时指定DBCOMPATIBILITY='A')。

  • 创建DATABASE LINK时,需要使用CREATE EXTENSION创建 postgres_fdw 或 jdbc_fdw 扩展,并具备DATABASE LINK相关权限。

  • 不支持return为%rowtype行数据类型。

  • 不支持二进制数据类型、二进制大对象类型、xml数据类型。

  • 由于 Oracle与Vastbase函数的差异性,return值会以最后一个out参数形式存在,直接远程调用函数、存储过程时,数据库会自动补充return参数,不需要用户补充,而使用同义词时,无法确定此时是call调用还是select 调用,无法自动补全return参数,需要用户自行补充,在使用流程章节中将以用例再次详细说明。

  • Vastbase不支持在连接system的情况下访问system下的自定义类型。

语法格式

  • 创建DATABASE LINK。

     CREATE [PUBLIC] DATABASE LINK ${DBLINK_NAME} CONNECT TO ${USERNAME}
     IDENTIFIED BY '${PASSWORD}' USING {
          postgres_fdw(host 'remote_host',port 'reomte_port',dbname 'remote_database') |
          jdbc_fdw(url 'jdbc_url',jarfile 'jarfile')
          };
    

    DBLINK加密时需要使用usermapping.key.cipher和usermapping.key.rand文件作为加密密码文件和加密因子。首次使用前需要在本地通过如下命令创建这两个文件,并将这两个文件放入各节点目录 $GAUSSHOME/bin,且确保具有读权限。

    vb_guc generate -o usermapping -S default -D $GAUSSHOME/bin
    
  • 删除DATABASE LINK。

     DROP [PUBLIC] DATABASE LINK ${DBLINK_NAME}
    
  • 通过DATABASE LINK进行远端表的增删改查操作。

     INSERT INTO [${SCHEMA_NAME}].${TABLE_NAME}@${DBLINK_NAME}  --插入
     UPDATE [${SCHEMA_NAME}].${TABLE_NAME}@${DBLINK_NAME} SET...    --更新
     DELETE FROM [${SCHEMA_NAME}].${TABLE_NAME}@${DBLINK_NAME} WHERE... --删除 
     SELECT ... FROM [${SCHEMA_NAME}].${TABLE_NAME}@${DBLINK_NAME}  --查询
    
  • 创建同义词。

     CREATE [OR REPLACE] SYNONYM synonym_name FOR [${SCHEMA_NAME}].${TABLE_NAME}@${DBLINK_NAME};
    

参数说明

  • PUBLIC

    表示DATABASE LINK支持跨用户访问。

  • ${DBLINK_NAME}

    连接名称,可自定义。

  • ${USERNAME}

    远端数据库用户名。

  • ${PASSWORD}

    远端数据库用户的密码。

  • remote_host

    远端 Vastbase 数据源 IP 地址。

  • remote_host

    远端 Vastbase 数据源端口号。

  • remote_database

    远端 Vastbase 数据源库名。

  • jdbc_url

    jdbc 连接串的URL。

  • jarfile

    jdbc使用的jar包名称。

  • ${SCHEMA_NAME}

    目标函数或存储过程所在的模式名(或者package名称)。可不指定,不指定时默认访问database link中Oracle用户对应的模式。

  • ${TABLE_NAME}

    远程访问的数据库表名。

  • synonym_name

    自定义的同义词名称。

示例

示例1: 使用DATABASE LINK连接远端vastbase数据库并执行DML操作。

1、在远端数据库创建用户testlink_user并授权。

CREATE USER testlink_user WITH sysadmin password '123456Aa';
grant all on database vastbase to testlink_user;

2、在远端数据库创建表emp_fdw,并插入数据。

create table emp_fdw(empno int primary key,ename varchar(30));
insert into emp_fdw values(1,'foo');
insert into emp_fdw values(2,'bar');

3、在本地数据库创建 postgres_fdw扩展。

create extension postgres_fdw;

4、在本地数据库创建用户user_1 并授权。

CREATE user user_1 password 'Aa123456';
GRANT all on database vastbase to user_1;
GRANT usage on foreign data wrapper postgres_fdw to user_1;

5、关闭远端数据库和本地数据库的强制修改密码功能。

ALTER SYSTEM SET password_force_alter=off;

6、在本地数据库,把当前会话里的会话用户标识和当前用户标识都设置为用户user_1 。

SET SESSION session authorization user_1 password 'Aa123456';

7、在本地数据库生成加密密码文件和加密因子。

vb_guc generate -o usermapping -S default -D $GAUSSHOME/bin

8、在本地数据库创建DATABASE LINK。

CREATE DATABASE LINK dblink_84 CONNECT TO testlink_user IDENTIFIED BY '123456Aa' USING postgres_fdw(host '172.16.105.57',port '10929',dbname 'vastbase');

9、在本地数据库通过dblink_84查询表emp_fdw的数据。

SELECT * FROM emp_fdw@dblink_84;

返回结果为:

 empno | ename
-------+-------
     1 | foo
     2 | bar
(2 rows)

10、清理环境。

--远端数据库
DROP USER testlink_user CASCADE;
DROP table emp_fdw;

--本地数据库

DROP extension postgres_fdw;
DROP USER user user_1 CASCADE;
DROP DATABASE LINK dblink_84;

示例2: 基于DATABASE LINK创建同义词并查询表数据(G100到G100的DATABASE LINK)。

1、在远端数据库创建db_remote数据库。

create database db_remote;
\c db_remote

2、在远端数据库创建用户并授权。

create user user_remote with sysadmin password 'Aa123456';
grant all on database vastbase to user_remote;

3、切换用户(远端数据库)。

set role user_remote password 'Aa123456';

4、在远端数据库创建表并插入数据。

CREATE TABLE table_remote_1173268(col1 int primary key,col2 varchar(30));
INSERT INTO table_remote_1173268 VALUES(1,'clouds');
INSERT INTO table_remote_1173268 VALUES(2,'break');
INSERT INTO table_remote_1173268 VALUES(4,'hhhhh');

5、在本地数据库生成加密密码文件和加密因子。

vb_guc generate -o usermapping -S default -D $GAUSSHOME/bin

6、在本地创建数据库db_local。

create database db_local;
\c db_local;

7、在本地数据库db_local创建用户。

create user user_local with sysadmin password 'Aa123456';
grant all on database vastbase to user_local;
set role user_local password 'Aa123456';

8、切换用户(本地数据库)。

set role user_local password 'Aa123456';

9、在本地数据库创建插件。

create extension postgres_fdw;

10、在本地数据库创建DATABASE LINK。

CREATE DATABASE LINK dblink_84 CONNECT TO user_remote IDENTIFIED BY 'Aa123456' USING postgres_fdw(host '172.16.103.84',port '11001',dbname 'db_remote');

11、关闭远端数据库和本地数据库的强制修改密码功能。

alter system set password_force_alter=off;

12、在本地数据库创建表。

create table table_remote_1173268_2(col1 int,col2 varchar(30));
insert into table_remote_1173268_2(col1,col2) select col1,col2 from table_remote_1173268@dblink_84;

13、通过dblink查询远端数据插入本地表。

insert into table_remote_1173268_2(col1,col2) select col1,col2 from table_remote_1173268@dblink_84;

14、在本地数据库查询表数据。

select * from table_remote_1173268_2;

返回结果为:

 col1 |  col2
------+--------
    1 | clouds
    2 | break
    4 | hhhhh
(3 rows)

15、在本地数据库创建同义词。

CREATE SYNONYM syn_table_1173268 for table_remote_1173268@dblink_84;

16、在本地数据库使用创建的DATABASE LINK同义词插入数据。

insert into table_remote_1173268_2(col1,col2) select col1,col2 from syn_table_1173268;

17、在本地数据库 再次执行查询。

select * from table_remote_1173268_2;

返回结果为:

 col1 |  col2
------+--------
    1 | clouds
    2 | break
    4 | hhhhh
    1 | clouds
    2 | break
    4 | hhhhh
(6 rows)

18、清理环境。

--远端数据库
DROP DATABASE db_remote;
DROP USER user_remote cascade;
DROP TABLE table_remote_1173268;

--本地数据库
DROP DATABASE db_local;
DROP USER user_local cascade;
DROP extension postgres_fdw;
DROP DATABASE LINK dblink_84;
DROP TABLE table_remote_1173268_2;
DROP synonym syn_table_1173268;

示例3: 创建私有或公有DATABASE LINK。

1、修改postgresql.conf参数,将jdbc_fdw配置到shared_preload_libraries参数中,该参数修改完成后需要重启数据库。

shared_preload_libraries = 'jdbc_fdw'

2、重启数据库实例。

3、创建插件。

CREATE EXTENSION jdbc_fdw;

4、生成加密密码文件和加密因子。

vb_guc generate -o usermapping -S default -D $GAUSSHOME/bin

5、创建DATABASE LINK。

(1)创建私有private DATABASE LINK。

CREATE  DATABASE  LINK  oradb1  connect  to  SYSTEM  identified  by  'root'  
using  jdbc_fdw(
url  'jdbc:oracle:thin:@//172.16.103.120:1521/utf8',
jarfile  '/home/vastbase2/ojdbc7.jar'
);

(2)创建公共public DATABASE LINK。

CREATE  PUBLIC  DATABASE  link  oradb2  connect  to  SYSTEM  identified  by  'root'  
using  jdbc_fdw(
url  'jdbc:oracle:thin:@//172.16.103.104:1521/orcl',
jarfile  '/home/vastbase2/ojdbc7.jar'
);

6、使用DATABASE LINK查询。

select  *  from  fdw_t1@oradb1;
select  *  from  fdw_t1@oradb2;

返回结果为:

c1    |    c2
----+----
1     |  a1
2     |  a1
3     |  a1
(3 rows)

c1    |    c2
----+----
1     |  a1
2     |  a1
3     |  a1
(3 rows)

7、在本地执行ALTER SERVER命令。

ALTER  SERVER  oradb1  VERSION  '8.4';
ALTER  PUBLIC  SERVER  oradb2  VERSION  '8.5';

8、清理环境,删除DATABASE LINK。

DROP  DATABASE  LINK  oradb1;
DROP  PUBLIC  DATABASE  LINK  oradb2;

示例4: 基于DATABASE LINK(jdbc_fdw)的子查询下推功能。

1、获取驱动包ojdbc7.jar,并将其放置到$GAUSSHOME/lib/postgresql/下。

2、修改postgresql.conf参数,将jdbc_fdw配置到shared_preload_libraries参数中,该参数修改完成后需要重启数据库。

shared_preload_libraries = 'jdbc_fdw'

3、重启数据库实例。

4、在本地创建jdbc_fdw。

create extension jdbc_fdw;

5、Oracle远端创建表并插入数据。

CREATE TABLE t1(id int,a1 date,a2 varchar(30),a3 char(30));
INSERT INTO t1 VALUES(1,date'2023-1-26','a','a');
INSERT INTO t1 VALUES(2,date'2022-1-26','b','b');
INSERT INTO t1 VALUES(3,date'2021-1-26','c','c');
INSERT INTO t1 VALUES(4,date'2020-1-26','d','d');
insert into t1 VALUES(5,date'2019-1-26','e','e');
CREATE TABLE t2(id int,a1 date,a2 varchar(30),a3 char(30));
INSERT INTO t2 VALUES(1,date'2023-1-26','a','a');
INSERT INTO t2 VALUES(2,date'2022-1-26','b','b');
INSERT INTO t2 VALUES(3,date'2021-1-26','c','c');
INSERT INTO t2 VALUES(4,date'2020-1-26','d','d');
INSERT INTO t2 VALUES(5,date'2019-1-26','e','e');

6、生成加密密码文件和加密因子。

vb_guc generate -o usermapping -S default -D $GAUSSHOME/bin

7、在本地创建DATABASE LINK。

CREATE DATABASE LINK dblink_1193065 CONNECT TO wzr IDENTIFIED BY 'Aa123456'  USING jdbc_fdw(
url 'jdbc:oracle:thin:@//172.16.103.104:1521/orcl',
jarfile '/home/vb2216_danny_18432/local/vastbase/lib/postgresql/ojdbc7.jar'
);

8、在本地执行查询。

select * from t1@dblink_1193065 aa, (select a.id,a.a1,b.a2 from t1@dblink_1193065 a left join t2@dblink_1193065 b on a.id=b.id ) bb where aa.id=bb.id;

返回结果为:

 id |         a1          | a2 |               a3               | id |         a1          | a2 
----+---------------------+----+--------------------------------+----+---------------------+----
  1 | 2023-01-26 00:00:00 | a  | a                              |  1 | 2023-01-26 00:00:00 | a  
  2 | 2022-01-26 00:00:00 | b  | b                              |  2 | 2022-01-26 00:00:00 | b  
  3 | 2021-01-26 00:00:00 | c  | c                              |  3 | 2021-01-26 00:00:00 | c  
(3 rows)

9、查看步骤8查询语句的执行计划。

explain (costs off) select * from t1@dblink_1193065 aa, (select a.id,a.a1,b.a2 from t1@dblink_1193065 a left join t2@dblink_1193065 b on a.id=b.id ) bb where aa.id=bb.id limit 3;

返回结果为:

                    QUERY PLAN    
-----------------------------------------------------------
   -> Foreign Scan
        Relations:((tl aa)INNER JOIN(t1 a))LEFT JOIN(t2 b)
(2 rows)

10、在本地创建同义词并查询。

create synonym s1 for t1@dblink_1193065;
create synonym s2 for t2@dblink_1193065;

11、在本地通过同义词查询表数据。

select * from s1;
select * from s2;

返回结果均为:

 id |         a1          | a2 |               a3
----+---------------------+----+--------------------------------
  1 | 2023-01-26 00:00:00 | a  | a
  2 | 2022-01-26 00:00:00 | b  | b
  3 | 2021-01-26 00:00:00 | c  | c
  4 | 2020-01-26 00:00:00 | d  | d
  5 | 2019-01-26 00:00:00 | e  | e
(5 rows)