Stored Procedure In Snowflake | Chapter-21.1 | Snowflake Hands-on Tutorial

[@vkumaremail]

The following keywords for the CREATE PROCEDURE command are deprecated on May 12, 2022 IMMUTABLE VOLATILE

[@DataEngineering]

Thanks for note, I see it in snowflake docs now saying. [ VOLATILE | IMMUTABLE ] -- Note: VOLATILE and IMMUTABLE are deprecated. Ref: docs.snowflake.com/en/sql-reference/sql/create-procedure.html

[@MohitSharma0205]

@@DataEngineering poo plotw

[@chakradharreddymodulla6695]

Thank you Sir, I completed snowpro certification. Your videos on topics and practice tests made 80% of my preparation

[@DataEngineering]

Wonderful and congratulation on your success.

[@maheshtiriveedi5130]

I want to contact you..may ask your email id..

[@kameswarig5630]

Thank you so much for your great effort sir.. We owe you for such free teaching with so much in detail knowledge.

[@DataEngineering]

It's my pleasure

[@SreePotluri2024]

Your connect and explanation is great! One can get well experienced in snowflake just by following your channel. Thank you!

[@DataEngineering]

Great to hear!

[@sireeshap9624]

Thanks for sharing. Videos are very helpful to me to learn Snowflake.

[@DataEngineering]

Glad to hear that!

[@motivationalvibes5699]

Thanks sir awesome explanation

[@DataEngineering]

Thanks for liking

[@shubhangidhamdhere4612]

Thanks a million Sir.. We are very grateful to you for such a great effort and sharing your knowledge. Respect 🙏!!

[@DataEngineering]

Thanks and welcome

[@pranjalinagarkar3176]

Amazing video sir. every concept is well explained with example. Thank you for sharing your knowledge.

[@DataEngineering]

You are most welcome

[@babubansi9133]

Great Work.

[@DataEngineering]

Thank you 🙏 for watching my video and your word of appreciation really means a lot to me.

[@venkateshpasunuri4017]

Thank you so much for all your videos , every topic explained very well .

[@DataEngineering]

Thank you 🙏 for watching my video and your word of appreciation really means a lot to me. ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡ I have already published other knowledge series and snowflake certification videos and if you are interested, you can refer them. 🌐 Snowflake Complete Guide Playlist ➥ bit.ly/3iNTVGI 🌐 SnowPro Guide ➥ bit.ly/35S7Rcb 🌐 Snowflake SQL Series Playlist ➥ bit.ly/3AH6kCq 🌐 SnowPro Question Dump (300 questions) ➥ bit.ly/2ZLQm9E

[@grishrrr]

Thank you so much! It was excellent.

[@DataEngineering]

You're very welcome!

[@knowledgeacquire8566]

Thank you so much Sir!!

[@DataEngineering]

most welcome

[@gauravsinghal4948]

Thanks a millions for this wonderful training for Snowflake developers. have one doubt regarding ROLE and ACCESS. in this videos-- in chapter-20- you said Role and users are to be created by security-admin role but you are using account-admin role for creating USER (lead01 & lead02) and ROLE(created by Security-admin) assigning to those user. and again you switch to Securtiy-admin to assign role to role. not having clear understanding when and on what we can use those roles.

[@DataEngineering]

- Users should be created by user-admin - Role assigment should be done by Security admin (that's why it is named as security admin) Let me re-look into the content... but your observation is right..

[@Buzzingfact]

Thank you for creating these videos 👏

[@DataEngineering]

Glad you like them!

[@Praveen_Kumar_R_CBE]

Amazing content, very useful as always...

[@DataEngineering]

Thank you 🙏 for watching my video and your word of appreciation really means a lot to me.

[@subramanyams3742]

Thanks a lot for all the free videos. I am already certified however I am still learning a lot of real time content from your videos. 🙂Do you have any plans in making the videos on snowpark?.

[@DataEngineering]

You're very welcome! Will do it ..may take sometime... as need to understand the demand of audience... What specific challenge you have.. or area you wan to focus.. if you can drop note here or in my insta account .. I can design the course and publish it.

[@bethtilley5648]

Your video tutorials are great. Thanks. Is there a way to see the query that was used to create and populate the tables and views for past years?

[@rajeshbhosale2008]

Thanks a lot 👍🏻

[@DataEngineering]

You're welcome! and liked you are dropping a note after finishing each chapter.

[@nijamudhins3405]

Thanks alot!😀

[@DataEngineering]

Happy to help

[@investingduz8583]

Youre amazing, thank you so much

[@DataEngineering]

You're so welcome!

[@vinothkannaramsingh8224]

stored procedure is dead ? For ETL we can use spark or snowpark right ?

[@sebastienrichez6813]

Yours videos are great. Do you have any insight about stored procedure in Javascript vs stored procedure in Python ? for the cost, performance, devOps...

[@DataEngineering]

Not yet.. will consider it in future.. it is a very rare requirement..

[@Storyteller_30]

hi you are doing great work can you make storedprocedure in SQL Language with proper examples

[@DataEngineering]

will do it soon...

[@kandanulupruthvi8844]

Thanks for the videos, your videos are really very helpful. Can you help me with creating stored procedure using java script for inserting data into dimension tables(target)

[@DataEngineering]

Will try

[@sunshadow9704]

Great demo. One serious request. Please speak slower. Not only this will make the context easy to follow, it will boost your confidence in teaching.

[@DataEngineering]

Sorry for that and noted... will try my best to speak slow.

[@Anil_Rishvik_Memories]

Nice info, how can we implement SAP hana view reporting logic in snowflake procedure, that logic has multiple steps each step is depends on previous steps and it has if and case related stuff as ell

[@DataEngineering]

For such complex logic, you can write java or java script stored procedure rather using SQL based stored procedure.

[@Anil_Rishvik_Memories]

@@DataEngineering Thanks for the response and yeah i am using javascript to write procedures in snowflake for that logic but need some info how to implement as i know only the basics of stored procedures in Snowflake, can you guide me link or article which i can refer for the same

[@DataEngineering]

Hi @@Anil_Rishvik_Memories , now sure how helpful I will be as there are limited content available for javascript stored procedure and I am planning to make a playlist in future. If you can share your high level logic to my instagram account (instagram.com/learn_dataengineering/), I can review and see if I can guide you or point to some good content available out.

[@Anil_Rishvik_Memories]

@@DataEngineering yes shared the same in your insta message

[@muralikrishna-gi2kh]

Hi ur videos are very usefull please make a video on snaplogic

[@DataEngineering]

Will try it in future..

[@jiangjy1982]

Excellent tutorials! I find the tree view of course content very helpful. Is it shared anywhere? Thanks!

[@DataEngineering]

Not yet!

[@saereddy]

Calling too many stored procs leads to dead lock. So is there any limit for this ???

[@DataEngineering]

I don't think that there is any rule... have you come across any such scenario?

[@manidhar.V.S]

Request you to keep all queries,data that are used in vedios inside the discription box as text files.

[@DataEngineering]

Noted

[@visshalgupta]

Hi Snowflake is coming up with the new version of the snowpro core examination ? how much difference it will make ?

[@DataEngineering]

The weightage is changing but overlal the coverage is some.

[@srinivasanelluri8068]

Hello sir , thank you for the video, can we call thirdpary api call inside storedprocedure ?

[@DataEngineering]

Yes, you can and for that you have to create an external function first.. and then you can call it as function.. the function is processed per row basis.. so for example.. if you want to trasnlate the desc from language-1 to language-2 such use cases can be managed.. but just calling an API to get a single value like Java or python.. may not work.. or we need to check the documentation...

[@pavankalyankonda1022]

do we have any custom tool to convert SAP ABAP code to Snowsql?

[@pmohantymohanty7]

Why isn't the sql script webpage opening ?

[@hankjones9043]

Thanks for your videos. I have question when I tried to simulate the first procedure purge_inactive_customer, I had to use double-quotes instead of single-quotes around the sqlText. Do you know why? Thanks again! example "DELETE FROM CUSTOMER WHERE CUST_STATUS = 'Inactive';" instead of 'DELETE FROM CUSTOMER WHERE CUST_STATUS = 'Inactive';'

[@DataEngineering]

This worked for me.. $$ var rs = snowflake.execute( { sqlText: `DELETE FROM CUSTOMER WHERE CUST_STATUS = 'Inactive';` } ); return 'Inactive customers purged.'; $$; Where are you trying this.. snowsight or legacy webui? I tried in snowsight and it worked for me...

[@SreePotluri2024]

Also I was wondering if you are planning on a video focusing only on snowpark (python) ?

[@DataEngineering]

There are lot of content available on Snowpark... don't they give enough or good explaination.. if not..then I can make video around it... It needs a complete playlist as there are lot of key concept around snowpark. Feel free to share your though or drop me a note to my instagram or facebook msg. instagram.com/learn_dataengineering/ fb.me/learndataengineering

[@ipsna2758]

Hi, thnak you for your videos. But I need one help -- I am working on automating Poricedures depedncies with Javascript in SP, But I am not sure how to debug it as simple print ,ie, Cosnole.log(message) does not give any Output. I am totaly new to JS, so not sure why Its not working. and I am stuck with my code errors as I am not able to debug.

[@DataEngineering]

Thanks for your note and I am really not sure how to help you without knowing the actual problem statement. It is a challenge debugging javascript in Snowflake unless you have worked in the past. Either you can add your domain problem here, or post a direct message to my instagram account (instagram.com/learn_dataengineering/)

[@shashankm2859]

How can we print message in snowflake sql procedure?

[@DataEngineering]

There is no direct way to see... it has to be stored in a string and then return and using result_scan, the values can be captured.. this is a work around but there is no direct way to do it.

[@NeumsFor9]

Maybe they meant idempotent instead if immutable?

[@DataEngineering]

Could you point which part of the video has that term.. I published it long back.. so not able to recall

[@maheshpokharkar278]

How to enforce Primary Key Constraint in Snowflake?

[@swatisharma4409]

Hey there, Can we learn snowflake without having coding knowledge?

[@DataEngineering]

Thanks @Swati for your note... to some extend yes.. to really leverage the power of snowflake, SQL is must.. to some extend cloud storage integration... For analyst, programming is not must and you can even pull snowflake data into excel (and power query) and perform transformation and analysis Watch my latest video kzbin.info/www/bejne/jmXYiZ59iJWXi7M

[@shreyaskulkarni2142]

Where is part - 2?

[@DataEngineering]

I am working on it

[@JacobGeorgeJames]

​@@DataEngineering Waiting for part-2 sir. Thank you.

[@shruthiik6817]

Hi I am getting error while executing the following stored procedure. Can you please help me to fix the issue? create or replace procedure customers_insert_procedure(LNAME VARCHAR) returns string language javascript as $$ var sql_command = 'INSERT INTO CUSTOMER1(LNAME) VALUES(:1);' snowflake.execute ( { sqlText: sql_Command, binds: [LNAME] }); return "successfully executed."; $$; calling the procedure: create or replace task procedure_task warehouse = compute_wh schedule = '1 minute' as call customers_insert_procedure ('JACK');

[@DataEngineering]

What error are you getting.. if you could send the text or share screenshot in my insta account.. I could help you

[@abhik2791]

Great video! Great content. One question is can we use nested CTE as below with in sql or javascript construct? Is this valid at all? Do we have any limitation on number of CTE we can use (CT1,CT2,CT3------) CREATE OR REPLACE PROCEDURE sp5() RETURNS string LANGUAGE javascript AS $$ var my_sql_command = `with ct1 (f1,f2) as (select f1,f2 from TEST_1.PUBLIC.TEST11) ct2 (f1,f3) as (select f1,f3 from TEST_1.PUBLIC.TEST12) ct3 (f1,f2,f3) as (select a.f1,f2,f3 from TEST_1.PUBLIC.TEST11 a left join TEST_1.PUBLIC.TEST12 b on a.f1 = b.f1) insert into TEST_1.PUBLIC.EMP1 (f1,f2,f3) select f1,f2,f3 from ct2;`; var statement1 = snowflake.createStatement( {sqlText: my_sql_command} ); var result_set1 = statement1.execute(); return "Done"; $$; call sp5();

[@DataEngineering]

Thanks Abhi ... as far as I know, having nested CET should not be an issue... SQL Scripting is new addition in stored proc space. Worth trying this use case and see how it works in snowflake. Thanks again for sharing your use case, it helps community to know about these challenges if they are trying to solve the same use case.

[@guptagurumukh00]

I am trying to create to Create stored proc with "java" i am getting error. can you please check. CREATE OR REPLACE PROCEDURE MYPROC(x int) RETURNS int LANGUAGE JAVA PACKAGES = ('com.snowflake:snowpark:latest') HANDLER = 'MyJavaClass.run' AS $$ import com.snowflake.snowpark_java.*; public class MyJavaClass { public Integer run(int y) { return y; } } $$;

[@DataEngineering]

Can you share the error via instagram to my insta account

[@guptagurumukh00]

@@DataEngineering sorry i dont use insta, btw can we write a procedure to run a sql query where we chose lang = java, why i see people use java script only.