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.$$"

cp $1 /tmp/

log () {
CMD=`basename $0`
DATE=`date '+%Y-%m-%d %T'`
echo "$DATE $1 $CMD $2" >> $LOG_FILE
}

END_PT="<Redshift_Server_Cluster_Name>"
DB_NM="<Database_name>"
DST_PATH="s3://s3bucketunica/" -> s3Bucket_name
export AWS_ACCESS_KEY_ID="XXXXNNXXXXXNXXXXXXXN"
export AWS_SECRET_ACCESS_KEY="xXXxNXxXXNXxNX7xxxxNxXxNXXX/XXXXXxxxxxx"
export DSNNAME="<DSN_Name_Created>

#export AWS_DEFAULT_REGION=ap-northeast-1

PART_HOME=`dirname $0`
PART_HOME=${PART_HOME%/shell}

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="' csv"

ERR_CD=1

log "INFO" "aws s3 copy command TABLE_NAME=""$2"""
log "INFO" "aws s3 cp $1 $DST_PATH"

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 $ERR_CD
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.$$
log "INFO" "$COMMAND"
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
RESULT=1
fi
echo "RESULT is $RESULT"

if [ ${RESULT} -ne 0 ]; then
log "ERROR" "executing redshift copy (${RESULT}) FILE_NAME=""$1"" TABLE_NAME=""$2"""
exit $ERR_CD
fi

# remove file from s3?
aws s3 rm $S3_FILE
log "INFO" "aws s3 rm $S3_FILE"

log "INFO" "end script"

echo "LOG_FILE is $LOG_FILE"

exit 0