Snowflake: How to debug stored procedure efficiently
From Snowflake website below about stored procedure:

So, why not just console.log(), hmm?

Or PRINT (for SQL server folks),

Or DBMS_OUTPUT.PUT_LINE for Oracle folks?

Why it is so hard that I have to define a log table, blah, blablah…https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#logging-an-error-version-2

At least, the given example does not fit for my purpose.

As a developer, I often need to debug, what is the result of the function/procedure, why it fail(error message), at which line it fail.

Currently, Snowflake fall short of these.

What alternative option available?

OK, it is JavaScript. I should make use of it, especially,{} and [] — object and array.

Personally, I don’t think it is a good idea to use JavaScript instead of native language or dataframe support language, like Python. Data lineage tracking would be quite different, compared with SQL which can be parsed to AST.

CREATE OR REPLACE procedure MY_PROC(PARAM float)

RETURNS VARIANT NOT NULL

LANGUAGE JAVASCRIPT

AS

$$

try {

var msg = [];

msg.push({prc:'MY_PROC checkpoint 1:'+ new Date().toISOString(),txt:'test'});

A= snowflake.execute({ sqlText:`select 1/`+ PARAM});

A.next();

return_val = A.getColumnValue(1); msg.push({prc:'MY_PROC checkpoint 2:'+ new Date().toISOString(),txt:'test finished.'});

return {status:1, ret:return_val, msg:msg};

}

catch(err) {

msg.push({prc:'MY_PROC checkpoint 3:'+ new Date().toISOString(),txt:'FAILED:' + err});

return {status:0, ret:null, msg: msg};

}

$$;

Explanation, let me dive into the code snip a bit more.

1) msg array to hold execution message, check point x…

2) return object of the function is: {status: 0, ret: placeholder, msg:[]}, where: status indicates whether procedure run successfully or not.

ret: returns value of the procedure, msg: keeps the messages.

First test:

CALL MY_PROC(3);

Result is as below:

{

"msg": [

{

"prc": "MY_PROC checkpoint 1:2020-11-22T03:41:44.244Z",

"txt": "test"

},

{

"prc": "MY_PROC checkpoint 2:2020-11-22T03:41:44.322Z",

"txt": "test finished."

}

],

"ret": 3.333330000000000e-01,

"status": 1

}

status = 1 means it ran successfully, with the return value is 3.333330000000000e-01 as expected. Yeah!

Second test:

CALL MY_PROC(0)

{

"msg": [

{

"prc": "MY_PROC checkpoint 1:2020-11-22T03:13:33.884Z",

"txt": "test"

},

{

"prc": "MY_PROC checkpoint 3:2020-11-22T03:13:34.105Z",

"txt": 'FAILED:Division by zero'

}

],

"ret": null,

"status": 0

}

status = 0 means it ran with error, with the return value is null as expected. Bingo!

So, now I know somewhere error happened between checkpoint 1 and checkpoint 3.

It is not a ideal solution, but works well for me, I can debug the code more efficiently.

Also, I can define complex JavaScript function with in the stored procedure. However, it can not be use as common function or library, then I’d rather not making it too complicate.

Stay tuned for the next article: Snowflake: Nested Stored Procedure.
['Martin X.']
['Stored Procedure', 'Snowflake', 'Azure Data Factory', 'Debugging', 'JavaScript']