logo - 跳到首页

VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

TDS_FDW

功能描述

Vastbase支持创建外部数据封装器TDS_FDW，用于远程访问SQL Server数据库。

TDS_FDW是一个外部数据封装器（Foreign Data Wrapper，FDW），它允许数据库连接到支持TDS（Tabular Data Stream）协议的外部数据库，如SQL Server和Sybase。

使用TDS_FDW插件能直接访问和操作存储在外部系统中的数据，减少数据冗余和复杂的数据管理需求。

注意事项

  • 目前该功能仅限于对普通表和分区表进行查询操作，且不支持在函数和存储过程中使用外表。

  • 目前在函数体中查询外表时，如果条件语句中包含参数，会导致条件失效，请谨慎使用。

  • 该封装器插件不支持insert into .... select语法。

  • 该封装器插件支持的Vastbase版本：

    • V2.2 Build 15 (Patch No.2)及以后补丁版本。

    • V2.2 Build 16及以后版本。

使用TDS_FDW

  • 使用TDS_FDW需要连接SQL Server数据库，SQL Server数据库请自行安装。

  • 由于使用外部表的后续操作会涉及到使用user mapping，所以还需要在操作系统中使用以下命令生成mapping秘钥。

    vb_guc generate -o usermapping -S default -D $GAUSSHOME/bin

  • 加载TDS_FDW扩展：

    create extension tds_fdw

  • 创建服务器对象：

    CREATE SERVER

  • 创建用户映射：

    CREATE USER MAPPING

  • 创建外表：

    CREATE FOREIGN TABLE

  • 删除外表：

    DROP FOREIGN TABLE

  • 删除用户映射：

    DROP USER MAPPING

  • 删除服务器对象：

    DROP SERVER

  • 删除扩展：

    DROP extension tds_fdw;

示例

前置步骤：

1、在安装Vastbase数据库的操作系统中执行如下命令生成密钥。

vb_guc generate -o usermapping -S default -D $GAUSSHOME/bin

2、在Vastbase数据库中加载TDS_FDW扩展。

create extension tds_fdw;

示例1： Vastbase数据库远程访问SQL Server数据库，查询远端普通表。

1、在SQL Server数据库中创建测试表并插入数据。

CREATE DATABASE YourDatabase;
USE YourDatabase; --数据库
GO

CREATE TABLE remote_table (id INT PRIMARY KEY, year INT, data NVARCHAR(100), amount DECIMAL(10, 2));
GO

INSERT INTO remote_table (id, year, data, amount) VALUES (3, 2022, 'Sample Data 3', 200.00);
INSERT INTO remote_table (id, year, data, amount) VALUES (4, 2022, 'Sample Data 4', 250.00);
INSERT INTO remote_table (id, year, data, amount) VALUES (5, 2023, 'Sample Data 5', 300.00);
INSERT INTO remote_table (id, year, data, amount) VALUES (6, 2023, 'Sample Data 6', 350.00);
INSERT INTO remote_table (id, year, data, amount) VALUES (7, 2024, 'Sample Data 7', 400.00);
INSERT INTO remote_table (id, year, data, amount) VALUES (8, 2024, 'Sample Data 8', 450.00);
GO

2、在Vastbase数据库重创建server mapping。

CREATE SERVER sqlserver_new
FOREIGN DATA WRAPPER tds_fdw
OPTIONS(
    servername '172.16.100.23',   --替换为sql server所在的host，也可以填ip，请根据实际情况自行修改；
    port '1433',   --- sqlserver的默认端口号；
    database 'YourDatabase',         --在sql server中创建的数据库名；
    tds_version '7.4'            --固定值；
);

3、在Vastbase数据库中创建user mapping。

CREATE USER MAPPING FOR user  -- user是Vastbase数据库的用户名，需要按实际情况填写；
SERVER sqlserver_new
OPTIONS (username 'sa', password 'Niu24680@');  -- sa是sql server的默认系统管理员，密码在安装sql server的时候设置；

4、在Vastbase数据库中创建外表。

CREATE FOREIGN TABLE remote_table (
    id INT,
    year INT,
    data TEXT,
    amount NUMERIC
) SERVER sqlserver_new
OPTIONS (table_name 'remote_table');

5、在Vastbase数据库中执行查询。

SELECT id, data, year FROM remote_table;

返回结果为：

NOTICE :Query executed correctly
NOTICE :tds_fdw:Getting results
id |        data     |  year
---+-----------------+-------
3  |   Sample Data 3 |  2022
4  |   Sample Data 4 |  2022
5  |   Sample Data 5 |  2023
6  |   Sample Data 6 |  2023
7  |   Sample Data 7 |  2024
8  |   Sample Data 8 |  2024
(6 rows)

示例2： 数据库远程访问SQL Server数据库，调用存储过程。

1、在SQL Server数据库中创建测试表并插入数据。

CREATE DATABASE test_db;
USE test_db; -- 数据库名;
GO

CREATE TABLE Students (id INT PRIMARY KEY,name VARCHAR(50),age INT,grade VARCHAR(10));
INSERT INTO Students VALUES (1, 'Tom', 20, 'A');
INSERT INTO Students VALUES (2, 'Jack', 18, 'B');
INSERT INTO Students VALUES (3, 'Mary', 19, 'A');
INSERT INTO Students VALUES (4, 'John', 21, 'C');
INSERT INTO Students VALUES (5, 'Emma', 20, 'B');
INSERT INTO Students VALUES (6, 'Sophia', 19, 'A');
GO

2、在Vastbase数据库中创建server mapping。

CREATE SERVER sqlserver_new
FOREIGN DATA WRAPPER tds_fdw
OPTIONS(
    servername '172.16.100.23',   --替换为sql server所在的host，也可以填ip，请根据实际情况自行修改；
    port '1433',   --- sql server的默认端口号；
    database 'YourDatabase',         --在sql server中创建的数据库名；
    tds_version '7.4'            --固定值；
);

3、在Vastbase数据库中创建user mapping。

CREATE USER MAPPING FOR user  -- user是Vastbase数据库的用户名，需要按实际情况填写；
SERVER sqlserver_new
OPTIONS (username 'sa', password 'Niu24680@');  -- sa是sql server的默认系统管理员，密码在安装sql server的时候设置；

4、在Vastbase数据库中创建外表。

CREATE FOREIGN TABLE Students (
id INT,
name VARCHAR(50),
age INT,
grade VARCHAR(10)
) SERVER sqlserver_new
OPTIONS (table_name 'Students');

5、在Vastbase数据库中创建存储过程查询远端表。

CREATE OR REPLACE PROCEDURE sp1()
AS
DECLARE
result Students%ROWTYPE;
BEGIN
FOR result IN SELECT * FROM Students LOOP
RAISE NOTICE 'id: %, name: %, age: %, grade: %',
result.id, result.name, result.age, result.grade;
END LOOP;
END;
/

6、在Vastbase数据库中调用存储过程。

CALL sp1();

返回结果为：

NOTICE :    tds_fdw: Query executed correctly
CONTEXT:    PL/pgSQL function public.spl() line 8 at FOR over SELECT rows
CONTEXT:    tds_fdw: Getting results
CONTEXT:    PL/pgSQL function public.spl() line 8 at FOR over SELECT rows
NOTICE :    id:1,name:Tom,age:20,grade:A
NOTICE :    id:2,name:Jack,age:18,grade:B
NOTICE :    id:3,name:Mary,age:19,grade:A
NOTICE :    id:4,name:John,age:21,grade:C
NOTICE :    id:5,name:Emma,age:20,grade:B
NOTICE :    id:6,name:Sophia,age:19,grade:A
  sp1
------
(1 row)