Passing current date for Snowflake sub-procedure

~ Kamila

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';
    $$
    ;
Unknown's avatar

Published by Kamila

Leave a comment