Unfortunately, Redshift’s UNLOAD command appends a part number (like 000) to the output file names by default, and there’s no built-in way to change this behavior. Even when you set PARALLEL OFF, Redshift still appends the part number to the output file name.
However, you can work around this by using an external process to rename the file after it’s been unloaded. If you are using AWS S3 as the destination for the UNLOAD command, you can use the AWS CLI or SDKs to rename the file to your desired format.
Here’s an example using the AWS CLI:
- Unload your data from Redshift to S3:
UNLOAD ('SELECT * FROM freshers_table')
TO 's3://freshers-bkt/freshers-in-path/prefix_'
IAM_ROLE 'arn:aws:iam::freshers-in-account-id:role/freshers-in-role-name'
FORMAT AS CSV
PARALLEL OFF;
- Rename the file using the AWS CLI:
First, install the AWS CLI if you haven’t already: https://aws.amazon.com/cli/
Then, run the following command to copy the file with the desired name and delete the original file:
aws s3 mv s3://freshers-bkt/freshers-in-path/prefix_000 s3://<code class="language-sql">freshers-bkt/freshers-in-path/final_file_name.csv Keep in mind that you’ll need to have the appropriate permissions for the S3 bucket and objects involved in this process.
