- Newest
- Most votes
- Most comments
Hello,
Before I commence with explanation, I would like to mention that currently the "000" suffix is part of UNLOAD command architecture and there are no parameters available to avoid the "000" suffix, and here is why..
Amazon Redshift unloads the data in parallel from all nodes in cluster to provide maximum network throughput and fast UNLOAD operation. If PARALLEL is set to OFF or FALSE, UNLOAD writes to one or more data files serially, with data sorted according to the ORDER BY clause, if one is used. The maximum size for a data file is 6.2 GB. So, for example, if you unload 13.4 GB of data, UNLOAD creates the following three files.
s3://mybucket/key000 6.2 GB
s3://mybucket/key001 6.2 GB
s3://mybucket/key002 1.0 GB
Thus, the "000" number format is appended to the provided file path to ensure there is no redundancy in file names unloaded by Redshift for large resultsets. Here, as the resultset size of SELECT query in UNLOAD command cannot be predicted beforehand, there is no UNLOAD command parameter provided to control the appending of "000" suffix.
I hope you found this information helpful. Thank you !!
Hello,
Since there is no native options from Amazon Redshift, I have tried to find some workarounds for you. You can ignore the appended part while reading the file or you can rename the file using S3 mv cli command like below:
aws s3 mv s3://<S3-bucket>/<prefix>/file.000 s3://<S3-bucket>/<prefix>/file
You could also use lambda or shell/powershell script to do a renaming process in your use case to avoid this issue.
I hope it will help and have a nice day!
Thanks. Yes unfortunately we have to use a Glue job to rename the file. Honestly, for go-forward, if we know the file size will never exceed 6.2GB we could bake it into the requirements to expect 000 as part of the filename. It would mean using the EXTENTION parameter so that the extension gets applied after the 000.
Relevant content
- Accepted Answerasked a year ago
- asked 6 months ago
AWS OFFICIALUpdated a year ago- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 5 months ago
I get the purpose of it. I think the frustration I have with this is to supplant already existing exports using UNLOAD. For processes going forward we just have to set expectations that 000 will be part of the filename (as long as we're sure the file size will never exceed 6.2 GB) . We can use the EXTENSION parameter so that the filename still meets standard naming conventions.