How to debug a Snowflake stored procedure?

Question

I am using the Snowflake Cloud Database, please help me with a tool to debugging the procedures or functions.

Answer 1

The following Snowflake Javascript Stored Procedure is a template I use to get started on a new Stored Procedures. It contains plenty of debugging tricks, such as:

• it has a "where am I?" variable which gives you a understanding of where in the code you are
• it gathers information in an array as the process moves along
• it returns that array to the standard output of the call command
• it has a "good start" of an exception block, who's contents also get pushed out to standard output on a call of the stored procedure, should it fail.

Something I've been meaning to add is to set a query tag in the code as well, that'd be helpful when reviewing query history, to easily identify the SQL commands that were used in the execution of the Stored Procedure.

This "ties into" the final "debugging trick" - you should always review the query history (actual queries your code executed) when developing stored procedures in a development or test environment, particularly when you are building dynamic SQL statements. Reviewing your query history is a must-do and will show you exactly the commands run and the order of operations of them running.

Here's the code with the sample table it uses, I hope it helps...Rich

CREATE OR REPLACE TABLE test_scripts (
   load_seq  number,
   script    varchar(2000)
   );

INSERT INTO test_scripts values 
   (1, 'SELECT current_timestamp();'),
   (2, 'SELECT current_warehouse();'),
   (3, 'SELECT COUNT(*) FROM snowflake.account_usage.tables;'),
   (4, 'SELECT current_date();'),
   (5, 'SELECT current_account();'),
   (6, 'SELECT COUNT(*) FROM snowflake.account_usage.tables;'),
   (7, 'SELECT ''RICH'';');

select * from test_scripts;


CREATE OR REPLACE PROCEDURE sp_test(p1 varchar, p2 varchar)
  RETURNS ARRAY
LANGUAGE javascript
EXECUTE AS caller
AS 
$$
//note:  you can change the RETURN to VARCHAR if needed
//       but the array "looks nice"
try {
   var whereAmI = 1;
   var return_array = [];
   var counter = 0;

   var p1_str = "p1: " + P1
   var p2_str = "p2: " + P2
   var load_seq = P1;
   var continue_flag = P2;

   whereAmI = 2;
   return_array.push(p1_str)
   return_array.push(p2_str)

   whereAmI = 3;
   //which SQL do I want to run?
   if (continue_flag=="YES") {
       return_array.push("query 1")
       var sqlquery = "SELECT * FROM test_scripts WHERE load_seq >= " + load_seq + " order by 1, 2;";
       }
   else {
       return_array.push("query 2")
       var sqlquery = "SELECT * FROM test_scripts WHERE load_seq = " + load_seq + " order by 1, 2;";
       }

   whereAmI = 4;
   //begin the run of grabbing the commands
   var stmt = snowflake.createStatement( {sqlText: sqlquery} );
   var rs = stmt.execute();

   whereAmI = 5;
   // Loop through the results, processing one row at a time... 
   while (rs.next())  {
       counter = counter + 1;
       var tmp_load_seq = rs.getColumnValue(1);
       var tmp_script = rs.getColumnValue(2);

       var tmp_rs = snowflake.execute({sqlText: tmp_script});
       tmp_rs.next();
       var tmp_col1 = tmp_rs.getColumnValue(1);
       return_array.push("tmp_col1: " + tmp_col1)

       }

   whereAmI = 6;
   return_array.push("end process - counter: " + counter)

   return return_array;
   }

catch (err) {
   return_array.push("error found")
   return_array.push(whereAmI)
   return_array.push(err)
   return return_array;
}

$$;

CALL sp_test(3, 'NO');

Answer 2

I do not believe there is any editor / debugger for stored procedures for Snowflake. Few options:

1. You can break your code to smaller parts and try to troubleshoot
2. Use a log table and insert into log table often, so you can look at the log table to find out what went wrong

Answer 3

unfortunately there isn't one environment to rule them all 1. write your SQL in a Worksheet or Editor 2. write your SPROC code in a JS enabled editor 3. merge them together in a Worksheet or Editor 4. Unit test in SPROCS as shown above by @Rich Murmane

I normally just write SPROCS in a Worksheet but it isnt optimal

Answer 4

Logging is your friend here, as there is no debugger. In general finding and using a debugger for db stored procedures is hard to pull off. Not impossible, just unlikely.

This is a decent alternative:

CREATE or replace PROCEDURE do_log(MSG STRING)
 RETURNS STRING
 LANGUAGE JAVASCRIPT
 EXECUTE AS CALLER
AS $$
 
 //see if we should log - checks for do_log = true session variable
 try{
    var foo = snowflake.createStatement( { sqlText: `select $do_log` } ).execute();
 } catch (ERROR){
    return; //swallow the error, variable not set so don't log
 }
 foo.next();
 if (foo.getColumnValue(1)==true){ //if the value is anything other than true, don't log
    try{
        snowflake.createStatement( { sqlText: `create temp table identifier ($log_table) if not exists (ts number, msg string)`} ).execute();
        snowflake.createStatement( { sqlText: `insert into identifier ($log_table) values (:1, :2)`, binds:[Date.now(), MSG] } ).execute();
    } catch (ERROR){
        throw ERROR;
    }
 }
 $$
;

Then in the stored procedure, you want to debug add a log function at the top:

function log(msg){
    snowflake.createStatement( { sqlText: `call do_log(:1)`, binds:[msg] } ).execute();
}

Then above the call to the stored procedure:

set do_log = true; --true to enable logging, false (or undefined) to disable
set log_table = 'my_log_table'; --The name of the temp table where log messages go

Then in the actual stored procedure you need to add some logging lines:

log('this is another log message'); 

Then call the stored procedure as you would normally. Then select from my_log_table.

Important note: this uses a temp table, so you won't be able to read from that logging table in a different Snowflake connection. This means if you're using the Worksheet editor you need to keep all this stuff on the same sheet.

"Borrowed" from: https://community.snowflake.com/s/article/Snowflake-Stored-Procedure-Logging