Snowflake Python Stored Procedure as In Line Code in Snowflake worksheet | in-depth intuition

This video explains how to write a stored procedure in Python. You can use the Snowpark library within your stored procedure to perform queries, updates, and other work on tables in Snowflake.

Prerequisite:
-----------------
Getting Started with Snowpark for Python on Snowflake

CSV Data Load from External Stage to Snowflake Table using Snowpark

Read Parquet Data from External Stage & write in Permanent Snowflake Table using Snowflake Snowpark

Read Json Data from External Stage & write in Permanent Snowflake Table using Snowflake Snowpark

Understanding Caller’s Rights and Owner’s Rights in Snowflake Stored Procedures

Documentation:
-------------------------------

SQL Query:
------------------

drop database if exists ramu;
--Create Database
create database if not exists ramu;
--use the database
use ramu;

CREATE OR REPLACE PROCEDURE multiply_together(INPUT_NUMBER_1 int,INPUT_NUMBER_2 int)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'multiplier'
AS
$$
def multiplier(session,a,b):
return a*b
$$;

call multiply_together(2,3);

call multiply_together(3,5);

CREATE OR REPLACE PROCEDURE reverse_string(INPUT_STR STRING)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'reversal'
AS
$$
def reversal(session,a):
ms= a[::-1]
return ms
$$;

call reverse_string('ABC');

call reverse_string('BMHS');

CREATE OR REPLACE PROCEDURE retrieve_current_user_warehouse_schema()
returns string not null
language python
runtime_version = '3.8'
packages = ('snowflake-snowpark-python')
handler = 'retrieve_current_user_warehouse_schema'
as
$$
def retrieve_current_user_warehouse_schema(session):
$$
;

call retrieve_current_user_warehouse_schema();

CREATE OR REPLACE PROCEDURE basic_metadata_command_to_table(
INPUT_METADATA_COMMAND STRING
, INPUT_DESTINATION_TABLE STRING
)
returns string not null
language python
runtime_version = '3.8'
packages = ('snowflake-snowpark-python')
handler = 'basic_metadata_command_to_table_py'
execute as caller
as
$$
def basic_metadata_command_to_table_py(snowpark_session, metadata_command: str, destination_table: str):

## Read the command into a Snowflake dataframe

## Write the results of the dataframe into a target table
try:

return f"Succeeded: Results inserted into table {destination_table}"
except Exception as e:
return e
$$
;

create or replace table source_table( emp_no int,emp_name text,salary int, hra int ,Dept text);

INSERT INTO source_table VALUES (100, 'A' ,2000, 100,'HR'),
(101, 'B' ,5000, 300,'HR'),
(102, 'C' ,6000, 400,'Sales'),
(103, 'D' ,500, 50,'Sales'),
(104, 'E' ,15000, 3000,'Tech'),
(105, 'F' ,150000, 20050,'Tech'),
(105, 'F' ,150000, 20060,'Tech');

select * from source_table;

select * from hello_world;

Check this playlist for more Data Engineering related videos:

Apache Kafka form scratch

Snowflake Complete Course from scratch with End-to-End Project with in-depth explanation--

🙏🙏🙏🙏🙏🙏🙏🙏
YOU JUST NEED TO DO
3 THINGS to support my channel
LIKE
SHARE
&
SUBSCRIBE
TO MY YOUTUBE CHANNEL

[Neelkamal-yfzq]

great content, this resolved my problem

[mickyman]

HI Knwoledge Amplifier, I want to send error notifcation via stored procedure if something fails in that storeprocedure and I used you task video(task to sns notifcation), but I am unable to send notification, can you help how can I do it, it is running but I am not getting a sns notification

[EmitraHelpDeskForYou]

Hey Bro, Thanks for a really Amazing Video🥰

[itssinghmotivation]

hey man thank you for the great videos. I have a question about a project of yours in MATLAB. Its about the "car Drawing". Is it possible to make the wheels rotate? So it seems that the car is moving/driving. And if it is how would the code look like? Keep up the good work and hopefully you have an answer.

[MrAbhijitdebnath]

Hi, Could you please provide me one example of python stored proc where we can read from a sql query and stored in a cursor and process in a for loop.

[SabithByari]

is it possible to connect to google bigquery using snowflake python worksheet (snowpark)?