KingbaseES 通过 DBLink 实现跨库操作全攻略

KingbaseES可以通过dblink建立与远程数据库之间的会话，从而完成对远程数据库对象的访问。支持连接Oracle、KingbaseES、Postgresql和SQLServer的dblink对象。其中dblink 通过扩展模块来实现，下面将详细介绍其使用方法与关键要点。

一、启用 kdb_database_link 扩展
在利用 DBLink 进行跨库操作前，首要步骤是在 KingbaseES 数据库中启用kdb_database_link扩展。执行以下语句即可完成创建：

sql

CREATE EXTENSION kdb_database_link;

此操作是后续跨库连接与数据交互的基础，只有成功启用该扩展，才能顺利使用 DBLink 相关功能。

二、创建 Database Link 对象
（一）语法格式与参数详解
创建 dblink 对象时，需要精准指定各类连接信息，其语法格式如下：

sql

CREATE [ PUBLIC ] DATABASE LINK dblink

CONNECT TO user IDENTIFIED BY password USING

{(connect_string) | config_tag };

各参数含义如下：

PUBLIC：若指定该参数，将创建公有 dblink 对象，所有数据库用户均可访问，此类对象创建于 PUBLIC 模式下。若未指定，则创建私有 dblink，且私有 dblink 不可在 PUBLIC 模式下创建。
dblink：用于指定要创建的数据库链接名称，需注意在同一个模式下，dblink 名称不能重复。
user IDENTIFIED BY password：明确远端数据库的用户名和密码，是访问远程数据库的身份凭证。
connect_string：配置连接串信息，提供连接到远程数据库所需的关键信息。
config_tag：配置文件标签名，通过指定配置文件（sys_database_link.conf）中的标签项，从文件获取远程数据库的网络地址、端口以及数据库名称等连接信息。配置文件格式如下：
plaintext

[名称]

DriverName=连接驱动名称。

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

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

Dbname=远程数据库名称。

DbType=远程数据库类型。

其中，DbType支持 Oracle，KingbaseES，Postgres 三种数据库类型。

（二）创建示例
创建可被所有用户使用的 Oracle 数据库连接：
sql

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

直接指定连接串信息创建 Oracle 数据库连接：
sql

CREATE PUBLIC DATABASE LINK mylink CONNECT TO 'SYSTEM' IDENTIFIED BY 'password' USING (DriverName='Oracle ODBC');

三、跨库查询实战技巧
（一）数据类型匹配
本地表与远程表进行 JOIN 操作时，JOIN 字段的数据类型必须保持一致。可通过creat localtable as select * from table@oracle_dblink语句确认远端参与 JOIN 字段在本地对应的数据类型，然后在创建本地表时，使用该类型定义参与 JOIN 的列。

（二）Order by 字段执行规则
在 SQL 语句中，order by 列只有在特定数据类型下才支持下推到远端执行：

Catalog 模式：支持的数据类型包括 int2, int4, int8, tinyint, oid, float4, float8, numeric, date, timestamp, timestamptz, interval。
sys 模式：仅支持 data 类型。
（三）Limit 下推条件
Limit n 语句下推到远端执行需满足以下条件：

Limit 不能用于 group by 语句中。
n 必须为常量，不能是表达式。例如，Select * from table@dblink limit func(num);这种用法无法下推。
（四）系统函数下推规则
部分系统函数在 SQL 语句中可实现下推执行，包括 abs, acos, asin, rpad,rtrim, sqrt, atan, atan2,ceil, ceiling,cos, sign,sin, exp,initcap, char_length, character_length,concat,length,lower, lpad, ltrim,to_char,to_date, to_number, mod, octet_length, position, pow, power,replace,round, strpos, substr, tan,to_timestamp, translate,trunc, upper 等。
为实现更好的下推效果，需注意：

这些函数若出现在查询的目标列（SELECT 后 FROM 之前）中，则无法下推。
下推仅适用于函数在 WHERE 条件中的应用场景。
（五）优化数据传输
为减少数据传输量，提高查询效率，应仅获取需要的列。例如：

sql

-- 较差的方式，传输所有列数据到本地SELECT * FROM large_table@remote_db;-- 较好的方式，指定需要的列进行数据传输SELECT col1, col2 FROM large_table@remote_db WHERE condition;

（六）处理不支持的数据类型
若远端 Oracle 表中存在 KingbaseES 不支持的数据类型（如 bfile 类型），且 SQL 语句不会用到对应列，可在 Oracle 上创建不包含该列的视图，然后在 KingbaseES 中通过 dblink 访问该视图获取数据。
例如，若远端表 table 包含 a，b，c 三个列，c 列为 bfile 类型，直接执行select a,b from table@dblink会报错：column "C" of table "table" has an untranslatable data type。此时，可在 Oracle 上创建视图：

sql

create view table_v as select a, b from table;

然后在 KingbaseES 上通过 dblink 查询该视图：

sql

select a,b from table_v@dblink;

（七）索引使用检查
当查询速度较慢时，可使用explain verbose sql语句获取执行计划，其中的 Oracle query 字段包含相关 SQL 语句。将该语句在远程 Oracle 上直接运行，验证是否能使用 Oracle 中对应表的索引，并根据实际情况调整 SQL 语句，优化查询性能。

四、关键注意事项
（一）插件安装与配置
在查询外部数据库前，必须创建对应的数据库插件，如 kingbase_fdw、oracle_fdw、postgres_fdw、odbc_fdw 等，以确保能够顺利建立与不同类型数据库的连接。

（二）权限管理策略
DBLink 对象权限：对于私有的 DBLink，仅创建者和 DBA 拥有访问权限；以 PUBLIC 方式建立的 DBLink，除创建者外的其他数据库用户均可访问 。
远程访问权限：本地用户对远程数据库对象的访问权限由远程数据库系统的用户认证机制决定，通过 DBLink 连接到远程数据库的本地用户将获得远程数据库用户相应的查询权限。
（三）权限最小化原则
为保障数据库安全，应仅为远程账户授予必要权限，避免因权限过大引发安全风险。

（四）合理使用 DBLink
DBLink 应仅在必要时使用，过度使用可能导致性能问题和维护困难，影响数据库整体运行效率。

（五）性能与网络优化
性能影响：跨库查询受网络延迟影响较大，对于复杂查询，建议在应用层进行处理，以提升查询性能。
网络优化：若通过 DBLink 远程连接 Oracle 时，首次连接缓慢，后续正常，可能是 KingbaseES 所在服务器的 DNS 失效。可采取以下解决方案：
若使用 IP 连接远程 Oracle，可注释掉 KingbaseES 机器上的 DNS 配置（位于 /etc/resolv.conf）。
配置一台可用的 DNS 服务器，并将其信息写入到 /etc/resolv.conf 文件中。
————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/futianxia061112/article/details/147879589