Orchestration of Redshift Stored Procedures using AWS Managed Airflow

Question

I have many redshift stored procedures created(15-20) some can run asynchronously while many have to run in a synchronous manner.

I tried scheduling them in Async and Sync manner using Aws Eventbridge but found many limitations (failure handling and orchestration).

---

I went ahead to use AWS Managed Airflow.

• How can we do the redshift cluster connection in the airflow?

• So that we can call our stored procedure in airflow dags and stored proc. will run in the redshift cluster?

• Is there any RedshiftOperator present for connection or we can create a direct connection to the Redshift cluster using the connection option in the airflow menu?

• If possible can we achieve all these using AWS console only, without Aws cli?

Answer 1

How can we do the redshift cluster connection in the airflow?

So that we can call our stored procedure in airflow dags and stored proc. will run in the redshift cluster?

You can use Airflow Connections to connect to Redshift. This is the native approach for managing connections to external services such as databases.

Managing Connections (Airflow)
Amazon Redshift Connection (Airflow)

Is there any RedshiftOperator present for connection or we can create a direct connection to the Redshift cluster using the connection option in the airflow menu?

You can use the PostgresOperator to execute SQL commands in the Redshift cluster. When initializing the PostgresOperator, set the postgres_conn_id parameter to the Redshift connection ID (e.g. redshift_default). Example:

PostgresOperator(
    task_id="call_stored_proc",
    postgres_conn_id="redshift_default",
    sql="sql/stored_proc.sql",
)

PostgresOperator (Airflow)
How-to Guide for PostgresOperator (Airflow)

If possible can we achieve all these using AWS console only, without Aws cli?

No, it's not possible to achieve this only using the AWS console.