Kingbase kdb_database_link扩展插件使用

1. 插件Database Link简介

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

  • 插件名为 kdb_database_link

  • 插件版本 V1.0.0

注意

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

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

相关定义如下：

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

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

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

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

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

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

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

1.1. Database Link功能?

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

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

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

  • 支持远程表的插入。

  • 支持远程序列的访问。

  • 支持远程用户自定义函数(存储过程)的访问。目前仅支持KingbaseES数据库。

2. 插件kdb_database_link的加载方式?

KingbaseES数据库默认将他添加到 kingbase.conf 文件的 shared_preload_libraries 中，重启数据库时自动加载。

shared_preload_libraries = 'kdb_database_link'

在ksql中，运行

CREATE EXTENSION kdb_database_link;

3. 插件kdb_database_link的参数配置?

  • DriverName

连接驱动名称。

  • Host

远程数据库网络地址。

  • Port

远程数据库服务端口。

  • Dbname

远程数据库名称。

  • DbType

远程数据库类型。 DbType支持Oracle，KingbaseES，Postgres三种数据库类型。

4. 插件kdb_database_link的使用方法

4.1. 创建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，KingbaseES，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.2. 修改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 LINK kdb_dblink_regress.link_d拥有者修改为 kdb_dblink_user:

ALTER DATABASE LINK kdb_dblink_regress.link_d OWNER TO kdb_dblink_user;

兼容性

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

4.3. 删除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。

4.4. Database Link的权限控制?

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

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

4.5. DBLink相关的视图?

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

表 41.4.1 DBLink相关视图?

名称

说明

ALL_DBLINKS

描述当前用户可以访问的所有数据库连接

USER_DBLINKS

描述当前用户拥有的所有数据库连接

DBA_DBLINKS

描述DBA可以访问的所有数据库连接

4.6. 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更新远程 分区表 或者 继承表 中的数据

ORACLE兼容

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

4.7. 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后面也只能引用源表中的列，不能包含目标表的列。

4.8. DATABASE LINK 调用函数/存储过程?

DATABASE LINK — dblink 调用用户自定义函数/存储过程

语法格式

[schema_name.] func_name@dblink([args])

CALL  [schema_name.]proc_name@dblink([args])

描述

使用dblink访问远程用户自定义函数/存储过程。

参数

dblink

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

schema_name

远程模式名称。

func_name

远程用户自定义函数/储存过程名称。

args

函数/储存过程的参数。

使用限制

  • 函数

    当在同一用户模式下存在同名函数时，该函数不支持通过远程调用。 dblink函数的调用方式，支持select func@dblink(arg)这种方式。不支持select * from func@dblink(arg)方式，以及call func@dbink(arg)的这种方式。

    支持在以下语句类型中调用：

    1. SELECT列表；

    2. WHERE/HAVING条件；

    3. WITH/GROUP BY/ORDER BY；

    4. INSERT的VALUES子句；

    5. UPDATE的SET子句；

    6. JOIN条件；

    7. PL/SQL中引用。

    不支持以下函数类型：

    • 聚集函数

    • 窗口函数

    • PIPELINED函数

函数属性支持情况如下：

  1. IMMTABLE：该函数不能修改数据库，并且对于给定的参数值总是会返回相同的值；

  2. STABLE：该函数不能修改数据库，并且对于相同的参数值，它在一次表扫描中将返回相同的结果；

  3. VOLATILE：该函数的值在一次表扫描中有可能改变，不能做优化；

  4. PARALLEL :

    1. UNSAFE：该函数不能在并行模式中运行并且SQL语句中存在一个这样的函数会强制使用顺序执行计划；

    2. RESTRICTED：该函数能在并行模式中运行，但是其执行被限制在并行组的leader中；

    3. SAFE：该函数可以不受限制的在并行模式中执行；

  5. STRICT：该函数中任意参数为空值时，函数返回值也为空值。

其中，函数参数只支持IN模式，支持远程函数参数默认值。参数个数最大支持510个。参数类型和返回值支持KingbaseES的基本内置数据类型。

  • 存储过程

    支持使用``CALL``调用远程用户自定义存储过程。在dlbink用户有执行权限的情况下，允许跨模式调用其他用户定义的存储过程。 另外，当同一模式下存在同名存储过程时，该存储过程不能通过dblink远程调用。

    目前仅支持in参数，不支持out参数和inout参数。存储过程的参数个数不大于98个。

兼容性

目前仅支持KingbaseES远程数据库调用函数、存储过程。

5. 插件kdb_database_link的卸载方法?

在ksql中运行：

drop extension kdb_database_link;

修改 kingbase.conf 文件中 shared_preload_libraries 参数，去掉kdb_database_link后重启数据库。

shared_preload_libraries = '';

6. 插件kdb_database_link的升级方法?

kdb_database_link扩展插件通常随着KingbaseES安装包一并升级。通常情况下用户无须单独升级些插件。