Automate SQL Query to send email on Redshift

Question

I am beginner in AWS (from Microsoft domain). I want to run a SQL query against Redshift tables to view duplicates in table on daily basis and send results out in email to a Prod Support group.

Please advise, what is right way to proceed on this.

Answer 1

There is no in-built capability with Amazon Redshift to do this for you (eg no stored procedures).

The right way is to write a program that queries Redshift and then sends an email.

I see that you tagged your question with aws-lambda. I'd say that a Lambda function would not be suitable here because it can only run for a maximum of 5 minutes and that might be longer than you need your analysis to run.

Instead, you could run the program from an Amazon EC2 instance, or from any computer connected to the Internet.

Answer 2

Recommend doing this with either AWS Lambda or AWS Batch. Use one of these services to issue a short query on a schedule and send the results if required.

Lambda is ideally for simple tasks that complete quickly. https://aws.amazon.com/lambda/ Note that Lambda charges by duration has very tight limits on how long a step can run. A basic skeleton for connecting to Redshift in Lambda is provided in this S.O. answer: Using psycopg2 with Lambda to Update Redshift (Python)

Batch is useful for more complex or long running tasks that need to complete in a sequence. https://aws.amazon.com/batch/