KingbaseES KDB_Database_Link 使用介绍 (DBLINK)

最新推荐文章于 2025-03-24 17:59:05 发布

nevermore08

最新推荐文章于 2025-03-24 17:59:05 发布

阅读量811

点赞数 10

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/qq_44906157/article/details/135349663

版权

kdb_database_link 是 KingbaseES 为了兼容oracle 语法而开发的跨数据库访问扩展，可用于访问KingbaseES, Postgresql , Oracle 。以下分别介绍跨数据库访问KingbaseES 与Oracle 的配置过程。
注意：database link 支持DML 操作是从V8R6C4B0021 版本开始的，对于两端都是KingbaseES 数据库的，只需要源端是V8R6C4B0021 版本，目标端版本无要求（但必须保证ODBC 版本能够连接）。
转自：https://www.cnblogs.com/kingbase/p/14838316.html

配置ODBC
1.安装ODBC
若操作系统未安装odbc，则需要安装odbc：
查看系统是否安装了odbc：

[root@test ~]# rpm -qa|grep unixODBC
unixODBC-2.3.7-2.ky10.x86_64

安装odbc：

[root@test ~]# yum install unixODBC.x86_64

若是在安装了KES的服务器操作，则无需单独安装ODBC（odbc与kes软件集成一起）
以下操作在kingbase用户操作：

[root@test ~]# su - kingbase 
[kingbase@test ~]$
查看kes数据库版本：
[kingbase@test ~]$ kingbase --version
KINGBASE (KingbaseES) V008R006C004B0021
查看isql版本：
[kingbase@test ~]$ isql --version
unixODBC 2.3.7

查看odbc配置信息；

[kingbase@test ~]$ odbcinst -j
unixODBC 2.3.7
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /home/kingbase/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

或者
[root@test ~]# odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

注意：如果设置了LD_LIBRARY_PATH环境变量，则实际生效的是/usr/local/etc/odbcinst.ini ，否则可能是 /etc/odbcinst.ini
若查询的结果是/etc/odbcinst.ini，则需要修改kingbase用户的环境变量，修改方法如下：

vi /home/kingbase/.bashrc
# 添加数据库软件的lib路径
export LD_LIBRARY_PATH=/home/kingbase/KingbaseES/V8/Server/lib:$LD_LIBRARY_PATH

使配置文件生效
source /home/kingbase/.bashrc

配置
根据odbcinst -j 显示的路径，配置 odbcinst.ini 和 odbc.ini 文件。

[kingbase@test ~]$ odbcinst -j
unixODBC 2.3.7
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /home/kingbase/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

（以下操作用root用户）
编辑odbcinst.ini 文件内容如下：

[root@test ~]# vi /usr/local/etc/odbcinst.ini
# Example driver definitions
 
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/psqlodbcw.so
Setup           = /usr/lib/libodbcpsqlS.so
Driver64        = /usr/lib64/psqlodbcw.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1
 
 
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Driver64        = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1
 
[KingbaseES V8R6 ODBC Driver]
Description     = ODBC for KingbaseES
Driver          = /home/kingbase/KingbaseES/V8/Interface/odbc/kdbodbcw.so
Debug           = 1
CommLog         = 1

编辑.odbc.ini 文件内容如下：kingbase用户

[kingbase@test ~]$ vi /home/kingbase/.odbc.ini
[v8r6]
Description=KingbaseES
Driver=KingbaseES V8R6 ODBC Driver
Host=localhost
Database=test
Username=system
Password=123456
Port=54321

2.isql 验证
必须配置了 odbc.ini ，才能进行此步骤

[kingbase@test ~]$ isql v8r6
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

3.验证驱动
验证kingbase自带的驱动 kdbodbcw.so，确保没有"not found"

[kingbase@test ~]$ ldd /home/kingbase/KingbaseES/V8/Interface/odbc/kdbodbcw.so
ldd: 警告: 你没有执行权限  `/home/kingbase/KingbaseES/V8/Interface/odbc/kdbodbcw.so'
	linux-vdso.so.1 (0x00007ffd8bee8000)
	libpq.so.5 => /home/kingbase/KingbaseES/V8/Interface/odbc/./libpq.so.5 (0x00007f2753745000)
	libpthread.so.0 => /usr/lib64/libpthread.so.0 (0x00007f2753709000)
	libodbcinst.so.2 => /home/kingbase/KingbaseES/V8/Interface/odbc/./libodbcinst.so.2 (0x00007f27534f0000)
	libc.so.6 => /usr/lib64/libc.so.6 (0x00007f2753338000)
	libssl.so.10 => /home/kingbase/KingbaseES/V8/Server/lib/libssl.so.10 (0x00007f27530cd000)
	libcrypto.so.10 => /home/kingbase/KingbaseES/V8/Server/lib/libcrypto.so.10 (0x00007f2752cb8000)
	libcrypt.so.1 => /usr/lib64/libcrypt.so.1 (0x00007f2752c7b000)
	libm.so.6 => /usr/lib64/libm.so.6 (0x00007f2752af8000)
	/lib64/ld-linux-x86-64.so.2 (0x00007f2753c10000)
	libdl.so.2 => /usr/lib64/libdl.so.2 (0x00007f2752af3000)
	libz.so.1 => /usr/lib64/libz.so.1 (0x00007f2752ad9000)

若出现没有执行权限则做如下操作：

[kingbase@test ~]$ chmod +x /home/kingbase/KingbaseES/V8/Interface/odbc/*
[kingbase@test ~]$ ldd /home/kingbase/KingbaseES/V8/Interface/odbc/kdbodbcw.so
	linux-vdso.so.1 (0x00007ffd9fb41000)
	libpq.so.5 => /home/kingbase/KingbaseES/V8/Interface/odbc/./libpq.so.5 (0x00007f99d9f4f000)
	libpthread.so.0 => /usr/lib64/libpthread.so.0 (0x00007f99d9f13000)
	libodbcinst.so.2 => /home/kingbase/KingbaseES/V8/Interface/odbc/./libodbcinst.so.2 (0x00007f99d9cfa000)
	libc.so.6 => /usr/lib64/libc.so.6 (0x00007f99d9b42000)
	libssl.so.10 => /home/kingbase/KingbaseES/V8/Server/lib/libssl.so.10 (0x00007f99d98d7000)
	libcrypto.so.10 => /home/kingbase/KingbaseES/V8/Server/lib/libcrypto.so.10 (0x00007f99d94c2000)
	libcrypt.so.1 => /usr/lib64/libcrypt.so.1 (0x00007f99d9485000)
	libm.so.6 => /usr/lib64/libm.so.6 (0x00007f99d9302000)
	/lib64/ld-linux-x86-64.so.2 (0x00007f99da41a000)
	libdl.so.2 => /usr/lib64/libdl.so.2 (0x00007f99d92fd000)
	libz.so.1 => /usr/lib64/libz.so.1 (0x00007f99d92e3000)

KES到KES（DBLINK）
1.修改 shared_preload_libraries 参数
需将kdb_database_link 加入 shared_preload_libraries 中，注意 kdb_database_link 必须放在最后，因为，与其他项间有依赖关系。
修改方法如下（需要重启数据库）：

[kingbase@test ~]$ vi /home/kingbase/KingbaseES/V8/data/kingbase.conf f

# 数据库参数文件的shared_preload_libraries中添加最后位置
shared_preload_libraries = 'liboracle_parser, synonym, plsql, force_view, plugin_debugger, plsql_plugin_debugger, ora_commands,kdb_ora_expr, sepapower, dblink, sys_kwr, sys_ksh, sys_spacequota, sys_stat_statements, backtrace, kdb_utils_function,kdb_database_link'

2.创建扩展
kdb_database_link 实际是在kingbase_fdw上层进行了包装，需要kingbase_fdw扩展。以system用户连接（需要创建dblink的数据库），创建组件：

[kingbase@test ~]$ ksql -Usystem test
ksql (V8.0)
输入 "help" 来获取帮助信息.

test=# create extension kdb_database_link;
CREATE EXTENSION
test=# create extension kingbase_fdw;
CREATE EXTENSION

kdb_database_link 扩展创建后，会生成 $KINGBASE_DATA/sys_database_link.conf文件，类似于oracle 的tnsnames.ora 文件，用于配置服务名，在创建database link时使用。模板内容如下：

[kingbase@test ~]$ cd /home/kingbase/KingbaseES/V8/data/
[kingbase@test data]$ cat sys_database_link.conf 
#
#	Databaselink configuration file
#
# A databaselink is a set of named connection parameters.  You may specify
# multiple databaselinks in this file.  Each starts with a databaselink name in
# brackets.  Subsequent lines have connection configuration parameters of
# the pattern  "param=value".  A sample configuration for ORADB is
# included in this file.  Lines beginning with '#' are comments.
#
#
#[oradb]
#dbtype=Oracle
#dbname=orcl
#DriverName="Oracle12C ODBC Driver"
#host=127.0.0.1
#port=1521

[kingbaseV8R6]
dbtype=Kingbase
dbname=test
DriverName="KingbaseES V8R6 ODBC Driver"
host=127.0.0.1
port=54321

#[PostgreSQL]
#dbtype=Postgres
#dbname=postgres
#DriverName="PostgreSQL ODBC Driver"
#host=127.0.0.1
#port=5432

创建数据链(a到b的dblink)
目标：从a库访问b库的t1表。
创建用户：

ksql -Usystem test
create user a password '123456';
create user b password '123456';

创建数据库：

create database a owner a;
create database b owner b;
test=# \l
                                  数据库列表
   名称    | 拥有者 | 字元编码 |  校对规则   |    Ctype    |     存取权限      
-----------+--------+----------+-------------+-------------+-------------------
 a         | a      | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 b         | b      | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |

b数据库创建表

ksql -Ub b
create table t1(id int);
insert into t1 values(1);

b=> select * from t1;
 id 
----
  1
(1 行记录)

直接访问：不能跨库访问。

[kingbase@test ~]$ ksql -Ua a
ksql (V8.0)
输入 "help" 来获取帮助信息.

a=# select * from t1;
错误:  关系 "t1" 不存在
第1行select * from t1;
                   ^

a数据库创建扩展：

ksql -Usystem a
create extension kdb_database_link;
create extension kingbase_fdw;

创建dblink

ksql -Usystem a
create public database link a_link connect to 'b' identified by '123456' using ( DriverName = 'KingbaseES V8R6 ODBC Driver' , Host = '192.168.172.140' , Port = 54321 , Dbname = 'b' , Dbtype = 'kingbase');

或者修改sys_database_link.conf，配置正确的连接串，然后创建dblink：

ksql -Usystem a

create public database link a_link connect to 'b' identified by '123456' using 'a_link';

以上两种方式，选择一种即可。
数据链创建完后，会在pg_foreign_server 和 pg_user_mappings 增加相关条目

a=# select * from pg_user_mappings;
 umid  | srvid |          srvname          | umuser | usename |        umoptions         
-------+-------+---------------------------+--------+---------+--------------------------
 16674 | 16673 | dblink_server_a_link_2200 |      0 | public  | {user=b,password=123456}
(1 行记录)

a=# select * from pg_foreign_server;
  oid  |          srvname          | srvowner | srvfdw | srvtype | srvversion | srvacl |                 srvoptions                 
-------+---------------------------+----------+--------+---------+------------+--------+--------------------------------------------
 13740 | sysaudit_svr              |       10 |  13739 |         |            |        | 
 16673 | dblink_server_a_link_2200 |       10 |  16672 |         |            |        | {dbname=b,host=192.168.172.140,port=54321}

在这里插入图片描述
验证数据链
在a数据库进行查询：

a=# select * from t1@a_link;
 id 
----
  1
(1 行记录)

删除数据连接（dblink）
a数据库删除dblink：

drop database link public.a_link;

KES到oracle（dblink）
从KES的test数据库访问Oracle 11g的scott用户下的dept表。
在这里插入图片描述
创建扩展
如果要通过database link 连接访问oracle数据库，除了kdb_database_link外，还需要 oracle_fdw 插件。

ksql -Usystem test
create extension oracle_fdw;
create extension kdb_database_link;

配置sys_database_link.conf

[kingbase@test ~]$ cd /home/kingbase/KingbaseES/V8/data/
[kingbase@test data]$ vi sys_database_link.conf
[oradb]
dbtype=Oracle
dbname=test
DriverName="Oracle ODBC Driver"
host=192.168.172.133
port=1521

配置Oracle的odbc
1、下载 oracle_instantclient 和 instantclient-odbc 。KingbaseES 官方提供的 Oracel_fdw 一般自带了 oracle_instantclient，但不包含instantclient-odbc ，用户如果安装了oracle_fdw插件，只需下载 instantclient-odbc 就行。一般instantclient-odbc 有rpm 和zip两种格式，这里下载zip格式。
下载地址：
https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

在这里插入图片描述
2、安装 ODBC 驱动：将 zip 文件解压，提取 libsqora.so 文件，放在/home/kingbase/KingbaseES/V8/Server/lib目录下
上传odbc的zip包，并且解压缩

在这里插入图片描述
查看驱动包

拷贝驱动包：

[root@test instantclient_19_12]# cp libsqora.so.19.1 /home/kingbase/KingbaseES/V8/Server/lib/
[root@test instantclient_19_12]# chmod u+x /home/kingbase/KingbaseES/V8/Server/lib/libsqora.so.19.1
[root@test ~]# chown kingbase:kingbase /home/kingbase/KingbaseES/V8/Server/lib/*

3、验证ODBC 驱动库文件是否完整

[kingbase@test ~]$ ldd /home/kingbase/KingbaseES/V8/Server/lib/libsqora.so.19.1
	linux-vdso.so.1 (0x00007ffd0cdfd000)
	libdl.so.2 => /usr/lib64/libdl.so.2 (0x00007f41c51a4000)
	libm.so.6 => /usr/lib64/libm.so.6 (0x00007f41c5021000)
	libpthread.so.0 => /usr/lib64/libpthread.so.0 (0x00007f41c5000000)
	libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f41c4fe5000)
	librt.so.1 => /usr/lib64/librt.so.1 (0x00007f41c4fda000)
	libaio.so.1 => /usr/lib64/libaio.so.1 (0x00007f41c4fd5000)
	libresolv.so.2 => /usr/lib64/libresolv.so.2 (0x00007f41c4fba000)
	libclntsh.so.19.1 => /home/kingbase/KingbaseES/V8/Server/lib/libclntsh.so.19.1 (0x00007f41c0eaa000)
	libclntshcore.so.19.1 => /home/kingbase/KingbaseES/V8/Server/lib/libclntshcore.so.19.1 (0x00007f41c0907000)
	libodbcinst.so.2 => /home/kingbase/KingbaseES/V8/Server/lib/libodbcinst.so.2 (0x00007f41c06f1000)
	libc.so.6 => /usr/lib64/libc.so.6 (0x00007f41c0539000)
	/lib64/ld-linux-x86-64.so.2 (0x00007f41c5480000)
	libtirpc.so.3 => /usr/lib64/libtirpc.so.3 (0x00007f41c050a000)
	libnnz19.so => /home/kingbase/KingbaseES/V8/Server/lib/libnnz19.so (0x00007f41bfdc0000)
	libgssapi_krb5.so.2 => /usr/lib64/libgssapi_krb5.so.2 (0x00007f41bfd6a000)
	libkrb5.so.3 => /usr/lib64/libkrb5.so.3 (0x00007f41bfc84000)
	libk5crypto.so.3 => /usr/lib64/libk5crypto.so.3 (0x00007f41bfc6b000)
	libcom_err.so.2 => /usr/lib64/libcom_err.so.2 (0x00007f41bfc65000)
	libkrb5support.so.0 => /usr/lib64/libkrb5support.so.0 (0x00007f41bfc51000)
	libkeyutils.so.1 => /usr/lib64/libkeyutils.so.1 (0x00007f41bfc4a000)
	libcrypto.so.1.1 => /usr/lib64/libcrypto.so.1.1 (0x00007f41bf968000)
	libselinux.so.1 => /usr/lib64/libselinux.so.1 (0x00007f41bf93c000)
	libz.so.1 => /usr/lib64/libz.so.1 (0x00007f41bf922000)
	libsecurity.so.0 => /usr/lib64/libsecurity.so.0 (0x00007f41bf91b000)
	libpcre2-8.so.0 => /usr/lib64/libpcre2-8.so.0 (0x00007f41bf886000)

4、配置用户环境变量：ORACLE_HOME 和 TNS_ADMIN，ORACLE_HOME指向oracle_instantclient（也就是Server下的lib）目录。
ORACLE_HOME= /home/kingbase/KingbaseES/V8/Server/lib
TNS_ADMIN 指向 tnsnames.ora 文件的存放位置，根据你的实际配置。

[kingbase@test ~]$ vi .bashrc
export ORACLE_HOME=/home/kingbase/KingbaseES/V8/Server/lib
export TNS_ADMIN=/home/kingbase

使环境变量生效

[kingbase@test ~]$ source .bashrc

5、新建或修改 tnsnames.ora 文件，增加个tns 条目。类似如下

[kingbase@test ~]$ vi tnsnames.ora
test_ora =
 (DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS =(PROTOCOL = TCP)(Host = 192.168.172.133)(Port = 1521))
  )
  (CONNECT_DATA =(SID = test))
 )

配置odbc的参数文件
1.查看odbc的配置信息

[kingbase@test ~]$ odbcinst -j
unixODBC 2.3.7
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /home/kingbase/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

2.修改odbcinst.ini文件内容（可根据实际情况写）

[root@test ~]# vi /usr/local/etc/odbcinst.ini
[Oracle ODBC Driver]
Description     = ODBC for Oracle
Driver          = /home/kingbase/KingbaseES/V8/Server/lib/libsqora.so.19.1

3.修改.odbc.ini文件内容（可根据实际情况写）
编辑.odbc.ini 文件内容如下：kingbase用户

[kingbase @test ~]# vi /home/kingbase/.odbc.ini
[test_orcl]
Description=Oracle
Driver=Oracle ODBC Driver
ServerName=test_ora
UserID=scott
Password=tiger

4.验证连接

[kingbase@test ~]$ isql -v test_orcl
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

创建database link
登录test库并且创建dblink

ksql -Usystem test 

create public database link ora_link connect to 'scott' identified by 'tiger' using 'oradb';

或者

create public database link ora_link connect to 'scott' identified by 'tiger' using
( DriverName = 'Oracle ODBC Driver' , Host = '192.168.172.133' , Port = 1521 , Dbname = 'test' , Dbtype = 'oracle');

数据链创建后，可以在字典表sys_database_link 与 pg_foreign_server 看到相关信息。

test=# select * from sys_database_link;
  oid  | lnkname  | lnknamespace | lnkuser | lnkowner | lnkserver | dbtype |                                  lnkoptions                                  |
          lnkcreated           
-------+----------+--------------+---------+----------+-----------+--------+------------------------------------------------------------------------------+
-------------------------------
 16424 | ora_link |         2200 |   16423 |       10 |     16422 | oracle | {host=192.168.172.133,port=1521,dbname=test,"DriverName=Oracle ODBC Driver"} |
 2021-09-17 23:48:32.953587+08
(1 行记录)

test=# select * from pg_foreign_server;
  oid  |           srvname           | srvowner | srvfdw | srvtype | srvversion | srvacl |               srvoptions               
-------+-----------------------------+----------+--------+---------+------------+--------+----------------------------------------
 13740 | sysaudit_svr                |       10 |  13739 |         |            |        | 
 16422 | dblink_server_ora_link_2200 |       10 |  16421 |         |            |        | {dbserver=//192.168.172.133:1521/test}
(2 行记录)

在这里插入图片描述
5.验证数据链

test=# select * from scott.dept@ora_link;
 deptno |   dname    |   loc    
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 行记录)