Setting permissions to schedule a query Authenticating a scheduled query Setting up permissions to view schedule query history Monitoring the scheduled query Troubleshooting scheduling a query Finding details about scheduled queries with AWS CloudShell Demo video

Scheduling a query with query editor v2

You can create a schedule to run a SQL statement with Amazon Redshift query editor v2. You 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, the query is started by Amazon EventBridge and uses the Amazon Redshift Data API.

To create a schedule to run a SQL statement

On the Editor view, choose Schedule to create a schedule to run a SQL statement.
When you define the schedule, you provide the following information.
- The IAM role that assumes the required permissions to run the query. This IAM role is also attached to your cluster or workgroup.
- The authentication values for either AWS Secrets Manager or temporary credentials to authorize access your cluster or workgroup. These authentication methods are supported by the Data API. For more information, see Authenticating a scheduled query.
- The cluster or workgroup where your database resides.
- The name of the database that contains the data to be queried.
- The name of the scheduled query and its description. The query editor v2 prefixes the scheduled query name you provide with "QS2-". The query editor v1 prefixes its scheduled query names with "QS-".
- The SQL statement to be run on the schedule.
- The schedule frequency and repeat options or a cron formatted value that defines the schedule. For more information, see Cron Expressions in the Amazon CloudWatch Events User Guide.
- Optionally, you can enable standard Amazon SNS notifications to monitor the scheduled query. You might need to confirm the email address you provide to the Amazon SNS notification. Check your email for a link to confirm the email address for the Amazon SNS notification. For more information, see Email notifications in the Amazon Simple Notification Service Developer Guide. If your query is being run but you don't see messages published in your SNS topic, see My rule runs, but I don't see any messages published into my Amazon SNS topic in the Amazon EventBridge User Guide.
Choose Schedule query to save and activate the schedule and add the schedule to the list of queries in the Scheduled queries view.

The Scheduled queries view lists all the scheduled queries for your clusters and workgroups. With this view, you can display schedule query details, activate or deactivate the schedule, edit the schedule, and delete the scheduled query. When you view query details, you can also view the history of running the query with the schedule.

Note

A schedule query run is only available in the Schedule history list for 24 hours. Queries that run on a schedule don't appear in the Query history view of query editor v2.

Setting up permissions to schedule a query

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 with the IAM permissions to use Amazon EventBridge and Amazon Redshift Data API. To receive emails from scheduled queries, the Amazon SNS notification you optionally specify must be configured also.

The following describes the tasks to use AWS managed policies to provide permission, but depending on your environment, you might want to scope down the permissions allowed.

For the IAM user logged into query editor v2, edit the IAM user using the IAM console (https://console.aws.amazon.com/iam/).

In addition to permissions to run Amazon Redshift and query editor v2 operations, attach the AmazonEventBridgeFullAccess and AmazonRedshiftDataFullAccess AWS managed policies to an IAM user.
Alternatively, assign the permissions to a role and assign the role to the user.

Attach a policy that allows the sts:AssumeRole permission to the resource ARN of the IAM role you specify when you define the scheduled query. For more information about assuming roles, see Granting a user permissions to switch roles in the IAM User Guide.

The following example shows a permission policy that assumes the IAM role myRedshiftRole in account 123456789012. The IAM role myRedshiftRoleis also the IAM role that is attached to the cluster or workgroup where the scheduled query runs.
```
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AssumeIAMRole",
            "Effect": "Allow",
            "Action": "sts:AssumeRole",
            "Resource": [
                "arn:aws:iam::123456789012:role/myRedshiftRole"
            ]
        }
    ]
}
```
Update the trust policy of the IAM role used to schedule the query to allow the IAM user to assume it.
```
{
            "Sid": "AssumeRole",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::123456789012:user/myIAMusername"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}
```

For the IAM role that you specify to allow the scheduled query to run, edit the IAM role using the IAM console (https://console.aws.amazon.com/iam/).

Attach the AmazonRedshiftDataFullAccess and AmazonEventBridgeFullAccess AWS managed policies to the IAM role. The AmazonRedshiftDataFullAccess managed policy only allows redshift-serverless:GetCredentials permission for Redshift Serverless workgroups that are tagged with the key RedshiftDataFullAccess.

Authenticating a scheduled query

When you schedule a query, you use one of the following authentication methods when the SQL runs. Each method requires a different combination of input on the query editor v2. These authentication methods are supported by the Data API which is used to run your SQL statements.

The database user or role that is used to run the query must have the necessary database privileges. For example, to grant IAMR:MyRedshiftQEv2Scheduler privileges to table mytable, run the following SQL command.


GRANT all ON TABLE mytable TO "IAMR:MyRedshiftQEv2Scheduler";

To view the list of database users in your cluster or workgroup, query the system view PG_USER_INFO.

Note

Any Redshift Serverless workgroup for which you schedule queries much be tagged with the key RedshiftDataFullAccess. For more information, see Authorizing access to the Amazon Redshift Data API.

As an alternative to tagging the workgroup, you can add an inline policy to the IAM role (that is specified with the schedule) that allows redshift-serverless:GetCredentials. For example:


{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "UseTemporaryCredentialsForAllServerlessWorkgroups",
            "Effect": "Allow",
            "Action": "redshift-serverless:GetCredentials",
            "Resource": [
                "arn:aws:redshift-serverless:*:*:workgroup/*"
            ]
        }
    ]
}

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. You might have created a secret with the proper credentials when you created your cluster or workgroup. The secret must be tagged with the key RedshiftDataFullAccess. If the tag key is not already present, use the AWS Secrets Manager console to add it. For information about creating a secret, see Creating a secret for database connection credentials.

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 name and Database user values when connecting to a database in a cluster. You only need to provide your Database name when connecting to a database in a workgroup.

When connecting to a cluster, 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 attached to your cluster 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"
            ]
        }
    ]
}

Setting up permissions to view schedule query history

To allow users to view schedule query history, edit the IAM role (that is specified with the schedule) Trust relationships to add permissions.

The following is an example of a trust policy in an IAM role that allows the IAM user myIAMusername to view schedule query history. Instead of allowing an IAM user sts:AssumeRole permission you can choose to allow an IAM role this permission.



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

Monitoring the scheduled query

For the Amazon SNS topic that you specify to send email notifications, create the Amazon SNS topic using the query editor v2 by navigating to the SNS notifications section, Turn on monitoring, and create the topic with Create SNS topic. The query editor v2 creates the Amazon SNS topic and adds a service principal to the access policy for Amazon EventBridge. The following is an example Access policy that is created in the Amazon SNS topic. In the example, the AWS Region us-west-2, AWS account 123456789012, and Amazon SNS topic select-version-pdx-testunload are used.


{
  "Version": "2008-10-17",
  "Id": "__default_policy_ID",
  "Statement": [
    {
      "Sid": "Allow_Publish_Events",
      "Effect": "Allow",
      "Principal": {
        "Service": "events.amazonaws.com"
      },
      "Action": "sns:Publish",
      "Resource": "arn:aws:sns:us-west-2:123456789012:select-version-pdx-testunload"
    }
  ]
}

When the scheduled query runs, Amazon SNS sends AWS notification emails. The following example shows an email sent to myemail@example.com for scheduled query QS2-may25a that ran on AWS Region eu-north-1 in AWS account 123456789012 using Amazon SNS notification topic may25a-SNS.


{"version":"0","id":"8e4323ec-5258-7138-181b-91290e30ff9b","detail-type":"Scheduled Event","source":"aws.events","account":"123456789012","time":"2023-05-25T15:22:00Z",
                    "region":"eu-north-1","resources":["arn:aws:events:eu-north-1:123456789012:rule/QS2-may25a"],"detail":{}}

--
If you wish to stop receiving notifications from this topic, please click or visit the link below to unsubscribe:
https://sns.eu-north-1.amazonaws.com/unsubscribe.html?SubscriptionArn=arn:aws:sns:eu-north-1:123456789012:may25a-SNS:0c1a3d05-39c2-4507-bc3d-47250513d7b0&Endpoint=myemail@example.com

Please do not reply directly to this email. If you have any questions or comments regarding this email, please contact us at https://aws.amazon.com/support

Troubleshooting set up of scheduling a query

Consider the following if you have issues scheduling a query.

Queries not running

Check if the IAM role used in the schedule has permission to get the temporary cluster credentials. The permission for provisioned clusters is redshift:GetClusterCredentialsWithIAM. The permission for Redshift Serverless workgroups is redshift-serverless:GetCredentials.

Scheduled history not displaying

The IAM user or IAM role used to log in to the AWS console was not added into the trust policy of the IAM role used to schedule the query.

When using AWS Secrets Manager for the scheduled query to connect, confirm the secret is tagged with the key RedshiftDataFullAccess.

If the scheduled query is using an AWS Secrets Manager connection, the IAM role used to schedule the query must have the equivalent of managed policy SecretsManagerReadWrite attached to the role.

Query history status is Failed

View the SYS_QUERY_HISTORY system view for details about why the query failed. A common issue is that the database user or role that was used to run the query might not have the required privilege to run the SQL. For more information, see Authenticating a scheduled query.

The following SQL queries the SYS_QUERY_HISTORY view to return failed queries.


SELECT user_id, query_id, transaction_id, session_id, database_name, query_type, status, error_message, query_text 
FROM sys_query_history
WHERE status = 'failed';

To find out details for a specific failing scheduled query, see Finding details about scheduled queries with AWS CloudShell.

Finding details about scheduled queries with AWS CloudShell

You can use AWS CloudShell to find out details about a schedule query. You must have the proper permissions to run the AWS CLI commands shown in the following procedure.

To view the results of a scheduled query

On the AWS console, open the AWS CloudShell command prompt. For more information about AWS CloudShell, see What is AWS CloudShell in the AWS CloudShell User Guide.

Assume the IAM role of the scheduled query. To assume the role, find the IAM role associated with the scheduled query in query editor v2 and use it in the AWS CLI command in AWS CloudShell. For example, for the role scheduler enter an AWS STS command to assume the role used by the scheduled query.


aws sts assume-role —role-arn "arn:aws:iam::123456789012:role/scheduler" —role-session-name "scheduler-test"

The credentials returned are similar to the following.


"Credentials": {
"AccessKeyId": "AKIAIOSFODNN7EXAMPLE",
"SecretAccessKey": "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY",
"SessionToken": "je7MtGbClwBF/2Zp9Utk/h3yCo8nvbEXAMPLEKEY...",        
"Expiration": "2023-08-18T18:19:44+00:00"
},
"AssumedRoleUser": {
"AssumedRoleId": "AROA35B2NH6WBTP7ONL4E:scheduler-test",
"Arn": "arn:aws:sts::123456789012:assumed-role/scheduler/scheduler-test"
}
}

Create environmental variables in the AWS CLI using the credentials displayed from assuming the IAM role. You must use these tokens before their expiration time. For example, you enter the following in AWS CloudShell.
```
export AWS_ACCESS_KEY_ID=AKIAIOSFODNN7EXAMPLE
export AWS_SECRET_ACCESS_KEY=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
export AWS_SESSION_TOKEN=je7MtGbClwBF/2Zp9Utk/h3yCo8nvbEXAMPLEKEY...
```

To view the error of a failed query, run the AWS CLI command to describe a statement. The id of the SQL statement is from the ID shown in the Schedule history section of a scheduled query in the query editor v2.


aws redshift-data describe-statement —id 130d2620-05d2-439c-b7cf-815d9767f513

In this example, the scheduled SQL select * from users limit 100 results in a SQL error that the users table does not exist.


{
"CreatedAt": "2023-08-18T17:39:15.563000+00:00",
"Duration": -1,
"Error": "ERROR: relation \"users\" does not exist",
"HasResultSet": false,
"Id": "a1b2c3d4-5678-90ab-cdef-EXAMPLE11111",
"QueryString": "select * from users limit 100\n—RequestID=a1b2c3d4-5678-90ab-cdef-EXAMPLE22222; TraceID=1-633c5642-4039308d03f3a0ba53dbdf6f",
"RedshiftPid": 1073766651,
"RedshiftQueryId": 0,
"ResultRows": -1,
"ResultSize": -1,
"Status": "FAILED",
"UpdatedAt": "2023-08-18T17:39:16.116000+00:00",
"WorkgroupName": "default"
}

Demo of scheduling a query

For a demo of scheduling a query, watch the following video.

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

Working with datashares

Visualizing results