Connect SQL Server to Greenplum Database using Linked Server

In this SQL Server tutorial, I want to show SQL database administrators how to create Linked Server on SQL Server to connect to Pivotal Greenplum database. Pivotal Greenplum is one of the common data warehouse platforms used on premise and on cloud. SQL Server and Greenplum databases can be connected using Linked Server connection from MS SQL Server to Greenplum based on ODBC drivers.

This SQL Server tutorial can be considered in different sections:
Download and install Greenplum ODBC drivers,
Create System DSN using ODBC driver for Pivotal Greenplum database,
Create Linked Server on SQL Server to Greenplum database using DSN record

Download and Install ODBC Driver for Pivotal Greenplum Database

Data professionals can use Pivotal Greenplum ODBC and JDBC drivers to connect to Greenplum databases from other applications like BI Front-End tools, Data Virtualization tools or other data platform like SQL Server, etc.

Administrators can download ODBC and JDBC drivers from Data Direct.
Here is the download link DataDirect Greenplum ODBC Driver

In this SQL tutorial, I am using a SQL Server instance installed on a 64-bit Windows OS
As seen in below screenshot, I download the zipped file for ODBC driver and extract it.

Run the extracted "setup" file to launch ODBC driver installation wizard

The details of Greenplum ODBC driver installation can be found at Download and Install ODBC Driver for Pivotal Greenplum Database

Create ODBC Data Source using Greenplum Driver

After the Greenplum ODBC driver setup is completed, administrators can create ODBC data source that will be used to connect Greenplum database by SQL Server Linked Server. To create the ODBC data source, you can use ODBC Administration application.

Details of defining a new ODBC System DSN entry is shown at tutorial Create Greenplum ODBC Data Source.

Create Linked Server to Greenplum Database on SQL Server

To create a linked server on SQL Server which will enable SQL programmers to query data stored in Pivotal Greenplum data warehouse, first step is to launch SQL Server. Connect to the target SQL Server instance where you want to create the Greenplum linked server.

On Object Explorer window, drill down till "Server Objects > Linked Servers"
Right click on Linked Servers and choose "New Linked Server ..." menu option.

When the New Linked Server creation dialog screen is displayed, on General tab, give a name in "Linked server" textbox to the linked server we want to establish.

Choose "Other data source" server type and as "Provider" select "Microsoft OLE DB Provider for ODBC Drivers"

In "Product name" textbox, type "DataDirect"
Additionally, type the name of the ODBC DSN name in "Data source" textbox

Switch to "Security" tab and provide the Greenplum database user name and its password that will authenticate the Linked Server user to the Greenplum DWH.
I provided "gpadmin" user and its password on "Remote login" and "With password" input boxes

After driver is selected, target Greenplum database information is provided and authentication details are entered, the administrators can finish Linked Server creation step by clicking OK button.

Now let's test if the linked server works successfully.

Under Linked Servers, right click on the linked server entry that we have just created. On context menu, click on "Test Connection" to validate if the SQL Server to Pivotal Greenplum database linked server is successfully created.

If the Linked Server is created successfully created, the test connection to the linked server success message is displayed.

It is possible to see the list of objects accessible by using Linked Server to Greenplum database created on SQL Server.

SQL Server database developers can query Pivotal Greenplum database objects using SQL Select statements similar to following syntax.

As database developers will realize immediately data displayed is read on demand from the pxf_city table in remote Greenplum data source.

In fact what is interesting with this SQL sample is that, the remote data source Greenplum database table is also an external database table created on Greenplum using Greenplum Platform Extension Framework PXF.