Pivotal Knowledge Base

Follow

How to setup ODBC connections to Greenplum using DataDirect and Engage Tableau

Environment

 Product  Version
 Pivotal Greenplum   4.3.x
 Pivotal HAWQ   1.x 
 DataDirect   7.1.x

Purpose 

When trouble-shooting on DataDirect ODBC or a Tableau issue, you can follow the procedure below to setup the lab for ODBC connections to Greenplum/HAWQ using DataDirect and Engage Tableau.

** Pivotal provides OEM DataDirect drivers for Greenplum. Pivotal Support needs to engage with the customer and provide Support for any issues/questions. For any escalations, we need to reach out to DataDirect Support and create a Support case with them.

** Tableau is a visualization tool. There are two types - Tableau Public (No licensee, free) and Tableau Desktop (Only 14 days trial). Only Tableau Desktop provides the DataSource connection for Greenplum. You can search their Knowledge Base from their online help for more information. 

Procedure

Step 1: Download the "Greenplum Database 4.x - ODBC Driver for XXX" under the "Greenplum DataDirect Connectivity Drivers" folder.

Step 2: Click on "DataDirect ODBC Drivers 7.1.4 README" in the same folder. Follow this README file to install the ODBC for Greenplum, below are some tips that might be helpful:

* Start from "Linux Installation"
* Serial numbers/license keys also provided in this README file

Step 3: Configure datasource information in "odbc.ini"

[fzou]                   <-- Update your Datasource Name here
Database=gpadmin         <-- Update your Database Name here 
HostName=localhost <-- Update your Host Name here PortNumber=5432 <--
Update your Port Number here

Step 4: Verify the driver version and run the test driver connection. When you verify the driver version, you may get this error:

[root@gpdb-sandbox bin]# ./ddtestlib /usr/local/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddgplm27.so
./ddtestlib: error while loading shared libraries: libstdc++.so.5: cannot open shared object file: No such file or directory

You will need to download the libstdc++.so.5 package to fix it, you can run the command below for that:

[root@gpdb-sandbox bin]# yum whatprovides libstdc++.so.5
Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
* base: mirrors.163.com
* epel: mirror.rise.ph
* extras: mirrors.sohu.com
* updates: mirrors.aliyun.com
compat-libstdc++-33-3.2.3-69.el6.i686 : Compatibility standard C++ libraries
Repo : base
Matched from:
Other : libstdc++.so.5

compat-libstdc++-33-3.2.3-69.el6.i686 : Compatibility standard C++ libraries
Repo : installed
Matched from:
Other : Provides-match: libstdc++.so.5

[root@gpdb-sandbox bin]# yum list compat-libstdc++-33
Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
* base: mirrors.163.com
* epel: mirror.rise.ph
* extras: mirrors.sohu.com
* updates: mirrors.aliyun.com
Installed Packages
compat-libstdc++-33.i686 3.2.3-69.el6 @base
Available Packages
compat-libstdc++-33.x86_64 3.2.3-69.el6 base
[root@gpdb-sandbox bin]# yum install compat-libstdc++-33.x86_64
Loaded plugins: fastestmirror, security
Setting up Install Process
Loading mirror speeds from cached hostfile
* base: mirrors.163.com
* epel: mirror.rise.ph
* extras: mirrors.sohu.com
* updates: mirrors.aliyun.com
Resolving Dependencies
--> Running transaction check
---> Package compat-libstdc++-33.x86_64 0:3.2.3-69.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved
============================================================================================
Package Arch Version Repository Size
============================================================================================
Installing:
compat-libstdc++-33 x86_64 3.2.3-69.el6 base 183 k

Transaction Summary
============================================================================================
Install 1 Package(s)

Total download size: 183 k
Installed size: 806 k
Is this ok [y/N]: y
Downloading Packages:
compat-libstdc++-33-3.2.3-69.el6.x86_64.rpm | 183 kB 00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : compat-libstdc++-33-3.2.3-69.el6.x86_64 1/1
Verifying : compat-libstdc++-33-3.2.3-69.el6.x86_64 1/1

Installed:
compat-libstdc++-33.x86_64 0:3.2.3-69.el6

Complete!
[root@gpdb-sandbox bin]# ./ddtestlib /opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddgplm27.so
Load of /opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddgplm27.so successful, qehandle is 0xB6D060
File version: 07.15.0227 (B0276, U0184)
[root@gpdb-sandbox bin]# cd /opt/Progress/DataDirect/Connect64_for_ODBC_71/samples/example
[root@gpdb-sandbox example]# ./example
./example DataDirect Technologies, Inc. ODBC Example Application.
Enter the data source name : fzou
Enter the user name : gpadmin
Enter the password : ******
Enter SQL statements (Press ENTER to QUIT)
SQL> select * from de_test;
num a b sum
2 2005-10-19 10:23:542005-10-20 01:23:54.00000010100
4 2007-10-19 10:23:542007-10-20 01:23:54.00000010300
1 2004-10-19 10:23:542004-10-20 01:23:54.00000010000
3 2006-10-19 10:23:542006-10-20 01:23:54.00000010200
5 2008-10-19 10:23:542008-10-20 01:23:54.00000010400

Step 5: Download the Tableau Desktop (Free 14-day trial) and configure the Datasource as below

Screen_Shot_2017-05-24_at_2.58.14_PM.png 

Step 6: You can manipulate the data in the "Data Source" Tab and create CUBE/Report in the "Sheet" Tab

Screen_Shot_2017-05-24_at_3.00.50_PM.png

Additional Information 

The documentation for DataDirect Connect for ODBC and DataDirect Connect64 for ODBC PostgreSQL Wire Protocol driver (The PostgreSQL Wire Protocol driver): http://media.datadirect.com/download/docs/odbc/allodbc/index.html#page/odbc%2Fthe-postgresql-wire-protocol-driver.html%23

Comments

Powered by Zendesk