Amazon Redshift - Loader configurations

Steps to implement Copy Command/Loader utility for Amazon Redshift:

1. User should have Amazon Redshift database. Note down, Database Name, Port and DB user credentials.
2. Create S3 Bucket and make a note of the Bucket name
3. Need Access key ID and Secret access key to access S3 bucket.
4. Find ODBC URL, Database Host, Name and Port.
5. Configure Amazon Redshift in Campaign server. In case aws is not installed (in Linux). Please install awscli.noarch and aws configure packages.
6. Open <CAMPAIGN_HOME>/bin/setenv.sh file and give path for odbc.ini file export ODBCINI=<Path_for_odbc.ini_File>/odbc.ini
7. In Campaign install location create a folder e.g. <CAMPAIGN_HOME>/partitions/partition1/Redshift
8. Make sure below details are updated in 'RedShiftCopyCommand.sh' file

   END_PT="<redshift-host-address>" #CHANGE ME
   	DB_NM="<redshift-db-name>" #CHANGE ME
   	DST_PATH="<s3-bucket-name>" #CHANGE ME
   	export AWS_ACCESS_KEY_ID="<keyid>" #CHANGE ME
   	export AWS_SECRET_ACCESS_KEY="<accesskey>" #CHANGE ME
   	export DSNNAME="<dsnname>" #CHANGE ME
   

9. Now copy 'RedShiftCopyCommand.sh' file in the <CAMPAIGN_HOME>/partitions/partition1/Redshift folder.
10. Changes in Unica Application Configuration:
    • Create Data source for Redshift database using 'PostgreSQLTemplate' (if not already created).
    • In the Datasource provide below details.

      LoaderCommand: <CAMPAIGN_HOME>/partitions/partition1/Redshift/RedShiftCopyCommand.sh <DATAFILE> <TABLENAME> <DBUSER> <PASSWORD>
         LoaderCommandForAppend: <CAMPAIGN_HOME>/partitions/partition1/Redshift/RedShiftCopyCommand.sh <DATAFILE> <TABLENAME> <DBUSER> <PASSWORD>
         LoaderControlFileSpecifiesFields: TRUE
         LoaderDelimiter: ,
         LoaderDelimiterForAppend:

11. Restart application server and Campaign Listener.
12. Access application and verify of Copy/loader is working.
13. Once flowchart is completed successfully, verify the flowchart logs.
14. Make sure redshift loader is invoked.

    Sample example logs:
        [I] [TABLE ACC]  [Snapshot1]  USER (thread 0x7fefdb9a1700): Data retrieval completed; nnnnnn records retrieved and returned to caller. [sdbtacc:439]
        [I] [DB LOAD]    [Snapshot1]  Snapshot: INVOKING DATABASE-SPECIFIC LOADER:
        [I] [DB LOAD]    [Snapshot1]  <Campaign_home>/partitions/partition1/redshift/RedShiftCopyCommand.sh <Campaign_home>/partitions/partition1/tmp/d829aaaq.t~# RedShiftDB_Copy

15. Verify logs for any error.

Sample example of RedShiftCopyCommand.sh loader utilty script.

	LOG_FILE="/tmp/log.$$"
	 
	log () {
	CMD=`basename $0`
	DATE=`date '+%Y-%m-%d %T'`
	echo "$DATE $1 $CMD $2" >> $LOG_FILE
	}
	 
	END_PT="<redshift-host-address>" #CHANGE ME
	DB_NM="<redshift-db-name>" #CHANGE ME
	DST_PATH="<s3-bucket-name>" #CHANGE ME
	export AWS_ACCESS_KEY_ID="<keyid>" #CHANGE ME
	export AWS_SECRET_ACCESS_KEY="<accesskey>" #CHANGE ME
	export DSNNAME="<dsnname>" #CHANGE ME
	 
	TABLE_NM=$2
	USERNAME=$3
	PASSWORD=$4
	 
	FILE_NM=`basename $1`
	S3_FILE=$DST_PATH$FILE_NM
	 
	CMD_CPY="COPY "
	CMD_FRM=" FROM '"
	CMD_CRD="' CREDENTIALS 'aws_access_key_id="
	CMD_SEC=";aws_secret_access_key="
	CMD_END="' delimiter ','"
	 
	aws s3 cp $1 $DST_PATH >> $LOG_FILE 2>&1
	RESULT=$?
	if [ ${RESULT} -ne 0 ]; then
	log "ERROR" "error executing s3 cp (${RESULT}) FILE_NAME=""$1"" TABLE_NAME=""$2"""
	exit 1
	fi
	 
	COMMAND=$CMD_CPY$TABLE_NM$CMD_FRM$S3_FILE$CMD_CRD$AWS_ACCESS_KEY_ID$CMD_SEC$AWS_SECRET_ACCESS_KEY$CMD_END
	echo "$COMMAND" > /tmp/sql.$$
	isql $DSNNAME $USERNAME $PASSWORD -b < /tmp/sql.$$ > /tmp/isql.log.$$ 2>&1
	cat /tmp/isql.log.$$ >>  $LOG_FILE
	errormsg="$(grep '\[ISQL\]ERROR' /tmp/isql.log.$$)"
	 
	if [ -n "$errormsg" ]
	then
	log "ERROR" "executing redshift copy (${RESULT}) FILE_NAME=""$1"" TABLE_NAME=""$2"""
	exit 1
	fi
	 
	aws s3 rm $S3_FILE # remove file from s3
	exit 0