Unloading from Redshift to S3 in CSV format doesn't create the .csv file

Question

When I unload from Redshift to S3 in CSV format, then when I go the specified path in s3 there isn't any .csv file. The only files I see are the manifest and the 000 file.

The command is the below

UNLOAD('select distinct * from mytable')
to 's3://my-bucket/data/myfile'
iam_role 'xxxxxxxxxxxxxxxxxxxxxx'
header
CSV DELIMITER AS '|'
manifest 
allowoverwrite
parallel off 

The query runs fine and when I go the S3 path I see the below and a hyphen (-) in the type column:

myfile000
myfilemanifest

Shouldn't there also be a .csv file?

Thanks.

I don't know how to proceed on this.

Answer 1

It's a way how Redshift handles files where it appends 000 to the filename, though if your concern is more to get a .csv extension appened to the file name you can do so by adding the EXTENSION 'extension-name' option to the UNLOAD command

https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html

So in your case it would be

UNLOAD
(
'
select distinct * from mytable
'
)

to 's3://my-bucket/data/myfile'
iam_role 'xxxxxxxxxxxxxxxxxxxxxx'
header
CSV DELIMITER AS '|'
manifest 
allowoverwrite
parallel off 
EXTENSION '.csv'