Executing sql from a javascipt UDF

Question

I have a snowflake table being used to store records of sql being executed by a stored procedure and any error messages. The records in this table are being saved as a string with special chars escaped with javascripts escape('log') function. I then want to create a view to this log table that will return the records in an easily readable format.

My first attempt at this was to create an additional stored procedure to query the log table, pass the record to the unescape() function, then return it. Calling this procedure works as intended but we can't then create a view of this data say with something like

create view log_view as
select (call UNESCAPE_PROC());

The other idea was to use a UDF rather than a stored procedure. However this also fails as we can't execute sql code with a javascript UDF. This post touches on this idea.

My question is how can I record these executed statements in a table in such a way as they can be later viewed in a plain text readable format. I've outlined my attempts below but perhaps my approach is flawed, open to any suggestions.

Minimal working example below

Sample log table and procedure to write a statement to said table

create or replace table event_table(
event varchar,
event_stamp timestamp
);

create or replace procedure insert_to_event(stamp string)
RETURNS  VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = 'SP to log an event message with timestamp to event_table'
EXECUTE AS CALLER
AS
$$  
    // some variables to log in our event table
    var str_stamp = (new Date()).toISOString();
    to_log = `insert into dummy_table values(2, 'Bill', `+STAMP+`);`;
    
    sql = 
    `INSERT INTO event_table (
        event,
        event_stamp
    )
    VALUES
        ('`+escape(to_log)+`', to_timestamp('`+str_stamp+`'));`;
    
    var stmnt = snowflake.createStatement({ sqlText: sql });
    stmnt.execute();
    
    return "logged: "+ escape(to_log)
$$;

call insert_to_event(current_timestamp());
select * from event_table;

Stored procedure to return readable log records

CREATE OR REPLACE PROCEDURE UNESCAPE_PROC()
RETURNS  VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = 'SP will select a chosen column from event_table table, pass it to javascripts unescape() fn and return it'
EXECUTE AS CALLER
AS
$$  
    unescape_sql = 
    `select event from event_table` 
    
    var errs_res = [];
    try {
        all_logs = snowflake.execute(
            { sqlText: unescape_sql }
        );
        // iterate over all columns
        while (all_logs.next()) {
            errs_res.push(all_logs.getColumnValue(1));
        }
        return unescape(errs_res)
    }
    catch(err){
        return "something went wrong: " + err
    }
$$;
 call UNESCAPE_PROC();

Which returns the records in a readable form as expected However this of course wont work as part of a view eg.

 -- define a view calling this procedure??
 create view log_view as
    select (call UNESCAPE_PROC());

Javascript user defined function can be used in a view like this, however it cannot be used to execute sql as in the stored procedures

-- use a UDF instead
CREATE OR REPLACE FUNCTION UNESCAPE_UDF()
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  unescape_sql = 
    `select event from event_table` 
    
    var errs_res = [];
    try {
        all_logs = snowflake.execute(
            { sqlText: unescape_sql }
        );
        // iterate over all columns
        while (all_logs.next()) {
            errs_res.push(all_logs.getColumnValue(1));
        }
        return unescape(errs_res)
    }
    catch(err){
        return "something went wrong: " + err
    }
$$
;

select UNESCAPE_UDF();

Stored procedures will solve one half of my problem for me, whilst UDF's will solve the other half. How can I combine the functionality of these two methods to solve this issue?

Lukasz Szozda · Answer 1 · 2022-02-03T20:15:14.473

A much cleaner approach using parameters binding:

create or replace procedure insert_to_event(stamp string)
RETURNS  VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = 'SP to log an event message with timestamp to event_table'
EXECUTE AS CALLER
AS
$$  
    // some variables to log in our event table
    var str_stamp = (new Date()).toISOString();
    to_log = `insert into dummy_table values(2, 'Bill', '${str_stamp}');`;
    
    sql = `INSERT INTO event_table (event,event_stamp)
    VALUES(?, try_to_timestamp(?));`;
    
    var stmnt = snowflake.createStatement({sqlText: sql, binds:[to_log, str_stamp]});
    stmnt.execute();
    
    return "logged: "+ to_log
$$;

Call:

call insert_to_event(current_timestamp());
-- logged: insert into dummy_table values(2, 'Bill', '2022-02-03T17:45:44.140Z');

select * from event_table;

score 1 · Answer 2 · answered Feb 03 '22 at 15:53

Found a solution/workaround.

Rather than using javascripts escape/unescape functions to remove special chars from the logs, we use a regex replace eg.

create or replace procedure insert_to_event(stamp string)
RETURNS  VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = 'SP to log an event message with timestamp to event_table'
EXECUTE AS CALLER
AS
$$  
    // some variables to log in our event table
    var str_stamp = (new Date()).toISOString();
    to_log = `insert into dummy_table values(2, 'Bill', `+STAMP+`);`;
    to_log = to_log.replace(/[`~!@#$%^&*|+=?'"<>\{\}\[\]\\\/]/gi, '');
    
    sql = 
    `INSERT INTO event_table (
        event,
        event_stamp
    )
    VALUES
        ('`+to_log+`', to_timestamp('`+str_stamp+`'));`;
    
    var stmnt = snowflake.createStatement({ sqlText: sql });
    stmnt.execute();
    
    return "logged: "+ to_log
$$;

call insert_to_event(current_timestamp());
select * from event_table;

Which writes to the log table in an easily readable format with no need for additional stored procedures/UDF's.

Executing sql from a javascipt UDF

2 Answers2