How to execute scheduled SQL scripts in on Amazon Redshift

Question

I have a series of queries to be executed every hour automatically in Redshift Most of them are aggregation on my tables.

I have tried using AWS Lambda with CloudWatch Events, but Lambda functions only survive for 5 minutes max and my queries can take up to 25 minutes

Answer 1

I had faced the same problem earlier. You can use R or Python for that.

You need to install package RpostgreSQL and connect to your Redshift:

drv <- dbDriver("PostgreSQL")
conn <-dbConnect(drv,host='mm-stats-1.ctea4hmr4vlw.us-east-1.redshift.amazonaws.com',port='5439',dbname='stats',user='xxx',password='yyy')

and then you can build report with markdown and then scheduled it with crontab task. Also if it helps I have used the mailR package to send the report to other users