Script to Load a table from MySQL to Amazon Redshift with Alerting.

etl_mysql_redshift.sh

#!/bin/sh

credentials()
{
  username=loader
  password=L0#De&1234
  hostname=127.0.0.1
  database=yourdatabasename

  postgres_user=postgresusr
  postgres_host=my-redshift-cluster.f3uir8kw2jdw.ap-southeast-1.redshift.amazonaws.com
  postgres_port=5439
  redshift_database=segdatabase

  slack_webhook=https://hooks.slack.com/services/X82X2N2RR/URNAA6L9U/G1dvFd3I9he5VpVL7bYNNppZ

  mail_from=loader
  mail_to="[email protected]"
  mail_error="[email protected]"
  sendgrid_username=sendgridusr
  sendgrid_password=sendgridpwd
  sentby=whateverusername
}

logging()
{
  logdate=`date +'%d-%m-%Y_%H-%M-%S'`
}

mysql_export()
{
  mysql -u$username -p$password -h$hostname $database -e "select * from yourdatabase.yourtable into outfile '/tmp/yourtable.txt' FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'"

  if [ "$?" != "0" ]
  then
    echo "Error Loading .csv Data."
    return 0
  else
    echo "Data successfully exported to local disk."
  fi
}

remove_residual_files()
{
  if [ -e /tmp/yourtable.txt ]
  then
    mv /tmp/yourtable.txt /usr/local/gists/yourtable$logdate.txt
    echo "File moved to archive directory"
  fi

  if [ -e /tmp/yourtable.txt.gz ]
  then
    mv /tmp/yourtable.txt.gz /usr/local/gists/yourtable$logdate.txt.gz
    echo "Compressed file moved to archive directory"
    return 0
  fi

  if [ "$?" = "0" ];
  then
      aws s3 rm $s3bucket/yourtable.txt.gz
      echo "File removed from S3."
      return 0
  fi
}

compress()
{
  gzip /tmp/yourtable.txt

  if [ -e /tmp/yourtable.txt.gz ]
  then
    echo "File successfully compressed."
  else
    echo "File compression failed."
  fi
}

send_to_s3()
{
  s3bucket=s3://yours3bucket/yourfolder

  aws s3 ls $s3bucket/yourtable.txt.gz

  aws s3 cp /tmp/yourtable.txt.gz $s3bucket/yourtable.txt.gz

  aws s3 ls $s3bucket/yourtable.txt.gz

  if [ "$?" = "0" ];
  then
    echo "File sucessfully uploaded to S3 bucket."
  else
    echo "Upload to S3 bucket failed."
  fi
}

truncate_redshift_table()
{
    psql -U$postgres_user -h $postgres_host -p $postgres_port $redshift_database -c "truncate table yourdatabase.yourtable;"
    if [ "$?" != "0" ];
    then
      echo "Table truncated on Redshift."
    fi
}

upload_to_redshift()
{
    psql -U$postgres_user -h $postgres_host -p $postgres_port $redshift_database -c "copy yourdatabase.yourtable from '$s3bucket/yourtable.txt.gz' credentials 'aws_access_key_id=ajgaslgkshtasj;aws_secret_access_key=shfs/34fiethajthaslthksthask' delimiter '|' gzip removequotes ESCAPE ACCEPTINVCHARS ACCEPTANYDATE;"
    if [ "$?" = "0" ];
    then
      echo "File successfully imported in Redshift."
    fi
}

push_notification()
{
  channel=teamxyz
  slack_username="ETL Alert"
  failureText="Failure: Data for Your Table failed to Upload on Redshift"
  escapedText=$(echo $failureText | sed 's/"/\"/g' | sed "s/'/\'/g")
  json="{\"username\":\"$slack_username\",\"text\": \"$escapedText\"}]}"
  curl -s -d "payload=$json" "$slack_webhook"
}

send_email(){
    # sending the summary via mail
    /usr/local/bin/mailsend -auth -port 587 -domain $HOSTNAME -to "$mail_to" -sub "Redshift Sync Summary Report for '$prev_date'" -smtp smtp.sendgrid.net -user $sendgrid_username -pass $sendgrid_password -f $sentby@$mail_from -M "Your Table Load Summary"
}

execute()
{
  credentials
  logging
  mysql_export
  compress
  send_to_s3
  truncate_redshift_table
  upload_to_redshift
  push_notification
  send_email
  remove_residual_files
}

execute