1

I am using copy into command to store the query results from snowflake into a file in s3 bucket. It works as expected but when the query returns no data I still want to save the column names in the same file so that I can display the empty row with column names in UI. I have specified header=true in the command but it doesn't work when the query returns no result.

Thanks

Gowthaman
  • 1,262
  • 1
  • 9
  • 15

1 Answers1

1

According to the docs:

If the source table contains 0 rows, then the COPY operation does not unload an empty data file; not even a file containing the table column headings.

And there doesn't seem to be a way to change this behavior.

However, before writing the table to S3, you could write the headers (as data) by querying the INFORMATION_SCHEMA. Then write your table as usual (with OVERWRITE=TRUE) -- and if no rows are written, the header file will remain.

UPDATE

Here's an how to generate a header:

select listagg(column_name,',') within group (order by ordinal_position)
from MY_DB.information_schema.columns 
where table_schema='MY_SCHEMA' and table_name='MY_TABLE';

You can then write it as data to a CSV file like this:

copy into @my_stage/my_table  (
    select listagg(column_name,',') 
        within group (order by ordinal_position)
    from MY_DB.information_schema.columns 
    where table_schema='MY_SCHEMA' and table_name='MY_TABLE';
)
file_format = (type=csv compression=none field_delimiter=none)
header=false;
waldente
  • 1,324
  • 9
  • 12
  • what is the table name to get header in information schema dbname.information_schema.columns ? – PIG Apr 04 '20 at 08:56
  • 1
    You can construct the header like this: `select listagg(column_name,',') within group (order by ordinal_position) header from MY_DB.information_schema.columns where table_schema='MY_SCHEMA' and table_name='MY_TABLE' ;` – waldente Apr 04 '20 at 13:54