金仓数据库KingbaseES 插件kdb_database_link

1.Database Link介绍

Database Link简称DBLink，是数据库管理系统提供的用于访问外部数据库对象的机制。用于可以通过DBLink来访问外部数据库的表、视图对象。DBLink实现了两个数据库之间的通信。DBLink包含一个基于网络的数据连接以及用于登陆远程数据库的用户名、密码信息。本地数据库系统可以通过DBLink建立与远程数据库之间的会话，从而完成对远程数据库对象的访问。DBLink实现了SQL/MED标准中定义的对外部数据源进行访问的部分功能。

  Note:

SQL/MED是SQL Management of External Data的缩写，是ISO/IEC 9075-9:2003标准中对SQL语言的扩展。SQL/MED标准规定了如何通过外部数据封装器（Foreign Data Wrapper）和数据连接（datalink）实现对外部数据的访问。这里的外部数据指的是可以被基于SQL的DBMS访问的数据。

dblink对象是一个数据库内部对象，可以通过dblink对象连接引用其他数据库的对象。支持连接Oracle、Kingbase和Postgresql的dblink对象。

相关定义如下：

• 本地数据库：当前数据库客户端所连接到的数据库，可以直接访问和操作在此数据库内拥有相应权限的对象，客户端持有该连接。

• 远程数据库：通过定义于本地数据库内的dblink访问的数据库，虽然客户端看起来可以直接访问远程数据库的对象，实际上此类连接由本地数据库持有，对象定义等系统信息在本地数据库并不存在。远程数据库也可能只是本地数据库通过设置连接到了自己的另一个服务进程或者线程。

• 远程连接：通过dblink访问远程数据库对象时创建的数据库连接，对于目标数据库，本地数据库是它为之提供服务的客户端。本地数据库对自己创建和拥有的远程连接，需要进行合理的管理，在必要时应及时关闭，防止过多的资源开销。

• 远程表（视图、物化视图）：存在于远程数据库上的表，也可能是视图或者物化视图，本地数据库对此并不关心，只关心所取得的数据结构定义和数据本身。

• 远程序列：存在于远程数据库上的序列发生器，通常用于统一id的生成。

• 远程函数（存储过程）：存在于远程数据库上的函数或者存储过程，一般远程调用函数的场景居多。

• 远程同义词：存在于本地数据库指向远程对象（表、序列、函数等等）的同义词。

2. Database Link功能

Dblink功能主要是为了满足常见语法的适配，让用户应用的代码能够适用于更宽泛的产品而无需在移植时大量修改：

• 支持连接管理，在适当的时候关闭连接减少远程数据库的资源开销。

• 支持远程表（视图、物化视图）的查询，并且支持下推查询条件减少数据传输的网络开销。

• 支持远程表的插入。

• 支持远程序列的访问。

3. 创建Database Link对象

在创建dblink对象时指定连接信息，包括远程数据库网络地址、端口、数据库名称、用户名和密码，具体需要提供哪些信息会因为不同数据库存在差异。

语法格式：

CREATE [ PUBLIC ] DATABASE LINK dblink
       CONNECT TO user IDENTIFIED BY password USING
       {(connect_string) | config_tag };

参数说明：

PUBLIC

指定PUBLIC创建公有dblink对象，所有用户都可以访问公有dblink对象。公有dblink是创建在PUBLIC模式下面的。未指定PUBLIC时创建私有dblink，不可在PUBLIC模式下创建私有dblink。

dblink

指定要创建的数据库链接的名称，同一个模式下的dblink不能重名。

user IDENTIFIED BY password

指定远端数据库的用户名和密码。

connect_string

配置连接串信息，connect_string用来提供用于连接到远程数据库的信息。

config_tag

配置文件标签名,用于指明配置文件（sys_database_link.conf）中的一项,从而通过配置文件获取用于建立数据库连接的远程数据库的网络地址、端口以及数据库名称。

注解：

1、在配置文件中的格式如下：

[名称]

DriverName=连接驱动名称。

Host=远程数据库网络地址。

Port=远程数据库服务端口。

Dbname=远程数据库名称。

DbType=远程数据库类型。

DbType支持Oracle，Kingbase，Postgres三种数据库类型。

2、使用该功能时，需将kdb_database_link加入shared_preload_libraries中。

3、查询外部数据库时，需创建对应的数据库插件，如:kingbase_fdw、oracle_fdw、postgres_fdw。

举例：

创建一个到Oracle数据库的数据库连接，它可以被所有数据库用户使用：

CREATE PUBLIC DATABASE LINK mylink CONNECT TO ‘SYSTEM’ IDENTIFIED BY ‘password’ USING 'ORADB';

创建一个到Oracle数据库的数据库连接，直接指定连接串信息：

CREATE PUBLIC DATABASE LINK mylink CONNECT TO ‘SYSTEM’ IDENTIFIED BY ‘password’ USING
(DriverName=’Oracle ODBC Driver’, Host=192.168.0.1, Port=1521, Dbname=TEST, DbType=Oracle);

4. 修改Database Link对象

ALTER DATABASE LINK修改一个dblink数据库对象。

第一种形式更改dblink的拥有者，要修改拥有者，你必须拥有该dblink并且也是新拥有角色的一个直接或间接成员。

第二种形式更改dblink的名称，只有dblink拥有者或者超级用户可以重命名一个dblink。

语法格式：

ALTER [PUBLIC] DATABASE LINK dblink_name OWNER TO new_owner;
ALTER [PUBLIC] DATABASE LINK dblink_name RENAME TO new_name;

参数说明：

dblink_name

一个现有dblink的名称（可以是模式限定的）。

new_owner

该dblink的新拥有者的用户名。

dblink_name

该dblink的新名称。

注解：

ALTER DATABASE LINK语法类似于ALTER VIEW。

举例：

把DATABASE LINKkdb_dblink_regress.link_d拥有者修改为 kdb_dblink_user:

ALTER DATABASE LINK kdb_dblink_regress.link_d OWNER TO kdb_dblink_user;

兼容性：

ALTER DATABASE LINK语句是一个KingbaseES扩展。

5. 删除Database Link对象

DROP DATABASE LINK移除一个dblink。要执行这个命令必须拥有该dblink的权限。

语法格式：

DROP DATABASE LINK dblink_name

参数说明：

dblink_name

删除名为dblink_name的DATABASE LINK

注解：

DBLink可以被拥有者和DBA删除,删除DBLink对象之后，所有的用户会话中的对象都会被清除。

如果DBLink正在被其他用户使用，则无法删除此DBLink。

暂不支持PUBLIC关键字。

兼容性：

该命令是KingbaseES的一个扩展，兼容Oracle。

6. Database Link的权限控制

对于私有的DBLink，其创建者和DBA具有对此DBLink对象进行访问的权限。除DBLink创建者之外的其他数据库用户可以对以PUBLIC方式建立的DBLink进行访问。

本地用户对远程数据库对象的访问权限由远程数据库系统的用户认证机制来控制。通过DBLink连接到远程数据库的本地用户将得到远程数据库的用户拥有的查询权限。

7. DBLink相关的视图

DBLink向用户提供的视图如下：

8. DATABASE LINK DML

DATABASE LINK — dblink 的DML操作

语法格式：

INSERT

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name@dblink [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER} VALUE ]
    { VALUES ( { expression } [, ...] ) [, ...] | query }
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

UPDATE

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE table_name@dblink [ * ] [ [ AS ] alias ]
    SET { column_name = { expression } |
        ( column_name [, ...] ) = [ ROW ] ( { expression } [, ...] ) |
        ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition ]
    [ RETURNING * | output_expression [ [ AS ] output_name ]  [, ...] ]

DELETE

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM table_name@dblink [ * ] [ [ AS ] alias ]
    [ WHERE condition ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

描述：

使用dblink对远程表进行插入，更新，删除。

参数：

dblink

dblink的名称，使用CREATE DATABASE LINK创建的dblink数据库连接的名称。

with_query

WITH子句允许你指定一个或者多个子查询，在INSERT UPDATE DELETE 查询中可以用子查询的名字来引用它们 详见 WITH查询（公共表表达式） 和 SELECT 。

table_name

一个已有表的名称（可以被模式限定）。

alias

目标表名的替补名称。当提供了一个别名时，它会完全隐藏掉表的实际名称。

column_name

名为table_name 的表中的一个列 的名称，可以用表名(可以被模式名限定)做限定。如有必要，列名还可以用一个子域名(子域名需要和表名、模式名一同用小括号包围起来进行限定)或者数组下标限定。

expression

要赋予给相应列的表达式或者值。

output_expression

在每一行被插入或更新或删除后由INSERT或UPUDATE或 DELETE命令计算并且返回的 表达式。该表达式可以使用``table_name`` 指定的表中的任何列。写成*可返回被插入或更新或删除行的所有列。

output_name

要用于被返回列的名称。

condition

一个能返回boolean值的表达式。只有让这个表达式返回 true的行才将被更新，删除。

使用限制：

INSERT

远端表插入操作不支持ON CONFLICT子句

不支持显示指定生成列的值

对于RETURNING，不支持RETURNING ctid等系统列

暂不支持列的默认值，对于有默认值的远程表，在进行insert的时候，如果显式使用例如insert into {tablename} default value或者insert into {tablename} values(default)会报错dblink not support insert with default value；如果insert into的时候，没有指定全部的列也会报上面的错误比如当表tab有两列a, b的时候，那么下面sql都会报错

1. insert into tab@dblink values(1)

2. insert into tab@dblink(a) values(1)

3. insert into tab@dblink select a from tab

4. insert into tab@dblink(a) select a from tab

UPDATE

当远端表为继承表时，不支持仅更新父表数据，指定ONLY关键字时报错

对于RETURNING，不支持RETURNING ctid等系统列

目前update不支持列的默认值，在进行update的时候，如果显式的指定使用默认值，比如update {tablenaem} set {columname}=default，会报错dblink not support insert with default value

目前不支持 WHERE CURRENT OF cursor_name 的用法

目前update对于 分区表 和 继承表 的行为表现不确定，所以禁止使用dblink更新远程 分区表 或者 继承表 中的数据

DELETE

当远端表为继承表时，不支持仅删除父表数据，指定ONLY关键字时报错

对于RETURNING，不支持RETURNING ctid等系统列

目前delete对于 分区表 和 继承表 的行为表现不确定，所以禁止使用dblink删除远程 分区表 或者 继承表 中的数据

ORACLE兼容：

当dblink创建的数据库连接是连接到Oracle的时候，除了以上的使用限制外，目前dblink还不支持UPDATE和DELETE

9. DATABASE LINK MERGE

DATABASE LINK — dblink 的MERGE操作

语法格式：

MERGE INTO [ schema. ] { target_table@dblink } [ [ AS ] target_table_alias ]
    USING { [ schema. ] { source_table } [ [ AS ] source_table_alias ]
    ON ( condition_expression )
[ merge_update_clause ]
[ merge_insert_clause ];

merge_update_clause:
WHEN MATCHED THEN
UPDATE SET column = { expr }[, column = { expr } ]...
[ where_clause ]
[ delete_clause ]

delete_clause:
[DELETE where_clause]

merge_insert_clause:
WHEN NOT MATCHED THEN
INSERT [ ( column [, column ]...) ]
VALUES ({ expr }[, { expr } ]...)
[ where_clause ]

where_clause:
WHERE condition

描述：

使用dblink对远程表进MERGE操作。

参数：

target_table

MERGE远程目标表的名称。

dblink

dblink的名称，使用CREATE DATABASE LINK创建的dblink数据库连接的名称。

source_table

MERGE源表的名称。源表可以是一个本地表，普通外表，也可以是一个dblink的远程表，即tablename@dblinkname。

target_table_alias

MERGE目标表的别名。

source_table_alias

MERGE源表的别名。

expr

要赋予给相应列的表达式或者值。

condition_expression

指定目标表与源表之间进行联接的联接条件。如果该条件为真，且指定了 WHEN MATCHED THEN UPDATE 子句，则对匹配到的目标表的该元组执行更新操作；否则，如果该条件为假且指定了 WHEN NOT MATCHED THEN INSERT 子句，则对目标表执行插入操作。

merge_update_clause

当目标表和源表的ON条件为真时，执行该子句，即更新目标表数据。该更新操作会触发目标表上面的触发器。更新的列不能是ON条件中被引用的列，更新时可以通过WHERE条件指明要更新的行，条件中既可以包含源表的列，也可以包含目标表的列，当指明WHERE条件且条件为假时，则不更新。

delete_clause

DELETE子句只删除目标表和源表的ON条件为真、并且是更新后的符合删除条件的记录，DELETE子句不影响INSERT项插入的行。删除条件作用在更新后的记录上，既可以和源表相关，也可以和目标表相关，或者都相关。如果ON条件为真，但是不符合更新条件，并没有更新数据，那么DELETE将不会删除任何数据。

merge_insert_clause

当目标表和源表的ON条件为假时，执行该语句。可指定插入条件，插入时的WHERE条件只能引用源表中的列。VALUES后面也只能引用源表中的列，不能包含目标表的列。