S5. What's under the Snowflake(s)?

Let us add something new in What’s under the Snowflake(s)? In this post, we focus on the challenge of Week 41 – Basic from Frosty Friday

Getting started with Python worksheets

Let’s getting started with Python worksheet in Snowflake.

What’s Python worksheet? What does it allow you to do?

Something about it! https://medium.com/snowflake/introducing-snowflake-worksheets-for-python-276dda90bb67

Let’s go on your Snowsight instance!

You can find Python worksheet in Snowsight, the Snowflake web interface. You find it under the «add» blue bottom. Click on «Python Worksheet».

This is the worksheet you will see. There are some instructions, read them carefully and you will be ready to work with it!

From Packages, let’s look for snowflake-snowpark-python. It is already in the Installed Anaconda Packages! As you can see, you can find here others useful packages as well as, for instance pandas, numpy and so on and so forth.

A possible strategy

At this point, we can start the challenge. In our Python worksheet, we should write some lines of code in order to obtain two colomns where statements are splitted.

We have to define the handler function and the statements to use as input, i.e. the statements to return. Once you have a handler, you can create a procedure with a CREATE PROCEDURE command (actually, we will use another way), then call the procedure with a CALL statement. Curious to know more about? Read this cool blog post on stored procedure.

def main(session: snowpark.Session): 

    statement_1 = "We love Frosty Friday"
    statement_2 = "Python worksheets are cool"

It is now possible to see that each statement has the same number of words. Hence we can use the tuple(zip()) function. Indeed, zip() function takes iterables (can be zero or more), aggregates them in a tuple, and returns it. While the tuple() function is a built-in function in Python that can be used to create a tuple. Lastly, we can use the split() method that spilts a string into a list. We can also specify the separator. To note that the any whitespace is a default separator.

statement_tuples = tuple(zip(
          statement_1.split(" ")
        , statement_2.split(" ")
      ))

We are hence arrived at the dataframe. In the last step, we should add the function we have recently created into the dataframe. Actually, we sketch the statements in the Snowflake dataframe.

dataframe = session.create_dataframe(
statement_tuples
, schema=["STATEMENT_1", "STATEMENT_2"])

The last step is to write the Return function in order to see the value that will appear in the Results tab. An easy step. It is only necessary to write:

return dataframe

Create a stored procedure with a click and a button

We can then create the stored procedure called statement_creator(). In this way we are extending the system with procedural code. From a stored procedure, we can return a single value or (where supported with the handler language) tabular data. As in our following case!

Instead of creating a procedure with a CREATE PROCEDURE command, in our Python worksheet, we can simply create it by the bottom Deploy.

By clicking on it, a window appear on your screen. We are ready to create our new Python procedure. We have to specify the name, adding some comments if you want. At the end, we want to open our stored procedure in a new worksheet. Hence, click on the left bottom. Everything will be executed immediately by Snowflake.

Our stored procedure is created by Snwoflake! There is not to much to do, Snowflake creates it automatically. In the picture below, we can see how Snowflake creates it.