Redshift COPY commend takes the wrong file from S3

Question

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`

Mfanelo · Answer

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

Redshift COPY commend takes the wrong file from S3

Relevant content