Redshift Serverless Query Editor V2 - Query Scheduler to have dependencies

Question

Good day,

I am trying to build a no/zero code architecture, i was planning to use MWAA for orchestration but i was tasked to look at alternatives keeping it simple
I was hoping Redshift query schedular was similar to SQL Agent, where a job is defined with multiple steps and it can be scheduled with the dependency of previous step etc.

How can I make this work with the scheduler? where I have an example of 50 scripts, but they run sequentially based on the previous script else go to step?
Can one create a job within the schedule that has multiple steps like SQL Agent/Airflow?
Not keen on MWAA/Cloud watch/lambda, looking for a simple way to run my sql that pulls data from RDS using FDW SP/Functions

Please help, any links or examples

Regards

Accepted Answer

Hello good evening,

Thank you for your message and questions. There are some alternatives in AWS that provide you a simple, codeless and cost effective solution to process SQL scripts running against Amazon Redshift. Below some examples that you can use:

a) Orchestrate SQL scripts running in Amazon Redshift using AWS Step Functions: this blogpost describes an effective solution in AWS to orchestrate SQL scripts with dependencies running in Amazon Redshift using Redshift Data API. You have control of aspects of your orchestration such as parallelism, dependency, error handling, retry, etc. Besides, AWS Step Functions has integration natively with other services that you don't need to use code to trigger notifications (for example).

- Example 1 - Blog Post Link: https://aws.amazon.com/blogs/big-data/etl-orchestration-using-the-amazon-redshift-data-api-and-aws-step-functions-with-aws-sdk-integration/
- Example 2 - Blog Post Link: https://aws.amazon.com/blogs/big-data/accelerate-orchestration-of-an-elt-process-using-aws-step-functions-and-amazon-redshift-data-api/
- Redshift Data API limits: https://docs.aws.amazon.com/redshift/latest/mgmt/data-api.html

b) Redshift Scheduler: You can schedule a SQL script to run in Amazon Redshift using Redshift Scheduler. This approach will use also the Redshift Data API to run the SQL scripts in the cluster. You can have SQL statements separated by ";" and they will run sequentially in Amazon Redshift. However, I would suggest this solution for specific cases such as schedule operations in Amazon Redshift (for example). If you purpose is schedule an ETL job, I recommend you to use the approach "a".

- Query Editor Documentation Link: https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-schedule-query.html
- Redshift Data API limits: https://docs.aws.amazon.com/redshift/latest/mgmt/data-api.html

Thanks!

Answer

**Thank You very much, this is great!**

Redshift Serverless Query Editor V2 - Query Scheduler to have dependencies

相关内容