Accessing MSSQL with tds_fdw in Klustron

Objective:

Klustron employs Foreign Data Wrappers (FDW) to access a variety of external data sources. This document provides detailed instructions on using the tds_fdw extension to connect to MSSQL databases from Klustron.

01 Introduction to tds_fdw

tds_fdw is a PostgreSQL FDW extension that establishes connections to databases using the TDS (Tabular Data Stream) protocol, applicable to both Sybase and Microsoft SQL Server.

Currently, this version of tds_fdw does not support join pushdowns or write operations. Nonetheless, it enables WHERE conditions and column pushdown when configured with the match_column_names setting.

The workflow is illustrated in the diagram below.

Using the tds_fdw extension allows for straightforward access to data in MSSQL from Klustron and can also be used as a tool for migrating data from MSSQL to Klustron.

02 Specific Configuration Process

Unless otherwise specified, the user performing the operations below is Klustron's installation user, kunlun.

2.1 Installing Free TDS

FreeTDS is a library that enables access to Microsoft MSSQL databases from Linux systems. tds_fdw relies on the FreeTDS interface.

Perform the following operations as the root user:

yum install epel-release
yum install freetds freetds-devel

2.2 Downloading and Installing the tds_fdw Package

During the make/make install process, ensure that the path to pg_config is in the current user’s $PATH environment variable.

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 Loading the tds_fdw Extension

First, create a Klustron user and database.

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

Log in to testdb as user kunlun_test and then load the tds_fdw extension.

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

Check if tds_fdw has been successfully installed.

2.4 Preparing the MSSQL Database

Using the login name testlogin, create a table dbo.employee in the MSSQL database mstest and insert data.

SQL Server version 2014, server IP 192.168.40.156, listening port 1433, database name mstest

2.5 Using tds_fdw in Klustron

Create a foreign server named mssql_server and configure the connection to the MSSQL database.

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

• mssql_server：a custom SERVER NAME

• OPTIONS: include three parameters -- servername、port、database

  • servername (default is 127.0.0.1): The host IP address running the MSSQL database.
  • port (default is 1433): The listening port for the MSSQL database service.
  • database (default is master): The default database to connect to.

View the server that has just been created.

Create a mapping in Klustron to the MSSQL.

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

• kunlun_test: An existing user in Klustron

• mssql_server: The SERVER NAME created

• OPTIONS include two parameters: username and password

  • username (required): The username
  • password (required): The user's password

View the newly created mapping.

Create a foreign table in Klustron associated with the MSSQL table.

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

Note:

• The foreign table's table name and column names do not need to match those in MSSQL, but the order of the fields must align.
• Use single quotes to specify the table name in MSSQL as 'username.table name'.
• row_estimate_method defaults to 'execute', which requires executing a query on the MSSQL side to estimate the number of rows in the foreign table; 'showplan_all' indicates using the MSSQL 'set showplan_all' to obtain statistical information on the number of rows.

After creating the foreign table, you can execute queries to access the corresponding foreign table.

An explain statement shows that the data in mssql_emp is retrieved through a remote query from MSSQL.

END