Amazon Redshift - Loader configurations
Steps to implement Copy Command/Loader utility for Amazon Redshift:
- User should have Amazon Redshift database. Note down, Database Name, Port and DB user credentials.
- Create S3 Bucket and make a note of the Bucket name
- Need Access key ID and Secret access key to access S3 bucket.
- Find ODBC URL, Database Host, Name and Port.
- Configure Amazon Redshift in Campaign server. In case aws is not installed (in Linux). Please install awscli.noarch and aws configure packages.
- Open <CAMPAIGN_HOME>/bin/setenv.sh file and give path for odbc.ini file export ODBCINI=<Path_for_odbc.ini_File>/odbc.ini
- In Campaign install location create a folder e.g. <CAMPAIGN_HOME>/partitions/partition1/Redshift
- 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
- Now copy 'RedShiftCopyCommand.sh' file in the <CAMPAIGN_HOME>/partitions/partition1/Redshift folder.
- 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:
- Restart application server and Campaign Listener.
- Access application and verify of Copy/loader is working.
- Once flowchart is completed successfully, verify the flowchart logs.
- 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
- 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