CODEWITHSUNDEEP
csvexport-to-csvsnowflake-cloud-data-platform
David Morgan
David Morgan

Reputation: 145

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

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.

Upvotes: 11

Views: 9237

Answers (3)

Marco Roy
Marco Roy

Reputation: 5273

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

Upvotes: 2

Jiaxing Liang
Jiaxing Liang

Reputation: 302

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

Upvotes: 12

Marcin Zukowski
Marcin Zukowski

Reputation: 4729

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.

Upvotes: 4

Related Questions