Scheduling Amazon Redshift Data API operations with Amazon EventBridge

Amazon EventBridge helps you to respond to state changes in your Amazon resources. When your resources change state, they automatically send events into an event stream. Events are sent to the account that contains the Amazon Redshift database. You can create rules that match selected events in the stream and route them to targets to take action. You can also use rules to take action on a predetermined schedule. For more information, see the Amazon EventBridge User Guide.

To schedule Data API operations with EventBridge, the associated IAM role must trust the principal for CloudWatch Events (events.amazonaws.com). This role should have the equivalent of the managed policy AmazonEventBridgeFullAccess attached. It should also have AmazonRedshiftDataFullAccess policy permissions that are managed by the Data API. You can create an IAM role with these permissions on the IAM console. When creating a role on the IAM console, choose the Amazon service trusted entity for CloudWatch Events. For more information about creating an IAM role, see Creating a Role for an Amazon Service (Console) in the IAM User Guide.

When you create a schedule in EventBridge, you can run the SQL statement against an Amazon Redshift cluster or an Amazon Redshift Serverless workgroup.

The following example uses the Amazon CLI to create an EventBridge rule that is used to run a SQL statement against an Amazon Redshift cluster.

aws events put-rule 
--name test-redshift-cluster-data 
--schedule-expression "rate(1 minute)"

Then an EventBridge target is created to run on the schedule specified in the rule.

aws events put-targets 
--cli-input-json file://data.json

The input data.json file is as follows.

{
    "Rule": "test-redshift-cluster-data",
    "EventBusName": "default",
    "Targets": [
        {
            "Id": "2",
            "Arn": "arn:aws:redshift:us-east-1:123456789012:cluster:mycluster",
            "RoleArn": "arn:aws:iam::123456789012:role/Administrator",
            "RedshiftDataParameters": {
                "Database": "dev",
                "DbUser": "root",
                "Sql": "select 1;",
                "StatementName": "test-scheduler-statement",
                "WithEvent": true
            }
        }
    ]
}

The following example uses the Amazon CLI to create an EventBridge rule that is used to run a SQL statement against an Amazon Redshift Serverless workgroup.

aws events put-rule 
--name  test-redshift-serverless-workgroup-data 
--schedule-expression "rate(1 minute)"

Then an EventBridge target is created to run on the schedule specified in the rule.

aws events put-targets 
--cli-input-json file://data.json

The input data.json file is as follows.

{
    "Rule": "test-redshift-serverless-workgroup-data", 
    "EventBusName": "default", 
    "Targets": [ 
        {
            "Id": "2",
            "Arn": "arn:aws:redshift-serverless:us-east-1:123456789012:workgroup/a1b2c3d4-5678-90ab-cdef-EXAMPLE11111",
            "RoleArn": "arn:aws:iam::123456789012:role/Administrator", 
            "RedshiftDataParameters": {
                "Database": "dev",
                "Sql": "select 1;",
                "StatementName": "test-scheduler-statement", 
                "WithEvent": true 
            } 
        } 
    ] 
}