How to Cancel Amazon Redshift

To cancel a running query in Amazon Redshift, follow these steps:

1. Identify the Query: First, you need to identify the query you want to cancel. You can do this by querying the system tables to find the process ID (PID) or session ID of the running query. For Amazon Redshift clusters, use the STV_RECENTS table, and for Amazon Redshift Serverless, use the SYS_QUERY_HISTORY table.

2. Retrieve the Process or Session ID: Use a SQL command like the following to retrieve the process or session ID:
   sql
   SELECT pid, starttime, duration, trim(user_name) as user, trim(query) as querytxt
   FROM stv_recents
   WHERE status = ‘Running’;

   For Serverless, use:
   sql
   SELECT user_id, session_id, start_time, query_text
   FROM sys_query_history
   WHERE status=’running’;

3. Cancel the Query: Once you have the process or session ID, you can cancel the query using the CANCEL command. For example, if the process ID is 802, use:
   sql
   CANCEL 802;

   If you want to specify a custom message, you can include it in single quotation marks:
   sql
   CANCEL 802 ‘Long-running query’;

4. Permissions: Ensure you have the necessary permissions to cancel queries. Users can only cancel their own queries unless they have the CANCEL privilege or are superusers, who can cancel any query.

5. Alternative Method via AWS Console: You can also terminate queries directly from the AWS Management Console by navigating to the Queries and loads page and selecting the Terminate query option for the desired query. This method requires specific IAM permissions, such as the redshift:CancelQuerySession action.