COPY INTO statement is not generating file type as .csv – Snowflake, S3

November 9, 2021

I am copying data from Snowflake to S3 using COPY INTO statement.

This is the code:

copy into 's3://dev'
  from "TEST_DATABASE"."RAW_DATA"."TEMP_TABLE"
  credentials = (aws_key_id='***' aws_secret_key='***')
  OVERWRITE = TRUE
  file_format = (type=csv compression='none')
  single=true
  max_file_size=4900000000;

Data gets copied but I don’t get a .csv extension.

I get data as the filename, not data.csv.

What am I doing wrong here?
Any advice appreciated.

>Solution :

There is a parameter called FILE_EXTENSION within the file format options. This should help you get the desired file extension added. You need to specify this parameter, because you are unloading your data into multiple files, not one single file.

Docs for FILE_EXTENSION: https://docs.snowflake.com/en/sql-reference/sql/copy-into-location.html#type-csv