Scheduling a query on the Amazon Redshift console - Amazon Redshift

Scheduling a query on the Amazon Redshift console

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 on the console

  1. Open the console and query editor as described in Using the query editor.

  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 list that you can reach from the navigation pane. To see the list, on the navigation pane choose QUERIES, Schedule query list.

  • 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 AWS 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 AWS-managed policy.

  • 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 AWS-managed policy to the IAM role.

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

The following is an example of the definition of an IAM role.

{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": [ "events.amazonaws.com" ] }, "Action": "sts:AssumeRole" }, { "Sid": "AssumeRole", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::account-id:user/user-name" }, "Action": "sts:AssumeRole" } ] }

The following example snippet allows specific IAM users to view schedule history.

{ "Sid": "AssumeRole", "Effect": "Allow", "Principal": { "AWS": [ "arn:aws:iam::account-id:user/testRedshiftUser", "arn:aws:iam::account-id:user/myusername" ] }, "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.

AWS Secrets Manager

With this method, provide a secret value for secret-arn that is stored in AWS 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 AWS Secrets Manager in the AWS 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" ] } ] }