PXF Access SQL Server

This example demonstrates how to use PXF to connect to SQLServer 2016

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

From https://docs.microsoft.com/en-us/sql/connect/jdbc/using-the-jdbc-driver?view=sql-server-ver15 Download jdbc driver: sqljdbc4.jar Put the driver into the PXF library directory (required for all nodes):

cp sqljdbc4.jar $PXF_CONF/lib

Synchronous configuration to other nodes:

pxf cluster sync

3. Start and view the cluster status

Start the node:

pxf cluster start

Check the cluster status:

pxf cluster status

4. Visit

Create a plugin:

postgres=# CREATE EXTENSION pxf_fdw ;
CREATE EXTENSION

Definition 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 map:

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

Create an external 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}');

Access SQL Server data through external tables:

SELECT * FROM foreign_sqlserver limit 10;