Offloading empty Snowflake table into stage - Stack Overflow

admin管理员组

文章数量:1397208

I have a requirement to export rows from Snowflake table into a file in an internal Snowflake stage. If there are zero rows, I still want the file to be exported with just columns and no data. My code is Python/Snowpark so I probably can write a workaround but was hoping there is solution for this problem in Snowflake. I could not find anything here:

code I am using:

        df.write.copy_into_location(stage_name + file_name,
                          file_format_type = 'csv',
                          header = True, 
                          overwrite = True ,
                          single = True,
                        format_type_options={"COMPRESSION": "NONE", "NULL_IF": (),"RECORD_DELIMITER":"\r\n", "FIELD_OPTIONALLY_ENCLOSED_BY":'"'},
                    )

I have a requirement to export rows from Snowflake table into a file in an internal Snowflake stage. If there are zero rows, I still want the file to be exported with just columns and no data. My code is Python/Snowpark so I probably can write a workaround but was hoping there is solution for this problem in Snowflake. I could not find anything here: https://docs.snowflake/en/sql-reference/sql/copy-into-location#label-copy-into-location-copyoptions

code I am using:

        df.write.copy_into_location(stage_name + file_name,
                          file_format_type = 'csv',
                          header = True, 
                          overwrite = True ,
                          single = True,
                        format_type_options={"COMPRESSION": "NONE", "NULL_IF": (),"RECORD_DELIMITER":"\r\n", "FIELD_OPTIONALLY_ENCLOSED_BY":'"'},
                    )

• snowflake-cloud-data-platform

Share Improve this question Follow asked Mar 27 at 6:04 Humble HeroHumble Hero 14711 silver badge1616 bronze badges 1

• 1 Copy into location - usage notes - "If the source table contains 0 rows, then the COPY operation does not unload a data file." – Lukasz Szozda Commented Mar 27 at 8:14

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

As Lukasz Szozda already mentioned, Snowflake's COPY command does not unload data if the souce has 0 rows, and df.write.copy_into_location method uses COPY command to unload the data.

To overcome this, you may do a trick and write the column names as data:

    if ( df.count() == 0):

        header_values = []
        for field in df.schema.fields:
            header_values.append( field.name ) 
        df = session.createDataFrame( [header_values] )            

        df.write.copy_into_location(stage_name + file_name,
                      file_format_type = 'csv',
                      header = False, 
                      overwrite = True ,
                      single = True,
                    format_type_options={"COMPRESSION": "NONE", "NULL_IF": (),"RECORD_DELIMITER":"\r\n", "FIELD_OPTIONALLY_ENCLOSED_BY":'"'},
                )
    else:
        df.write.copy_into_location(stage_name + file_name,
                      file_format_type = 'csv',
                      header = True, 
                      overwrite = True ,
                      single = True,
                    format_type_options={"COMPRESSION": "NONE", "NULL_IF": (),"RECORD_DELIMITER":"\r\n", "FIELD_OPTIONALLY_ENCLOSED_BY":'"'},
                )

本文标签： Offloading empty Snowflake table into stageStack Overflow