Scheduling a query - Amazon Redshift
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Scheduling a query

To create a schedule to run a SQL statement, you can use the query editor on the Amazon Redshift console. You can create a schedule to run your SQL statement at the time intervals that match your business needs. When it's time for the scheduled query to run, Amazon EventBridge initiates the query.

To create a schedule to run a SQL statement
  1. Open the console and query editor as described in Using the query editor. You can only use this query editor with provisioned clusters.

  2. Choose Schedule to create a schedule to run an SQL statement.

    When you define the schedule, you provide the following information:

You can also manage and update scheduled queries using the Amazon Redshift console. Depending on your version of the console, scheduled queries might be listed in the following places:

  • On the Schedules tab of the details page of your cluster.

  • On the Scheduled queries tab of the query editor.

If you choose Schedule name from one of these locations, you can view and edit your scheduled query's definition.

Setting up permissions to schedule a query on the Amazon Redshift console

To schedule queries, the Amazon Identity and Access Management (IAM) user defining the schedule and the IAM role associated with the schedule must be configured as follows.

For the IAM user logged into the Amazon Redshift console, do the following:

  • Attach the AmazonEventBridgeFullAccess Amazon managed policy to an IAM role and assign the role to the user.

  • Attach a policy with the sts:AssumeRole permission of the IAM role that you specify when you define the scheduled SQL statement.

    The following example shows a policy that assumes a specified IAM role.

    { "Version": "2012-10-17", "Statement": [ { "Sid": "AssumeIAMRole", "Effect": "Allow", "Action": "sts:AssumeRole", "Resource": "arn:aws:iam::account-id:role/sql-statement-iam-role" } ] }

For the IAM role that you specify to enable the scheduler to run a query, do the following:

  • Ensure that this IAM role specifies the EventBridge service principal (events.amazonaws.com). The following is an example trust relationship.

    { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": [ "events.amazonaws.com" ] }, "Action": "sts:AssumeRole" } ] }

    For more information about how to create an IAM role for EventBridge events, see Permissions required to use the Amazon EventBridge scheduler.

  • Attach the AmazonRedshiftDataFullAccess Amazon managed policy to the IAM role.

  • To allow users to view schedule history, edit the IAM role to add the sts:AssumeRole permission.

The following is an example of a trust policy in an IAM role.

{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": [ "events.amazonaws.com" ] }, "Action": "sts:AssumeRole" } ] }

Authenticating a scheduled query

When you schedule a query, you use one of the following authentication methods when the query SQL runs. Each method requires a different combination of input from the Amazon Redshift console.

Amazon Secrets Manager

With this method, provide a secret value for secret-arn that is stored in Amazon Secrets Manager. This secret contains credentials to connect to your database. The secret must be tagged with the key RedshiftDataFullAccess.

For more information about the minimum permissions, see Creating and Managing Secrets with Amazon Secrets Manager in the Amazon Secrets Manager User Guide.

Temporary credentials

With this method, provide your database and db-user values.

The AmazonRedshiftDataFullAccess policy allows the database user named redshift_data_api_user permission for redshift:GetClusterCredentials. If you want to use a different database user to run the SQL statement, then add a policy to the IAM role to allow redshift:GetClusterCredentials. The following example policy allows database users awsuser and myuser.

{ "Version": "2012-10-17", "Statement": [ { "Sid": "UseTemporaryCredentialsForAllDbUsers", "Effect": "Allow", "Action": "redshift:GetClusterCredentials", "Resource": [ "arn:aws:redshift:*:*:dbuser:*/awsuser", "arn:aws:redshift:*:*:dbuser:*/myuser" ] } ] }

Create an Amazon EventBridge rule that runs when a query finishes

You can create an event rule to send a notification when a query finishes. For the procedure using the Amazon EventBridge console, see Creating Amazon EventBridge rules that react to events in the Amazon EventBridge User Guide. For more information about event patterns, see Amazon EventBridge event patterns in the Amazon EventBridge User Guide.

For example, the following sample event is sent when a query is FINISHED.

{ "version": "0", "id": "6a7e8feb-b491-4cf7-a9f1-bf3703467718", "detail-type": "Redshift Data Statement Status Change", "source": "aws.redshift-data", "account": "123456789012", "time": "2020-12-22T17:00:00Z", "region": "us-west-1", "resources": [ "arn:aws:redshift:us-east-2:123456789:cluster:t1" ], "detail": { "statementId": "01bdaca2-8967-4e34-ae3f-41d9728d5644", "clusterId": "test-dataapi", "statementName": "awsome query", "state": "FINISHED", "pages": 5, "expireAt": "2020-12-22T18:43:48Z", "principal": "arn:aws:sts::123456789012:assumed-role/any", "queryId": 123456 } }

You can create an event pattern rule to filter the event.

{ "source": [ "aws.redshift-data" ], "detail-type": [ "Redshift Data Statement Status Change" ], "detail": { "state": [ "FINISHED" ] } }