By using AWS re:Post, you agree to the Terms of Use

Redshift COPY commend takes the wrong file from S3

0

Hi I am trying to load a file from S3 with command like below:

copy bundle FROM 's3://....../..../bundle' iam_role 'arn:aws:.......' delimiter '^' dateformat 'auto'

It ran into some column formatting issue, but then when I check the stl_load_errors table, the file path captured is different than the one I specify in the command. Why is Redshift doing this?

path in the command: s3://....../..../bundle

path captured in stl_load_errors : s3://....../..../bundle_delivery.csv

  • I ended up having to specify the .csv suffix explicitly such as copy bundle FROM 's3://....../..../bundle.csv' iam_role 'arn:aws:.......' delimiter '^' dateformat 'auto'. Otherwise it just choose whatever file that matches the name.

asked 2 months ago60 views
1 Answers
0

Hi There,

I understand that you have an issue where Redshift COPY command takes the wrong file from S3

And you have an understanding that

copy bundle FROM 's3://....../..../bundle'

ran into some column formatting issue, but then when you check the stl_load_errors table, the file path captured is different than the one you specified in the command.

From my analysis I have found that the S3 bucket folder name will act similar to a wild card character when running the query in redshift spectrum . I tried using a '/' at the end of the bucket subfolder name which you are intending to hit but what I strongly suggest more is to use the absolute path to the file.

Example in your case would be: copy bundle FROM 's3://....../..../bundle.csv'

Please see the suggested documentation [1]

For More information on S3 's3://copy_from_s3_objectpath' see the link [2].

I hope the above information is helpful in resolving your issue .

Knowledge Articles:

[1] https://docs.aws.amazon.com/redshift/latest/dg/loading-data-files-using-manifest.html

[2] https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-source-s3.html - Parameters section

[3]https://docs.aws.amazon.com/redshift/latest/dg/c-spectrum-troubleshooting.html#spectrum-troubleshooting-incompatible-data-format

answered 2 months ago

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.

Guidelines for Answering Questions