How to schedule a daily sql script in snowflake db

Question

How to schedule a sql script in the snowflake database to run every day, and set the output file name to include the current date. E.g. if the code ran today then the file name should be 20200906*****.csv.gz, similary for tomorrow 20200907******.csv.gz.

Answer 1

You could use Snowflake TASKS in order to schedule execution of SQL statements. Task can execute a single SQL statement, including a call to a stored procedure. Tasks run according to a specified execution configuration, using any combination of a set interval and/or a flexible schedule using a subset of familiar cron utility syntax.

For your goal I would create a Stored Procedure (so that you could use variables for managing changing filename and for any more complex things).

SF Doc: https://docs.snowflake.com/en/sql-reference/sql/create-task.html

--create a new task that executes a single SQL statement based on CRON definition
CREATE TASK mytask_hour
  WAREHOUSE = mywh
  SCHEDULE = 'USING CRON 0 9-17 * * SUN America/Los_Angeles'
  TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
AS
INSERT INTO mytable(ts) VALUES(CURRENT_TIMESTAMP);


--create a new task that executes a Stored Procedure every hour
create task my_copy_task
  warehouse = mywh
  schedule = '60 minute'
as
  call my_unload_sp();

After creating a task, you must execute ALTER TASK … RESUME in order to enable it.

Use SHOW TASKS to check your task's definition/configuration and then query TASK_HISTORY in order to check executions.

Your Snowflake JS Stored Procedure could be something like this:

create or replace procedure SP_TASK_EXPORT()
    RETURNS VARCHAR(256) NOT NULL
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
as $$    

function getToday_yyyymmdd()
{
    var v_out_Today;
    rs = snowflake.execute ( { sqlText: `SELECT to_char(current_date,'yyyymmdd');` }  );
    if( rs.next())
    {
       v_out_Today = rs.getColumnValue(1); // get current date as yyyymmdd
    }

    return v_out_Today; 
}   

  var result = new String('Successfully Executed');
  var v_Today = getToday_yyyymmdd();

  try {
       
    var sql_command = `copy into @unload_gcs/LH_TBL_FIRST` + v_Today + `.csv.gz from ........`;
 
    var stmt = snowflake.createStatement({sqlText: sql_command});
    var res = stmt.execute();
     
    
   }
    catch (err) {
    result =  "Failed: Code: " + err.code + " | State: " + err.state;
    result += "\n  Message: " + err.message;
    result += "\nStack Trace:\n" + err.stackTraceTxt; 

  } 

 return result;

  $$;

Before creating your task and schedule it, test your Stored Procedure invoking it:

call SP_TASK_EXPORT();