MogDB Oracle DBLink兼容性增强

原创 MogDB 2024-08-06

218

可获得性

本特性自MogDB 5.0.0版本开始引入。

特性简介

DBLink全称为database link，是一种数据库之间的单向连接，通常用来创建外部数据库的连接并对外部数据进行DML操作。本特性支持Oracle DBLink语法，通过oracle_fdw插件可直接使用@符号在MogDB数据库中访问Oracle数据库中的表。

客户价值

增强MogDB与Oracle的兼容性，减少应用程序的迁移代价。

特性描述

本特性支持Oracle DBLink语法，可以使用@符号访问Oracle数据库中的表。

支持通过DBLink执行SELECT、INSERT、UPDATE、DELETE、EXPLAIN操作。

特性约束

仅支持 SELECT、INSERT、UPDATE、DELETE、EXPLAIN 操作。

语法描述

通过DBLink连接到Oracle数据库
```
create server <dblink_name> foreign data wrapper oracle_fdw options(dbserver '<IP:PORT>/db_name'); 
```
- dblink_name：表示自定义的DBLink名称。
- IP:PORT：表示Oracle数据库所在的服务器IP地址和端口号。
- db_name：表示Oracle数据库名。
通过DBLink创建用户映射。
```
create user mapping for <mogdb_user_name> server <dblink_name> options(user '<oracle_user_name>',password 'oracle_password');
```
- mogdb_user_name：表示MogDB数据库用户名。
- dblink_name：表示自定义的DBLink名称。
- oracle_user_name：表示Oracle数据库的用户名。
- oracle_password：表示Oracle数据库的用户名密码。

通过DBLink查询表

SELECT * FROM <oracle_table_name>@<dblink_name>;

通过DBLink插入数据

INSERT INTO <oracle_table_name>@<dblink_name> VALUES (...);

通过DBLink更新表数据

UPDATE <oracle_table_name>@<dblink_name> SET... WHERE...;

通过DBLink删除表数据

DELETE FROM <oracle_table_name>@<dblink_name> where ...;

查看执行计划

explain SELECT * FROM <oracle_table_name>@<dblink_name>;

说明：

oracle_table_name：表示Oracle数据库的表名。

dblink_name：表示自定义的DBLink名称。

示例

环境准备

MogDB 环境

已安装MogDB数据库。
已安装oracle_fdw插件。安装方法参见oracle_fdw。

Oracle 环境

已登录数据库。

新建表。

CREATE TABLE scott.EMPLOYEE (
ID INT PRIMARY KEY,
NAME VARCHAR2(50) NOT NULL,
SALARY NUMBER(10,2)
);

插入测试数据。

INSERT INTO scott.EMPLOYEE (ID, NAME, SALARY) VALUES (1001, 'Mike', 5000);
INSERT INTO scott.EMPLOYEE (ID, NAME, SALARY) VALUES (1002, 'JACK', 6000);

步骤

登录MogDB数据库，以数据库postgres，端口号27000为例。

[omm5@localhost oracle_file]$ gsql -d postgres -p 27000 -r
gsql ((MogDB 5.0.0 build 503a9ef7) compiled at 2023-06-26 16:30:36 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=#

创建用户，赋予用户sysadmin权限，以用户名mymogdb50为例。

MogDB=# create user mymogdb50 identified by 'Enmo@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
MogDB=# alter user mymogdb50 sysadmin;
ALTER ROLE

退出数据库。

MogDB=# \q
[omm5@localhost oracle_file]$

为用户创建用户映射密钥文件。

[omm5@localhost oracle_file]$ gs_guc generate -S 'xxxx@123' -D $GAUSSHOME/bin -o usermapping -U mymogdb50
The gs_guc run with the following arguments: [gs_guc -S ******** -D /data/mogdb500/app/bin -o usermapping -U mymogdb50 generate ].
gs_guc generate -S *** -U ***

说明：-S 表示自定义密钥，例如xxxx@123。

以mymogdb50用户登录MogDB数据库。

[omm5@localhost oracle_file]$ gsql -d postgres -p 27000 -r -U mymogdb50 -W 'Enmo@123'
gsql ((MogDB 5.0.0 build 503a9ef7) compiled at 2023-06-26 16:30:36 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=>

创建MogDB数据库到Oracle数据库的DBLink连接，以IP地址127.2.15.23，端口号55446，数据库名pdb_test 为例。

MogDB=> CREATE SERVER db_link_to_pdb_test_15_2 FOREIGN DATA WRAPPER oracle_fdw OPTIONS(dbserver '121.36.15.2:55446/pdb_test');
CREATE SERVER

创建用户映射。为MogDB数据库用户mymogdb50和Oracle数据库用户scott建立映射关系。
```
MogDB=> create user mapping for mymogdb50 server db_link_to_pdb_test_15_2 options(user 'scott',password 'xxx123');
CREATE USER MAPPING
```
说明：用户名scott以及密码xxx123需要根据实际环境进行替换。

通过DBLink在MogDB数据库中查询Oracle表。

MogDB=> SELECT * FROM scott.EMPLOYEE@db_link_to_pdb_test_15_2;
  id  | name | salary
------+------+---------
 1001 | Mike | 5000.00
 1002 | JACK | 6000.00
(2 rows)

MogDB=> SELECT * FROM scott.EMPLOYEE@db_link_to_pdb_test_15_2 WHERE SALARY > 400;
  id  | name | salary
------+------+---------
 1001 | Mike | 5000.00
 1002 | JACK | 6000.00
(2 rows)

通过DBLink在MogDB数据库中对Oracle数据库表执行更新、插入、删除、查询操作，以scott.EMPLOYEE表为例。

MogDB=> UPDATE scott.EMPLOYEE@db_link_to_pdb_test_15_2 SET SALARY = 5500 WHERE ID = 1001;
UPDATE 1

MogDB=> insert into scott.EMPLOYEE@db_link_to_pdb_test_15_2 values (1003, 'JANE', 7000);
INSERT 0 1

MogDB=> DELETE FROM scott.EMPLOYEE@db_link_to_pdb_test_15_2 WHERE SALARY =6000;
DELETE 1

MogDB=> select * from scott.EMPLOYEE@db_link_to_pdb_test_15_2;
  id  | name | salary
------+------+---------
 1003 | JANE | 7000.00
 1001 | MIKE | 5500.00
(2 rows)

通过DBLink在MogDB数据库中对Oracle数据库表执行查看执行计划操作。

MogDB=> explain select * from scott.EMPLOYEE@db_link_to_pdb_test_15_2;
                                                    QUERY PLAN

-----------------------------------------------------------------------------------------------
-------------------
 Foreign Scan on "scott.employee@db_link_to_pdb_test_15_2" employee  (cost=10000.00..20000.00 r
ows=1000 width=78)
   Oracle query: SELECT /*66abc20a4a7895b75898e391381f9de8*/ r1."ID",r1."NAME",r1."SALARY" FROM
 scott.employee r1
(2 rows)