Friday, 28 November 2014

How to connect to Amazon Redshift cluster using psql in a unix script?

How to connect to Amazon Redshift cluster using psql in a unix script and execute a sql statement?


You need to first define the following variables in your unix script and enter appropriate values and then follow it up with the psql command with whatever statement you need to connect to Amazon Redshift cluster using psql ;

AWS_ACCESS_KEY=<ENTER ACCESS KEY>
SECRET_ACCESS_KEY=<ENTER SECRET ACCESS KEY>

db=default
AWS_SERVER=mytest-cluster2.adsadsadh.us-east-1.redshift.amazonaws.com

username=admin

psql -h $AWS_SERVER_ADDRESS -p 8443 -d $db -U $username  -c  "TRUNCATE TABLE PUBLIC.TABLENAME"



Note: if you have AWS_ACCESS_KEY and SECRET_ACCESS_KEY defined as environment variable then you can use psql command as shown below and instead of variables directly enter the address like below to connect to Amazon Redshift cluster using psql.


localhost> export AWS_ACCESS_KEY=<ENTER ACCESS KEY>
localhost> export SECRET_ACCESS_KEY=<ENTER SECRET ACCESS KEY>
localhost> psql -h mytest-cluster2.adsadsadh.us-east-1.redshift.amazonaws.com  -p 8443 -d default -U $username  -c  "TRUNCATE TABLE PUBLIC.TABLENAME"