AWS: Automating queries in redshift

Question

I want to automate a redshift insert query to be run every day.

We actually use Aws environment. I was told using lambda is not the right approach. Which is the best ETL process to automate a query in Redshift.

Answer 1

For automating SQL on Redshift you have 3 options (at least)

Simple - cron Use a EC2 instance and set up a cron job on that to run your SQL code.

psql -U youruser -p 5439 -h hostname_of_redshift -f your_sql_file

Feature rich - Airflow (Recommended) If you have a complex schedule to run then it is worth investing time learning and using apache airflow. This also needs to run on a server(ec2) but offers a lot of functionality.

https://airflow.apache.org/

AWS serverless - AWS data pipeline (NOT Recommended)

https://aws.amazon.com/datapipeline/

Cloudwatch->Lambda->EC2 method described below by John Rotenstein This is a good method when you want to be AWS centric, it will be cheaper than having a dedicated EC2 instance.