Is it possible to include column names in the csv with a copy into statement in Snowflake?

Question

For example:

COPY INTO @my_stage/my_test.csv 
FROM (select * from my_table) 
FILE_FORMAT = (TYPE = CSV) 
OVERWRITE=TRUE SINGLE=TRUE 

will result in a csv but does not include column headers. If it is not possible with a copy into statement, is there perhaps any non-obvious technique that might accomplish this?

Thanks in advance.

Leucoderma

Answer 1

We've seen this request before, and it's on our roadmap. If it's high priority for you, please contact Snowflake support.

If you're looking for a workaround, it's hard to come up with a truly generic one.

• One option is to add a single row with explicit column names, but you'd need to know them in advance and it might not be efficient if not all your fields are strings.
• Another is to convert all records using OBJECT_CONSTRUCT(*) and export as JSON, then you will have column names, but it will be of course only useful if you can ingest JSON.

But I hope Snowflake will add this functionality in the not-so-far future.

Caveman

Marcin, thanks for the response. I'm doing this from a python app. I was able to execute a show columns in <table> query, build a copy into statement with the list of columns for correct ordering, download the csv and set the column names upon loading the csv into a pandas dataframe. A little hoop jumping. I guess the problem is solved for now but I may look into object_construct (none of our fields are strings). Hopefully they can get that feature in. Not having headers in the csv really limits what type of applications can easily work with the data. – Leucoderma

Answer 2

Snowflake has added this feature. You can simply add an option HEADER=TRUE:

COPY INTO @my_stage/my_test.csv 
FROM (select * from my_table) 
FILE_FORMAT = (TYPE = CSV) 
OVERWRITE=TRUE SINGLE=TRUE HEADER=TRUE

Surfperch

Verified. However if SINGLE=FALSE it will put the header in each file. Which is a problem when trying to concatenate the files into one. :( – Leucoderma

Yes. It would be better if Snowflake adds an option for adding header to one single file. If this option is high priority for you, please contact Snowflake support. – Surfperch

Answer 3

To supplement @Jiaxing's answer, the Snowflake HEADER feature also allows you to explicitly define your column names by naming the columns via AS:

COPY INTO @my_stage/my_test.csv 
FROM (
  SELECT
    column1 AS "Column 1",
    column2 AS "Column 2"
  FROM my_table
) FILE_FORMAT = (TYPE = CSV)
HEADER=TRUE

Prerequisite