How to add a timestamp to the filename when unloading from Snowflake to AZ blob?

Question

I have created a file format in Snowflake of CSV type for data unloading; and want the filename to be appended with current timestamp .

create or replace file format "DEV_DB"."SCHEMA".my_out_file              
   -- type = csv
      compression = none
      FILE_EXTENSION = 'txt' 

copy into devstg/A/OutputFiles/my_outbound
     from (select * from "DEV_DB"."SCHEMA"."TABLE")
     file_format = my_out_file
     OVERWRITE=TRUE
     SINGLE=TRUE; 

This is creating an output file with filename : my_outbound. The desired filename is my_outbound_05132022171500

How to append the timestamp to a filename in Snowflake/Azure?

Thanks

Answer 1

Give this a try. Your SQL Stored Proc was really close but had a few minor bugs when assigning the variable to the date_str. Note you can't re-run it on the same day without use over the OVERWRITE param (or adding a second timestamp to the date). Also note that the stage name & table name need to be modified.

Hope this helps.

If using SnowSight use this block:

DECLARE 
  query STRING; 
  date_str STRING; 
  table_nm STRING :='TEMP2';
  BEGIN 
  
  date_str :=  TO_CHAR(current_date(),'mmddyyyy');
  QUERY := 'copy into @pn_stage/my_file_' || date_str || '.csv 
  FROM (SELECT * FROM ' || :table_nm ||') 
     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; 

Same code for the Classic UI:

EXECUTE IMMEDIATE
$$
DECLARE 
  query STRING; 
  date_str STRING; 
  table_nm STRING :='TEMP2';
  BEGIN 
  
  date_str :=  TO_CHAR(current_date(),'mmddyyyy');
  QUERY := 'copy into @pn_stage/my_file_' || date_str || '.csv 
  FROM (SELECT * FROM ' || :table_nm ||') 
     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; 
$$;

Answer 2

This should also work, if you are not averse to declaring variable every time. Change variable declaration as per parameters that needs to be included.

set var='copy into @test_stage/fileprefix_'||to_char(current_timestamp(),'yyyymmddhh24miss')||' from yourtable';


execute immediate $var;