Release History
Help

YMatrix

Quick Start

Deployment

Data Usage

Manage Clusters

Security

Best Practice

Technical Principles

Advanced Features

Guide

SQL Reference

FAQ

Accessing SQL Server with PXF

This example demonstrates how to connect to SQL Server 2016 using PXF.

1. PXF Installation

sudo yum install pxf-matrixdb3-5.16.1-1.el7.x86_64.rpm -y
sudo mkdir /usr/local/pxfconf
sudo chown mxadmin.mxadmin -R /usr/local/pxfconf
sudo chown mxadmin.mxadmin -R /usr/local/pxf-matrixdb4

export PXF_HOME=/usr/local/pxf-matrixdb4
export PXF_CONF=/usr/local/pxfconf
export PATH=$PXF_HOME/bin:$PATH

cd $PXF_CONF/servers
mkdir sqlserv_server

2. Configuration

Initialize PXF:

pxf cluster init

Download the JDBC driver from:
https://docs.microsoft.com/en-us/sql/connect/jdbc/using-the-jdbc-driver?view=sql-server-ver15
File: sqljdbc4.jar

Copy the JDBC driver to the PXF library directory (required on all nodes):

cp sqljdbc4.jar $PXF_CONF/lib

Synchronize configuration to all cluster nodes:

pxf cluster sync

3. Start and Check Cluster Status

Start PXF services on all nodes:

pxf cluster start

Check cluster status:

pxf cluster status

4. Access SQL Server

Create the extension:

postgres=# CREATE EXTENSION pxf_fdw ;
CREATE EXTENSION

Define the foreign server:

DROP SERVER sqlserv_server CASCADE;

CREATE SERVER sqlserv_server FOREIGN DATA WRAPPER jdbc_pxf_fdw OPTIONS (
    jdbc_driver 'com.microsoft.sqlserver.jdbc.SQLServerDriver',
    db_url 'jdbc:sqlserver://{ip}:{port};databaseName={database_name}',
    batch_size '10000',
    fetch_size '2000'
);

Create a user mapping:

CREATE USER MAPPING FOR mxadmin SERVER sqlserv_server OPTIONS (user '{username}', pass '{password}');

Create a foreign table:

CREATE FOREIGN TABLE foreign_sqlserver (
    id int4,
    banzu varchar(20),
    banzu_name varchar(50),
    realdate timestamp
)
SERVER sqlserv_server
OPTIONS (resource '{source_database_schema.table_name}');

Query data from SQL Server through the foreign table:

SELECT * FROM foreign_sqlserver LIMIT 10;
← Previous
PXF Access Oracle
Next →
Automated Downgrade Storage