Snowflake Snowpark API for Python: Master Class with Project

You need to complete the initial setup scripts such as :

• Python Environment Setup with Snowflake Snowpark using Anaconda and Microsoft Visual Studio Code

• Snowflake Trial Account and Creation of Database with Notification Integration

You can download the course material from the external resources URL. I have attached a PDF Slide Deck and the Code Repository Link

Let's get a refresher on the Snowflake Snowpark API for Python and on setting up the local environment.

Snowpark Library uses Python Connector to connect to Snowflake. This is done through the snowpark session object.

To establish the snowflake connection, you require a Session Object. You must provide connection parameters to connect with a Snowflake database.

Builder attribute of session object allows to set configurations and connectivity with snowflake

The important thing to note here is that the session object is not a thread-safe object

Snowpark allows you to register a local or stage file as an import inside a user-defined function

The files will not be immediately gets uploaded to the snowflake stage, until and unless you register your UDF.

Adding two files with the same file name is also not allowed.

Snowpark session also allows you to add third-party packages as dependencies of a UDF. This can be done for all the Python packages available in Snowflake.

There are two ways in which you can add packages, either using the add_packages function or the add_requirement function.

Returns the name of the current account for the Python connector session attached to this session

Returns the name of the current database for the Python connector session attached to this session

Returns the name of the primary role in use for the current session

Returns the name of the current schema for the Python connector session attached to this session

Returns the name of the warehouse in use for the current session

Returns the fully qualified name of the current schema for the session

Returns a list of imports added for user defined functions (UDFs). This list includes any Python or zip files that were added automatically by the library

Returns a dict of packages added for user-defined functions (UDFs). The key of this dict is the package name and the value of this dict is the corresponding requirement specifier

Returns the name of the temporary stage created by the Snowpark library for uploading and storing temporary artifacts for this session

Specifies the active/current database for the session

Specifies the active/current primary role for the session

Specifies the active/current schema for the session

Specifies the active/current secondary roles for the session. The currently-active secondary roles set the context that determines whether the current user has the necessary privileges to perform SQL actions

Specifies the active/current warehouse for the session

While you have Session methods to add imports or third-party packages, Snowflake Snowpark API also provides the methods to remove individual or clear all the packages or imports. Also, You can terminate the entire session using Session.close()

Session.remove_import() - Removes a file in stage or local file from the imports of a user-defined function|

Session.remove_package() - Removes a third-party package from the dependency list of a user-defined function|

Session.clear() - Removes a file in stage or local file from the imports of a user-defined function|

Session.clear_imports() - Clears all files in a stage or local files from the imports of a user-defined function|

Session.clear_packages() - Clears all files in a stage or local files from the packages of a user-defined function|

Session.close() - Terminate the snowflake Session|

Refresher quiz on understanding Snowpark Session Method, establishing the Connectivity with Snowflake using Snowpark Session Builder and setting up the session-level configuration for your snowpark coding journey

There are different ways you can interact with snowflake to fetch the data from the tables or use a SQL syntax. Below are ways to read the data:

session.sql():

• returns a new DataFrame representing the results of a SQL query.

• You can use this method to execute an SQL statement.

• Note that you still need to call DataFrame.collect() to execute this query in Snowflake.

• for binding parameters. it only supports qmark bind variables.

session.table():

• Returns a Table that points to the specified table.

• If your table name contains special characters, use double quotes to mark it like this, session.table('"my table"'). For fully qualified names, you need to use double quotes separately like in this, session.table('"my db"."my schema"."my.table"').

session.table_function():

• Creates a new DataFrame from the given snowflake SQL table function.

session.call() :

• Calls a stored procedure by name.

• sproc_name – The name of the stored procedure in Snowflake.

• args – Arguments should be basic Python types.

• statement_params – Dictionary of statement level parameters to be set while executing this action.

• log_on_exception – Log warnings if they arise when trying to determine if the stored procedure as a table return type.

• Provides methods to load data in various supported formats from a Snowflake stage to a DataFrame.

• The paths provided to the DataFrameReader must refer to Snowflake stages.

• Access an instance of a DataFrameReader by using the Session.read property for using this object

• Specify any format-specific options and copy options by calling the option() or options() method.

• These methods return a DataFrameReader that is configured with these options.

• Specify the schema of the data that you plan to load by constructing a types.StructType object and passing it to the schema() method if the file format is CSV.

• Except CSV, Other file formats such as JSON, XML, Parquet, ORC, and AVRO don’t accept a schema.

• If you want to load only a subset of files from the stage, you can use the pattern option to specify a regular expression that matches the files that you want to load.

• To load Parquet, ORC and AVRO files, no schema is accepted because the schema will be automatically inferred.

• Inferring the schema can be disabled by setting option “infer_schema” to False.

• Loading JSON and XML files doesn’t support schema either.

• You also need to use $1 to access the column data as an OBJECT.

DataFrameWriter

Provides methods for writing data from a DataFrame to supported output destinations.

We can create an instance of a DataFrameWriter by accessing the DataFrame.write property.

we can Specify the save mode by calling mode(), which can take append, overwrite, errorifexists and ignore

then we can Call save_as_table() or copy_into_location() to save the data to the specified destination.

FileOperations

• Provides methods for working on files in a stage.

• To access an object of this class, use Session.file.

1. get - Downloads the specified files from a path in a stage to a local directory.

2. get_stream - Downloads the specified files from a path in a stage and exposes it through a stream.

3. put - Uploads local files to the stage.

4. put_stream - Uploads local files to the stage via a file stream.

• The put_stream uses the file/data already in memory using a BytesIO object and then writes the object to the internal stage. Where the PUT saves the physical file on the disk and then writes the file to the internal stage

Snowflake Snowpark Table

• Represents a lazily-evaluated Table.

• It extends DataFrame so all DataFrame operations can be applied to it.

• You can create a Table object by calling Session.table() with the name of the table in Snowflake.

• It supports various Methods:

• delete()

• drop_table()

• merge()

• update()

Snowflake Snowpark Column

• Represents a column or an expression in a DataFrame.

• Snowflake object identifiers, including column names, may or may not be case sensitive depending on a set of rules.

• To create a Column object that represents a constant value, use snowflake.snowpark.functions.lit()

• Column objects can be built with the operators, such as + - * / ** & | == != > < >= <=

• When you use |, & as logical operators on columns, you must always enclose column expressions with parentheses

• Do not use and, or, and not logical operators on column objects

• The reason is Python doesn’t have a magic method, or dunder method for them. It will raise an error and tell you to use |, & or ~, for which Python has magic methods.

• To access elements of a semi-structured Object and Array, use [] on a Column object

• This class has methods for the most frequently used column transformations and operators.

• Module snowflake.snowpark.functions defines many functions to transform columns.

class storedProcedureRegistration

• Provides methods to register lambdas and functions as stored procedures in the Snowflake database.

• session.sproc returns an object of this class. You can use this object to register stored procedures that you plan to use in the current session or permanently.

• Note that the first parameter of your function should be a snowpark Session.

• Also, you need to add snowflake-snowpark-python package (version >= 0.4.0) to your session before trying to create a stored procedure.

• There are two ways to register a stored procedure with Snowpark:

• Use sproc() or register(). By pointing to a runtime Python function, Snowpark uses cloudpickle to serialize this function to bytecode, and deserialize the bytecode to a Python function on the Snowflake server during stored procedure creation.

• Use register_from_file(). By pointing to a Python file or a zip file containing Python source code and the target function name, Snowpark uploads this file to a stage (which can also be customized), and load the corresponding function from this file to the Python runtime on the Snowflake server during stored procedure creation.

class udfRegistration

• Provides methods to register lambdas and functions as UDFs in the Snowflake database

• session.udf returns an object of this class. You can use this object to register UDFs that you plan to use in the current session or permanently. The methods that register a UDF return a UserDefinedFunction object, which you can also use in Column expressions.

• Represents a lazily-evaluated relational dataset that contains a collection of Row objects with columns defined by a schema.

• A DataFrame is considered lazy because it encapsulates the computation or query required to produce a relational dataset. The computation is not performed until you call a method that performs an action

• Broadly, the operations on DataFrame can be divided into two types:

• Transformations produce a new DataFrame from one or more existing DataFrames. Note that transformations are lazy and don’t cause the DataFrame to be evaluated. If the API does not provide a method to express the SQL that you want to use, you can use functions.sqlExpr() as a workaround.

• Actions cause the DataFrame to be evaluated. When you call a method that performs an action, Snowpark sends the SQL query for the DataFrame to the server for evaluation.