Klustron使用tds_fdw访问MSSQL

本文目标：

Klustron提供FDW(Foreign Data Wrappers) 作为访问各种外部数据源的机制。本文介绍如何在Klustron中使用tds_fdw 访问MSSQL数据库。

01 tds_fdw介绍

tds_fdw扩展是 PostgreSQL FDW，可以连接到使用TDS（Tabular Data Stream）协议的数据库，例如 Sybase 数据库和 Microsoft SQL Server。

当前版本尚不支持 JOIN下推或写操作。当tds_fdw启用match_column_names 时，它可以支持 WHERE 条件和列下推。

具体的工作流程见下图。

使用tds_fdw扩展可以方便地从Klustron中访问MSSQL中的数据，并且还可以用作MSSQL到Klustron数据迁移的工具。

02 具体配置过程

下文中操作用户如无特别注明，则都是Klustron的安装用户kunlun。

2.1 安装Free TDS

FreeTDS 是一个程序库，可以实现在 Linux系统下访问微软的MSSQL数据库。tds_fdw依赖FreeTDS的接口。

使用root用户执行下面的操作：

yum install epel-release
yum install freetds freetds-devel

2.2 下载并安装tds_fdw软件包

在make/make install的时候，需要确保pg_config的路径存在于当前用户的$PATH环境变量中。

mkdir -p /kunlun/fdw/mssql
cd /kunlun/fdw/mssql
git clone https://github.com/tds-fdw/tds_fdw.git
cd tds_fdw
make USE_PGXS=1
make USE_PGXS=1 install

2.3 装载tds_fdw扩展

首先创建Klustron用户和数据库。

psql -h 192.168.40.152 -p 47001 postgres
create user kunlun_test with superuser with password 'kunlun';
create database testdb owner kunlun_test;
grant all privileges on database testdb to kunlun_test;
\q

以用户kunlun_test登陆testdb，然后装载tds_fdw扩展。

psql -h 192.168.40.152 -p 47001 -U kunlun_test testdb
CREATE EXTENSION tds_fdw;

检查tds_fdw是否安装成功。

2.4 MSSQL数据库准备

使用登陆名testlogin在MSSQL的mstest数据库中创建表dbo.employee，并插入数据。

版本为SQL Server 2014，服务器IP为192.168.40.156，监听端口1433，数据库名为mstest

2.5 在Klustron中使用tds_fdw

创建外部服务器server，配置到Oracle数据库的连接。

CREATE SERVER mssql_server
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (servername '192.168.40.156', port '1433', database 'mstest');

• mssql_server：自定义一个 SERVER NAME

• OPTIONS: 有3个参数--servername、port、database

• • servername(默认为127.0.0.1)：运行MSSQL数据库的主机IP地址。
  • port(默认为1433): MSSQL数据库服务的监听端口。
  • database(默认为master): 默认连接的数据库

查看已经创建的Server。

创建Klustron到MSSQL之间的映射。

CREATE USER MAPPING FOR kunlun_test SERVER mssql_server OPTIONS (username
'testlogin', password 'oracle');

• kunlun_test：Klustron中已存在的用户

• mssql_server：已创建的 SERVER NAME

• OPTIONS：有2个参数--username、password

• • username(必需)：用户名
  • password(必需)：用户的密码

查看刚刚创建映射关系。

在Klustron中创建外部表与MSSQL表关联。

CREATE FOREIGN TABLE mssql_emp (
    id integer,
    name varchar)
SERVER mssql_server
OPTIONS (table_name 'dbo.employee', row_estimate_method 'showplan_all');

请注意：

• 外部表的表名和字段名不需要与MSSQL的表保持一致，但是字段顺序需要与 MSSQL的表保持一致
• table_name使用单引号指定在MSSQL中表对应的用户名.表名
• row_estimate_method 默认是execute, 表示需要在MSSQL端执行查询操作来获取外部表估计的行数；而showplan_all则表示在MSSQL执行set showplan_all去获取统计信息对应的行数

创建外部表完毕后，可以执行查询访问对应的外部表。

通过explain可以看到mssql_emp中的数据都是经由remote query从MSSQL中获取到的。

END