SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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.

Progress DataDirect ODBC drivers for Greenplum database

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

ODBC driver for Greenplum database

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.

Greenplum System DSN created using ODBC driver

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.

create new Linked Server on SQL Server

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"

SQL Server Linked Server for Greenplum database

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

SQL Server Linked Server authentication on Greenplum

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.

test Greenplum Linked Server connection

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

Linked Server created successfully

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

Greenplum Linked Server on SQL Server

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

SELECT * FROM [GREENPLUM]..[public].[pxf_city]
Code

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

SQL query on Greenplum database tables using Linked Server

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.