Snowflake Stored Procedure Logging

Опубликовано: 06 Октябрь 2024
на канале: Knowledge Amplifier

4,440

In this video , a very straight forward mechanism for troubleshooting your stored procedure logic is explained with in-depth intuition.

Prerequisite:
How to create a stored procedure | Snowflake Stored Procedures | Snowflake
   • How to create a stored procedure | Sn...
Pass argument to the Snowflake procedure
   • Pass argument to the Snowflake procedure
Tactically return multiple values from Stored Procedure in Snowflake
   • Tactically return multiple values fro...

Code:
------------
DROP DATABASE IF EXISTS RAMU;

CREATE DATABASE RAMU;

USE RAMU;

CREATE or replace TABLE log_storer(Execution_time String,Message Text);

CREATE OR REPLACE PROCEDURE captureLog(MSG STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$

var my_sql_command_date = "select to_timestamp(current_timestamp()) as curr_time";
var statement1 = snowflake.createStatement( {sqlText: my_sql_command_date} );
var result_set1 = statement1.execute();
result_set1.next();
var time_info= result_set1.getColumnValue(1);

var my_sql_command = "INSERT INTO log_storer values ('"+time_info+"','"+MSG+"')";
var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
var result_set1 = statement1.execute();

$$;

CREATE OR REPLACE PROCEDURE my_test()
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
function log(msg)
{
var my_sql_command = "call captureLog('"+msg+"')";
var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
var result_set1 = statement1.execute();
}

try
{
var x=10/10;
log('log this message');
log('x = '+x.toString())
log('this is another log message')
var m=sum(2,3);
return 'All log captured!!!'
}
catch(ERROR)
{
log(ERROR);
return ERROR
}
$$;

call my_test();

select * from log_storer;

Check this playlist for more AWS Projects in Big Data domain:
   • Demystifying Data Engineering with Cl...