Sunday, 14 June 2015

How to connect to amazon redshift using Informatica power center?

How to connect to  amazon redshift using Informatica power center?


You can connect to amazon redshift from the drivers available on Informatica cloud. However, if you want to connect Informatica power center 9.1 and prior version  to redshift, using Postgres odbc drivers are an option. You can use DataDirect 6.1 PostgreSQL Wire Protocol and later versions for this purpose.

Step1: Install the postgres driver and create an entry in .odbc.ini


Some of the parameters you might have to set in your odbc (.odbc.ini) connection is below: Check you driver for the complete settings:

[PostgreSQL]
Driver=/informatica/server91/ODBC6.1/lib/DWpsql25.so
Description=DataDirect 6.1 PostgreSQL Wire Protocol
Database=mydatabasename
0HostName=myreshifthost.cqa0adasda-east-1.redshift.amazonaws.comLogonID=myuser_name
Password=mypassword
PortNumber=yourportnumber
TrustStore=/export/redshift-ssl-ca-cert.pem
PacketSize=32


You can contact Informatica or read the driver documentation for the full settings.

2) Create a relational odbc connection from Informatica workflow manager as shown below: