How to terminate redshift auto-copy jobs in pending state

Question

Hey guys, 
I have been exploring the redshift auto-copy feature using redshift serverless for a work related project. The idea is to load the incoming .csv files to Redshift immediately. 
What I want to achieve is:
- Auto-copy loads all the incoming .csv files at a specified prefix e.g. `bucket-123/example-123/prod/`. There are several folders inside prod representing each tenant.
- Do NOT load any csv files in any nested prefixes e.g. don't load anything inside `bucket-123/example-123/prod/tenant-123/raw/` or any other nested folders inside prod

As per my research there's no such thing available in auto-copy and it doesn't support manifest files too. 
I thought there might be a way around where I restrict the redshift IAM Role used to access S3 bucket such that I Deny the access to nested folders 
e.g. 
```
        {
            "Sid": "DenyAllRawFolders",
            "Effect": "Deny",
            "Action": "s3:GetObject",
            "Resource": "arn:aws:s3:::bucket-123/example-123/prod/*/raw/*"
        }
```
However, This doesn't seem to work as the SYS_COPY_JOB_DETAIL table show a job in pending state and if new incoming files doesn't trigger any jobs. Next I was thinking to programatically clear any pending jobs to move forward however, I am not sure if that's the right way to go.

```
select * from SYS_COPY_JOB_DETAIL where status = 'pending';
```

Appreciate any help on this 
Thanks

re:Post Agent · Answer

To terminate Redshift auto-copy jobs that are in a pending state, you can use SQL commands to manage these jobs directly within your Redshift serverless namespace.

For auto-copy jobs that are stuck in a pending state, you can use the DROP COPY JOB command to remove them completely:

```sql
DROP COPY JOB job_name;
```

You can also use ALTER COPY JOB to modify the job's behavior:

```sql
ALTER COPY JOB job_name SUSPEND;
```

Regarding your specific use case of loading files only from a specific prefix level without including nested folders, auto-copy doesn't directly support this type of selective loading or manifest files. Your approach of using IAM policies to restrict access to nested folders is creative, but as you've discovered, it can lead to pending jobs that block new incoming files from triggering jobs.

Instead of using IAM policies to restrict access, you might consider these alternatives:

1. Structure your S3 bucket differently to separate files that should be loaded from those that shouldn't.

2. Use a more specific prefix in your COPY command that exactly matches only the folders you want to monitor.

3. Consider implementing a Lambda function triggered by S3 events that can selectively initiate COPY commands only for files at the desired level.

For managing your auto-copy jobs, you can monitor them using system tables:
```sql
SELECT * FROM sys_copy_job;
SELECT * FROM sys_copy_job_detail;
```

These tables will help you identify which jobs are in a pending state and need to be terminated or modified.

Note that for Redshift serverless, you'll need to manage these jobs primarily through SQL commands, as the web UI may not fully display all auto-copy jobs for serverless environments.  
**Sources**  
[Redshift serverless: Autocopy jobs not displayed in web UI | AWS re:Post](https://repost.aws/questions/QUvXNdoL_wStuUWz9oNqwWGQ/redshift-serverless-autocopy-jobs-not-displayed-in-web-ui)  
[Redshift serverless auto-copy | AWS re:Post](https://repost.aws/questions/QUf2p-0iffTeOlDGgLmPA3gw/redshift-serverless-auto-copy)

How to terminate redshift auto-copy jobs in pending state

Add your answer

Relevant content