Redshift COPY command raises error if S3 prefix does not exist

Question

When I run this COPY command:

COPY to_my_table (field1, field2, etc)
FROM s3://my-service-f55b83j5vvkp/2018/09/03
CREDENTIALS 'aws_iam_role=...'
JSON 'auto' TIMEFORMAT 'auto';

I get this error:

The specified S3 prefix '2018/09/03' does not exist

Which makes sense, because my S3 bucket does not have any file in that specific prefix. However, this is part of a daily job to load data, where sometimes there's something to load, but some other times there's nothing to load.

I checked the COPY documentation and it doesn't seem to be any way to avoid the error and just don't do anything if there are no objects under that prefix. Maybe I am missing something?

user_1073487 · Answer

I would like to suggest here, how we have solved this problem in our case, though its simple solution but may be helpfull to others. Jon Scot has suggested good option in comment that I liked. But, unfortuanetely in our case, we coundn't do it as system adding files to S3 was not in our controll. So not sure it its your case too. I think you could solve your problem multiple ways, but here are two options that I suggest.

1) As you may be running cron job to load data to Redshift, put a file existence check before executing the Copy command, like below.

path=s3://my-service-f55b83j5vvkp/2018/09/03
count=\`s3cmd ls $path | wc -l\`

if [[ $count -eq 1 ]]; then
    //Your Redshift copy code goes here.
else
    echo "Nothing to load"
fi

Advantage of this options is your saving some cost though may be completely negligible.

2) dummy file without records, that will eventually load no data to Redshift.

Redshift COPY command raises error if S3 prefix does not exist

1 Answers