COPY INTO statement is not generating file type as .csv

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.

CodePudding user response：

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

CodePudding user response：

For a single file, you just specify the full file name including the extension:

copy into 's3://dev/MyFileName.csv'
  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;

If you don't specify the single=true option, you need to specify the FILE_EXTENSION option:

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')
  FILE_EXTENSION = 'csv'
  max_file_size=4900000000;