Add a timestamp to an export .csv file name in Snowflake Scripting Stored Procedure

user_18227599 0
Viewed 351

I am writing a stored procedure using Snowflake Scripting in which I need to copy out a Snowflake table into a .csv file, and add a timestamp to the file name. This table will have been created earlier in the procedure and will be saved with a variable name. I have two questions:

(1) How can I use the table variable name in the from statement of my copy command?

(2) How can I save the .csv file name with today's date stamp?

Below is how I would run this copy command in Snowflake SQL, where my_table is the name of the table I would like to copy out, and the file name is hard-coded rather than a variable.

          copy into @my_stage/my_file_03072022.csv
            from table($my_table)
            file_format=(type=csv compression=none skip_header=1
                field_optionally_enclosed_by= '"')
                header = true
                single = true
                max_file_size = 4900000000
                ;

Thank you for your help!

snowflake-cloud-data-platform stored-procedures
1 Answers

Are you asking how to format the date into the MMDDYYYY format, thus:

SELECT TO_CHAR(current_date,'mmddyyyy');
TO_CHAR(CURRENT_DATE,'MMDDYYYY')
03082022

or how to get that value and use it in a Snowflake Scripting block, like:

DECLARE
   query STRING;
   date_str STRING;
BEGIN 
    SELECT TO_CHAR(current_date,'mmddyyyy') INTO :date_str;
    QUERY := 'copy into @my_stage/my_file_' || date_str || '.csv table($my_table) 
    file_format=(type=csv compression=none skip_header=1
        field_optionally_enclosed_by= \'"\')
        header = true
        single = true
        max_file_size = 4900000000;';
                 
   --EXECUTE IMMEDIATE :QUERY;
   
   RETURN :QUERY;
END;
anonymous block
copy into @my_stage/my_file_03082022.csv table($my_table) file_format=(type=csv compression=none skip_header=1 field_optionally_enclosed_by= '"') header = true single = true max_file_size = 4900000000;
user_439920
Related