How do I schedule queries for an Amazon Redshift cluster?

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

Use the query editor v2 to run, view, save, and schedule SQL queries for your provisioned Amazon Redshift clusters. 

Configure query editor permissions

Before you create a scheduled query, you must configure the correct AWS Identity and Access Management (IAM) roles and permissions. Scheduled queries require Amazon EventBridge to initiate the query on schedule and the Amazon Redshift Data API to run the query on the cluster.

Both the IAM user who creates the schedule and the IAM role that runs the query must have the appropriate permissions.

IAM user access

You can attach policies, such as AmazonRedshiftDataFullAccess and AmazonEventBridgeFullAccess. 

You can also create a custom policy with more specific permissions. For more information, see Setting up permissions to schedule a query.

Execution role for queries

To run scheduled queries, identify or create an IAM role for your Amazon Redshift cluster or Amazon Redshift Serverless workgroup.

Then, attach the policies, such as AmazonRedshiftDataFullAccess and AmazonEventBridgeFullAccess, so that Amazon Redshift can assume the role and perform scheduled actions.

For more information, see Using identity-based policies (IAM policies) for Amazon Redshift.

Assume role configuration

Update the role's trust policy to allow the scheduling principal to assume it. Then, use the Amazon Redshift console or modify-cluster-iam-roles AWS CLI command to link the role to your Amazon Redshift cluster or Amazon Redshift Serverless workgroup.

Database authentication

Store your credentials in an AWS Secrets Manager secret that your IAM role can access.

Add the appropriate tags to allow Amazon Redshift Data API usage. Configure your IAM role to get temporary Amazon Redshift credentials. Your cluster or workgroup IAM role must trust the principal, and your database user must have the necessary permissions.

For more information, see Authenticating a scheduled query.

Schedule queries on the Amazon Redshift console

Complete the following steps:

1. Open the Amazon Redshift console.
2. In the navigation pane, choose Editor, and then choose Query editor V2.
3. Connect to a database in your cluster.
4. Create a query schedule to run a SQL statement.
5. (Optional) If you're using Secrets Manager with Amazon Redshift, then select your secret.
6. (Optional) If you're using temporary IAM user credentials, then choose your cluster and provide the database and usernames.

Note: To call the GetClusterCredentials action, you must have the a minimum set of permissions.

To view the newly created schedules, complete the following steps:

1. Open the Amazon RedShift console.
2. In the navigation pane, choose Editor, and then choose Query editor V2.
3. Choose Scheduled queries.

You can turn on Amazon Simple Notification Service (Amazon SNS) notifications to monitor the query's execution status.

If you turned on Amazon SNS, then make sure that the Publish API action exists for events.amazonaws.com.

Example access policy that includes the Publish action for events.amazonaws.com:

{
  "Sid": "Allow_Publish_Events",
  "Effect": "Allow",
  "Principal": {
    "Service": "events.amazonaws.com"
  },
  "Action": "sns:Publish",
  "Resource": "arn:aws:sns:us-east-1:{ACCOUNT_ID}:{SNS_TOPIC_NAME}"
}

For more information, see Monitoring the scheduled query.

Schedule queries with EventBridge

You can use EventBridge and the Amazon Redshift Data API to schedule SQL statements.

Complete the following steps:

1. Define an EventBridge event rule that's named scheduled-refresh-currentEvents.

2. Use the EventBridge console to schedule the event rule to run regularly or at a specific time. Or, run the following put-rule AWS CLI command:

   aws events put-rule \
   --name scheduled-refresh-currentEvents \
   --schedule-expression "cron(0/5 * * * ? *)"

   Note: Replace 5 with the number of minutes that you want between refreshes.

3. Use a text editor to create and save the following JSON file that's named refreshCurrentEvents.json:

   {
     "Rule": "scheduled-refresh-currentEvents",
     "EventBusName": "default",
     "Targets": [
       {
         "Id": "scheduled-refresh-currentEvents",
         "Arn": "Clsuter ARN",
         "RoleArn": "Role ARN",
         "RedshiftDataParameters": {
           "SecretManagerArn": "Secrets Manager ARN",
           "Database": "Database Name",
           "Sql": "REFRESH MATERIALIZED VIEW currentEvents;",
           "StatementName": "scheduled-refresh-currentEvents",
           "WithEvent": true
         }
       }
     ]
   }

   Note: Replace Cluster ARN, Role ARNSecrets Manager ARN with your ARN values.

4. Run the following put-targets command to create an event target with the saved JSON file:

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

5. Run the following remove-targets command to remove the targets:

   aws events remove-targets --rule scheduled-refresh-currentEvents --ids scheduled-refresh-currentEvents
   

6. Run the following delete-rule command to delate the rule:

   aws events delete-rule --name scheduled-refresh-currentEvents

View SQL status and results

Complete the following steps:

1. Open the Amazon RedShift console.

2. In the navigation pane, choose Editor, and then choose Query editor V2.

3. Choose Scheduled queries.

4. Select your query.

5. In Schedule history, note the ID to use in a later step.

6. To retrieve the query results, run the following assume-role command:

   aws sts assume-role --role-arn "Role ARN" --role-session-name Example Session Name

   Note: Replace Role ARN with the ARN of the role that you use to schedule the query and Example Session Name with your session name. The command's output returns the access key ID, secret access key, and session token.

7. Run the following export commands to configure the environment variables:

   export AWS_ACCESS_KEY_ID=ExampleRoleAccessKeyID
   export AWS_SECRET_ACCESS_KEY=ExampleRoleSecretAccessKey
   export AWS_SESSION_TOKEN=ExampleSessionToken

   Note: Replace the example values with your values.

8. Run the redshift-data command with get-statement-result to retrieve the results:

   aws redshift-data get-statement-result --id ID --region Region

   Note: Replace ID with your statement ID and Region with your AWS Region.

Related information

Why can't I connect to the AmazonRedshiftQueryEditor in Amazon Redshift?

Using the Amazon Redshift Data API to interact with Amazon Redshift clusters

Creating a rule that runs on a schedule in Amazon EventBridge