Tag: stored procedure

Passing current date for Snowflake sub-procedure

~ Kamila ~ Leave a comment

Passing parameters between JavaScript and SQL happens to be problematic in Snowflake.

If you need to pass current date as a parameter to a sub-procedure, you can use a string parameter, and pass Date.now() converted to string:

Date.now().toString()

In subprocedure use SQL functions to parse the date, eg. TO_TIMESTAMP_TZ().

Example:

CREATE or replace PROCEDURE log_transformation (
    MSG       string, 
    START_TIMESTAMP    string
)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS $$

    snowflake.createStatement( { sqlText: 
        `insert into ingestion_log (
            message,
            start_dt
        )
        values (:1, TO_TIMESTAMP_TZ(:2))`
        , binds:[
            MSG,
            START_TIMESTAMP
         ] } 
   ).execute();
$$;

create or replace procedure transform_nb_responses()
    returns string null
    language javascript
    as
    $$

		var execution_beginning = Date.now().toString();
		var accumulated_log_messages = "";

		function log_success() {
			snowflake.execute({
				sqlText: `call LOG_TRANSFORMATION(:1, :2)`, 
				binds:[accumulated_log_messages, execution_beginning]
			});
		}

		// some transformations

		return 'success';
    $$
    ;

Snowflake task that runs exactly once

~ Kamila ~ Leave a comment

Strange as it may sound, a task that will run exactly one might sometimes save you. In my situation it is a hack for missing features in a terraform for Snowflake provider.

According to the Snowflake documentation, a task can only run regularly on schedule, or not run at all.

To get a task run exactly once, it needs to suspend (or drop) itself. With a little help from stored procedures it is perfectly doable.

create or replace procedure destroy_task_test()
returns string null
language javascript
as
$$
// 1. do what the task needs to do
// …

// 2. drop the task
var sql_command = `DROP TASK SELF_DESTROY_TASK;`;
var sql_statement = snowflake.createStatement({sqlText: sql_command});
sql_statement.execute();
$$

Then create the suicidal task:

create task SELF_DESTROY_TASK schedule = '1 minute'
as
call destroy_task_test();

alter task SELF_DESTROY_TASK resume;

Now run SHOW TASKS and watch your task disappear after a minute;

Instead of DROP TASK you may want to use ALTER TASK <task name> SUSPEND.