Snowflake - Stored Procedures using JavaScript - Working Session

You can get all snowflake Videos, PPTs, Queries, Interview questions and Practice files in my Udemy course for very less price.. I will be updating this content and will be uploading all new videos in this course.
My Snowflake Udemy Course:
www.udemy.com/...
I can be reachable on [e-mail].
------------------------------------------------------------
CREATE OR REPLACE TABLE MYOWN_DB.PUBLIC.DAILY_TABLE_COUNTS
(
LOAD_TIME TIMESTAMP,
DATABASE_NAME VARCHAR(30),
SCHEMA_NAME VARCHAR(30),
TABLE_NAME VARCHAR(40),
TABLE_COUNT INT
);
CREATE OR REPLACE TABLE MYOWN_DB.PUBLIC.PROC_ERROR_LOG
(
PROC_NAME VARCHAR(50),
RUN_TIME TIMESTAMP,
ERROD_CD VARCHAR(20),
ERROR_MSG VARCHAR(1000),
ERROR_LINE VARCHAR(100)
);
// Create procedure
CREATE OR REPLACE PROCEDURE MYPROCS.PROC_LOAD_TABLE_COUNTS(DB VARCHAR, SCH VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
//EXECUTE AS CALLER
AS
$$
var err = '';
var procName = Object.keys(this)[0];
var procResult = 'SUCCESSFULLY COMPLETED';
try
{
var dbase = DB;
var table_list = `SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM `+dbase+`.INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = ? and TABLE_SCHEMA = ?`;
var table_list_stmt = snowflake.createStatement({sqlText: table_list, binds : [DB, SCH]});
var exe_table_list_stmt = table_list_stmt.execute();
while (exe_table_list_stmt.next())
{
var dbname = exe_table_list_stmt.getColumnValue('TABLE_CATALOG');
var schname = exe_table_list_stmt.getColumnValue('TABLE_SCHEMA');
var tblname = exe_table_list_stmt.getColumnValue('TABLE_NAME');
var row_count_qry = `SELECT COUNT(1) as CNT FROM `+dbname+`.`+schname+`.`+tblname+`;`;
var row_count_stmt = snowflake.createStatement({sqlText: row_count_qry});
var exe_row_count_stmt = row_count_stmt.execute();
exe_row_count_stmt.next();
var row_count = exe_row_count_stmt.getColumnValue('CNT');
var insrt_qry = `INSERT INTO MYOWN_DB.PUBLIC.DAILY_TABLE_COUNTS(LOAD_TIME, DATABASE_NAME, SCHEMA_NAME, TABLE_NAME, TABLE_COUNT) VALUES(CURRENT_TIMESTAMP,'`+dbname+`','`+schname+`','`+tblname+`',`+row_count+`);`;
var insrt_qry_stmt = snowflake.createStatement({sqlText: insrt_qry});
var exe_insrt_qry_stmt = insrt_qry_stmt.execute();
}
}
catch(err)
{
var error_query = `INSERT INTO MYOWN_DB.PUBLIC.PROC_ERROR_LOG(PROC_NAME, RUN_TIME, ERROD_CD, ERROR_MSG, ERROR_LINE) VALUES('`+procName+`', CURRENT_TIMESTAMP,'`+err.code+`','`+err.message.replace(/\'/g, "")+`','`+err.stackTraceTxt+`');`;
var error_query_stmt = snowflake.createStatement({sqlText: error_query});
var exe_error_query_stmt = error_query_stmt.execute();
procResult = `FAILED, CHECK PROC_ERROR_LOG TABLE FOR ERROR DETAILS`;
}
return procResult;
$$
;
// Calling Procedure
CALL MYOWN_DB.MYPROCS.PROC_LOAD_TABLE_COUNTS('SNOWFLAKE_SAMPLE_DATA', 'TPCH_SF1');
CALL MYOWN_DB.MYPROCS.PROC_LOAD_TABLE_COUNTS('MYOWN_DB', 'PUBLIC');
// Validating Data
select * from MYOWN_DB.PUBLIC.DAILY_TABLE_COUNTS;
select * from MYOWN_DB.PUBLIC.PROC_ERROR_LOG;
// Scheduling the procedure to run daily at 7.30 AM UTC
CREATE OR REPLACE TASK MYTASKS.TASK_TABLE_COUNTS
WAREHOUSE = COMPUTE_WH
SCHEDULE = 'USING CRON 30 7 * * * UTC'
AS
CALL MYOWN_DB.MYPROCS.PROC_LOAD_TABLE_COUNTS('SNOWFLAKE_SAMPLE_DATA', 'TPCH_SF1');

[@raghunaidukalvapalli]

Perfect!🎉❤...
I was searching for the same kind of video for so long but luckily got this video.
Really appreciated!

[@referralhelper]

Just started working a new job which is primarily writing stored procedures and I’ve watched a lot of videos on RUclips but this is by far the best one. Great job.

[@jimitmehta5985]

Excellent video.... I was not able to find a better video than this.... Can you make similar video in python in snowflake??

[@klayanbabu]

anna please explain about star and snowflake schema

[@prakashshiva2296]

It was wonderful explanations.
Are you taking any online live courses on snowflake. Please let me know.

[@mrjana520]

No, I am not taking any classes

[@shailu1243]

can we also write a procedure for mailing internally from the component it self by importing relevant library in the proceedure?

[@mrjana520]

as per my knowledge that is not available yet

[@sravaniram-t7m]

sir plz do playlist on snowpark

[@mrjana520]

I am not experienced on Snowpark

[@sravaniram-t7m]

@@mrjana520 😥😥

[@imunnoticeable5757]

Thank u for explanation,but when exactly I followed ur code ,how ever even if after some modification sp succeed .out is not getting inserted.Post some of debug ,seems ,binding has issue .can u recheck the code ..

[@mrjana520]

I checked and the code is perfectly working

[@muktiarahamed890]

could u please make a video on snowflake real time project implementation

[@reduxCVA]

Hi, Can we use python to create stored procs ?

[@mrjana520]

Yes but you have to use snowpark

[@reduxCVA]

@@mrjana520 oh ok thank you

[@spoorthihoney8512]

Please make a video to ingest data from aws to snowflake using matillion

[@maneendrareddy1791]

please upload videos on snowflake end to end project explanation.

[@mrjana520]

I can be reachable on [e-mail]

[@NamrataSharma-cx4sw]

how to optimize stored procedure?

[@mrjana520]

That is not 1 line answer, if you can optimize the sql queries inside the procedure, automatically the procedure will be optimized. I have explained about query optimization in my SQL playlist.

[@sql-world5536]

Hi sir
Do one video Data modeling

[@mrjana520]

I am not expert in that Bro

[@ashokkumarreddy8396]

bro is it mandatory to learn stores procedures using javascript for working as a snowflake developer

[@mrjana520]

yes mandatory to learn writing stored procs,
but the language is of your choice(Java script, SQL, python, Scala)

[@lavanyaadiseshu7489]

Hi bro ur giving snowflake training

[@mrjana520]

No