Skip to content
By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post
About re:Post

S3 event integration and Auto copy Job stops working

0

Inside S3 event integration, have created one AUTO COPY JOB which detects new file upload in S3 bucket and loads data into Amazon Redshift.

source: S3 bucket Destination: Redshift cluster

Its observed that after 1 hour this job doesn't do anything (no error). Once Drop and Re-Created that job it again works for 1 hour and goes into same situation where it doesn't do anything (no error). To troubleshoot, tried changing changed maximum session duration to 2 hours for role used in creating COPY JOB. so after 2 hours if this happens then its understandable but it just doesn't do anything after approximate 1 hour of creating.

Also tried to load next sequence file from bucket manually using COPY command and it works fine and loads data in redshift, Not sure why AUTO COPY JOB not detecting it.

Any help in this matter would be appreciated.

Topics
AnalyticsServerlessStorageDatabase
Tags
Job SchedulingAmazon RedshiftStorageDatabase
Language
English

asked 2 years ago415 views

1 Answer
0

This issue with the S3 event integration and Auto COPY JOB stopping after approximately one hour without any apparent errors is concerning and requires further investigation. Here are some steps you can take to troubleshoot and potentially resolve the issue:

  1. Check the system views: Query the SYS_COPY_JOB_DETAIL view to see if the Amazon S3 files have been loaded, are pending ingestion, or if there are any errors. This view can provide valuable information about the status of your COPY JOB.

  2. Examine error logs: Consult the STL_ERROR and SYS_COPY_JOB_INFO system tables for any credential errors or indications that the integration has become inactive. These logs might reveal issues that are not immediately apparent.

  3. Verify the S3 event integration status: Ensure that the S3 event integration remains active. Sometimes, the integration can become inactive due to issues with permissions or configurations.

  4. Check IAM role permissions: Confirm that the IAM role used for the COPY JOB has the necessary permissions to access the S3 bucket and perform the copy operation. Ensure these permissions haven't expired or been revoked.

  5. Monitor S3 bucket events: Verify that new file uploads to the S3 bucket are triggering the expected events. There might be an issue with the event notification configuration.

  6. Review COPY JOB configuration: Double-check the COPY JOB configuration, especially the AUTO ON setting, to ensure it's set to automatically detect and load new files.

  7. Check for S3 bucket name restrictions: Ensure that your S3 bucket name doesn't contain a period (.), as this can prevent the creation of S3 event integrations.

  8. Examine Redshift cluster status: Verify that the Redshift cluster isn't paused, as COPY JOBS don't run when the cluster is in a paused state.

  9. Consider COPY JOB limits: Be aware that there are limits on the number of COPY JOBS per cluster or workgroup (200 maximum) and per S3 bucket (35 maximum). Ensure you haven't exceeded these limits.

If the issue persists after checking these points, you may need to contact AWS support for further assistance. They can help investigate any potential service-related issues or provide more detailed troubleshooting steps based on your specific setup and configuration.
Sources
Create an S3 event integration to automatically copy files from Amazon S3 buckets - Amazon Redshift
Troubleshooting S3 event integration and COPY JOB errors - Amazon Redshift
COPY JOB - Amazon Redshift

answered 2 years ago

  • Alpesh
    2 years ago

    found no errors in below tables for job, checked it already. SYS_COPY_JOB_INFO SYS_COPY_JOB_DETAIL STL_LOAD_ERRORS SYS_LOAD_ERROR_DETAIL

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Relevant content