I'd like to UNLOAD data from Redshift table into already existing S3 folder, in a similar way of what happens in Spark with the write option "append" (so creating new files in the target folder if this already exists).
I'm aware of the ALLOWOVERWRITE option but this deletes the already existing folder.
Is it something supported in Redshift? If not, what approach is recommended? (it would be anyway a desired feature I believe...)
One solution that could solve the issue is to attach another unique suffix after the folder e.g.
unload ('select * from my_table')
to 's3://mybucket/first_folder/unique_prefix_' iam_role
'arn:aws:iam::0123456789012:role/MyRedshiftRole';
If you add unique_prefix_ after the first folder level, all your new files, will start with your unique_prefix_ during the unload operation, therefore you don't need any ALLOWOVERWRITE.
The only issue of this approach is that if you unloaded data change, you might have mix schema for your unloaded data.
