Greenplum With Informatica


Currently the Greenplum 4.2 version is supported with Informatica 9.1 Hot Fix 6 and Informatica 9.5 Hot Fix 2. There are two ways to connect to Greenplum from Informatica
  • First one is through ODBC drivers available in informatica ODBC setup. For mass loading performance wise ODBC is slow for loading the data of around 5GB , GP load utility will take 8 minutes and ODBC will take 22 minutes.
  • Second option would be to connect Natively by purchasing the license for Greenplum pwx and Gpload utility for mass loading.
- Install the gpload utility on Informatica server which would take hardly 50 MB of Space.
- Install the Greenplum pwx on Informatica server it doesn't hold much memory as two files will be kept in /server/bin directory which will be used for registery.

GP load utility installation :

Create a directory called GPLOADER
/NAS/Informatica/GPLOADER/
Place the gpload software dump  greenplum-loaders-4.2.2.0-build-5-RHEL5-x86_64.zip in above directory.

Unzip the greenplum-loaders-4.2.2.0-build-5-RHEL5-x86_64.zip

-rwxr-xr-x 1 informat informat 14484741 Jun 15  2012 greenplum-loaders-4.2.2.0-build-5-RHEL5-x86_64.bin
-rw-r--r-- 1 informat informat  1277122 Sep  6 23:40 gpsd_tbwgr_20130904.sql.gz
-rw-r--r-- 1 informat informat 14324949 Sep  6 23:41 greenplum-loaders-4.2.2.0-build-5-RHEL5-x86_64.zip

run the below binary file
./greenplum-loaders-4.2.2.0-build-5-RHEL5-x86_64.bin

Give the installation path as /NAS/Informatica/GPLOADER/
Which will intstall the gpload utility by creating the below files and directories in the below path

/NAS/Informatica/GPLOADER/

ls -ltr

drwxr-xr-x 2 informat informat   1024 Jun 15  2012 lib
drwxr-xr-x 3 informat informat     96 Jun 15  2012 ext
drwxr-xr-x 3 informat informat     96 Jun 15  2012 bin
-r--r--r-- 1    informat informat    186602 Jun 15  2012 LICENSE.thirdparty
-r--r--r-- 1     informat informat    50349 Jun 15  2012 GPDB-LICENSE.txt
-r--r--r-- 1     informat informat   1081 Sep  9 10:30 greenplum_loaders_path.sh

Take the backup of the .profile file and edit the .profile file and add the below entries.

#GPLOAD
GPHOME_LOADERS=/NAS/Informatica/GPLOADER/
export GPHOME_LOADERS
PYTHONPATH=$GPHOME_LOADERS/bin/ext
export PYTHONPATH

PATH=$GPHOME_LOADERS/bin:$GPHOME_LOADERS/ext/python/bin:$PATH

LD_LIBRARY_PATH =$GPHOME_LOADERS/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH

Run the .profile file.

. .profile
 
To Install the Greenplum PWX

Create a directory /NAS/Informatica/PWXGP3012
Place the Greenplum3012.zip file in the above directory.
unzip Greenplum3012.zip

Go to the directory 3.0.1.2 created by system
cd /NAS/Informatica/PWXGP3012/3.0.1.2/

You will find the below files select the file based on your Operating system

ls -ltr 

-rw-r--r-- 1 informat informat  103770523 Apr 22 21:57 3012_Greenplum_for_PWX_Installer_aix-ppc64.zip
-rw-r--r-- 1 informat informat  46936988 Apr 22 21:57 3012_Greenplum_for_PWX_Installer_linux-x64.zip
-rw-r--r-- 1 informat informat  82487341 Apr 22 21:58 3012_Greenplum_for_PWX_Installer_solaris-sp64.zip
-rw-r--r-- 1 informat informat  37353912 Apr 22 21:58 3012_Greenplum_for_PWX_Installer_winem-64t.zip

unzip 3012_Greenplum_for_PWX_Installer_linux-x64.zip

$ ls -ltr
total 264213

drwxr-xr-x 2 informat informat      1024 Dec 17  2012 properties
-rw-r--r-- 1    informat informat       107 Dec 17  2012 install.sh
drwxr-xr-x 4 informat informat        96 Dec 17  2012 Server
drwxr-xr-x 2 informat informat      1024 Dec 17  2012 Messages
-rw-r--r-- 1    informat informat 103770523 Apr 22 21:57 3012_Greenplum_for_PWX_Installer_aix-ppc64.zip
-rw-r--r-- 1    informat informat  46936988 Apr 22 21:57 3012_Greenplum_for_PWX_Installer_linux-x64.zip
-rw-r--r-- 1    informat informat  82487341 Apr 22 21:58 3012_Greenplum_for_PWX_Installer_solaris-sp64.zip
-rw-r--r-- 1   informat informat  37353912 Apr 22 21:58 3012_Greenplum_for_PWX_Installer_winem-64t.zip
drwxr-xr-x 3   informat informat        96 Apr 23 10:26 source

unzip greenplum-pwx-3.0.1.0-build-3-RHEL5-x86_64.bin

In the middle it will ask for the Informatica Installation directory, Please provide the $INF_HOME absolute path.
  • Once the installation completes please confirm whether below files have successfully  
  1. $INFA_HOME/server/bin/pmgpwriter.so
  2. $INFA_HOME/server/bin/Plugin/GreenplumConnector.xml 
  • Once you verify the above files next is to register the greenplum plugin to repository.
  1. Connect to the repository using the pmrep connect command 
  2.  Register the plug in using the  pmrep registerplugin
 pmrep -r Reposiory_name -d domain_name -n user_name -x password

 pmrep registerplugin –I $INFA_HOME/server/bin/Plugin/GreenplumConnector.xml -e –N

 Go to Workflow Manager Relational Connection search for Greenplum Connection and create the new connection.










Popular Posts

Featured Post

Basic unix commands used in Administration

ls –ltr                Shows all the files and sub directories in the  current directory. ls –la                  Shows all the hidden...