Writing Snowpark Code in Python Worksheets¶

Review and accept the Anaconda Terms of Service¶

Before you start using the packages provided by Anaconda inside Snowflake, you must acknowledge the External Offerings Terms.

1. Sign in to Snowsight.

2. Select Admin » Terms.

3. In the Anaconda section, select Enable.

4. In the Anaconda Packages dialog, click the link to review the External Offerings Terms page.

5. If you agree to the terms, select Acknowledge & Continue.

If you encounter an error when attempting to accept the External Offerings Terms, it may be due to missing information in your user profile, such as a first name, last name, or email address. If you have administrator privileges, see Add user details to your user profile to update your profile using Snowsight. Otherwise, contact an administrator to update your account.

Add a Python File from a Stage to a Worksheet¶

Snowflake includes the Snowpark packages from the Snowflake Anaconda channel in Python worksheets.

If you want to use Python files or packages other than those included in Anaconda in your Python worksheet, you must upload the files to a named stage in Snowflake and then add them to the list of installed packages for your Python worksheet.

To use a Python package in your worksheet, do the following:

1. Sign in to Snowsight.

2. Open Projects » Worksheets.

3. Select + » Python Worksheet.

4. Select a database and schema.

5. Select Packages » Stage Packages.

6. Enter the path to the package in the stage:

   • If the selected database and schema for the worksheet contain the stage where the package is located, you can reference the stage using an unqualified name. For example, @YourStage/path/to/example_package.py.

   • To reference a stage in a different database and schema, fully qualify the name of the stage. For example, @Database.Schema.Stage/path/to/other_package.py.

7. Select Import to add your package to the list of installed packages.

8. In your code, use import statements to use the package in your Python worksheet. For example, after importing packages from the example_package.py and other_package.py files, write the following code to import a function called function from the example_package, and import the package other_package for use in your code:

   from example_package import function
   import other_package
   

   Copy

For more details, see Making dependencies available to your code.

Return Results of a Different Data Type¶

When you write your Python code, consider which type of data is returned by the return statement in your code and adjust how the worksheet returns results. By default, a Python worksheet has a return type of Table() because the placeholder code returns a DataFrame.

Depending on what your Python code returns, you might want to change the worksheet settings to display the output differently:

• If your handler function returns a DataFrame, use the default return type of Table().

• If your handler function returns a list of Row objects, such as with the collect method, change the return type to Variant.

• If your handler function returns a string, such as return "Hello Python", or a value that you want to cast as a string, change the return type to String.

• If your handler function returns an integer, such as with the count method, use a return type of Variant or String.

For details about the return type of some DataFrame methods, see Performing an Action to Evaluate a DataFrame.

To update the worksheet settings to return results of a different type, do the following:

1. Sign in to Snowsight.

2. Open Projects » Worksheets.

3. Open the Python worksheet for which you want to display the results as a table.

4. Select a warehouse to use to run the worksheet. If you have a default warehouse for your user, it is pre-selected. Make sure your warehouse is running.

5. Select Settings and for the Return type, select the type returned by the handler function.

6. Run your Python worksheet.

7. Review the results in the Results panel.

Passing Additional Arguments to the Handler Function¶

With a Python worksheet, you can test a Python function that takes a single argument (a Snowpark Session object) by designating that function as the handler for the worksheet. Every function defined in a Python worksheet needs to pass in the session: snowpark.Session argument.

To test a function that passes in additional arguments, do the following:

1. Add the arguments to your function.

2. Define a separate, single-argument function that passes in a Snowpark Session. In this function, call the multi-argument function, passing in values for the additional arguments, then return the value of the function.

   For example, to write Snowpark Python code that filters a table of packages by the package language column, you can write the following code:

   import snowflake.snowpark as snowpark
   from snowflake.snowpark.functions import col
   
   # Add parameters with optional type hints to the main handler function
   def main(session: snowpark.Session, language: str):
     # Your code goes here, inside the "main" handler.
     table_name = 'information_schema.packages'
     dataFrame = session.table(table_name).filter(col("language") == language)
   
     # Print a sample of the dataFrame to standard output
     dataFrame.show()
   
     # The return value appears in the Results tab
     return dataFrame
   
   # Add a second function to supply a value for the language parameter to validate that your main handler function runs.
   def test_language(session: snowpark.Session):
     return main(session, 'java')
   

   Copy

   In this example, the main function is the multi-argument function and the test_language function is the single-argument function used to validate that your code runs with the passed argument values.

3. Set the single-argument function as the handler function to run the worksheet and validate that your code runs with the argument values.

   In this example, change the handler to the test_language function and then select Run. You can change the handler in the worksheet Settings, or select the Show actions lightbulb next to the handler function and select Set function “test_language” as handler.

When you deploy your Python worksheet as a stored procedure, you can choose the main handler function and review the arguments and the mapped types for your stored procedure.

Review Output Generated by Your Code¶

You can review standard output (stdout) or standard error (stderr) messages for your Python code in the Output panel for a Python worksheet.

You can see the output from the following types of functions in the Output panel:

• Functions that write to the console, such as print().

• Functions that print a DataFrame, such as the show method of the DataFrame class in Snowpark Python.

Review the Query History for a Python Worksheet¶

When a Python worksheet runs in Snowsight, an anonymous stored procedure runs the code and generates queries that execute the Snowpark commands in the code.

You can use the Query History page in Snowsight to review the queries that ran. See Review Query History in Snowsight.

For example, after running a worksheet, you can review the queries that ran by doing the following:

1. Review the Results of the worksheet.

2. In the Query Details for the worksheet, select » Copy Query ID

3. Select Projects » Worksheets to return to the list of worksheets.

4. Select Monitoring » Query History.

5. On the Query History page, display only the queries from your Python worksheet:

   1. Select Filters, and enable the Query ID option.

   2. Enter the Query ID of your Python worksheet.

   3. Select Apply Filters.

6. Review the queries run for the worksheet.

Example: Write a Simple Snowpark Program¶

In this example, write a Snowpark Python program that generates a small range of numbers and writes the range to a table that your code creates, or overwrites if it already exists, in Snowflake. To run this code example, you must have the CREATE TABLE privilege on the database schema to which you want to add the table.

1. Sign in to Snowsight.

2. Open Projects » Worksheets.

3. Select + » Python Worksheet.

4. Select a database and schema that you want to add the table to.

5. Select a warehouse to use to run the worksheet. If you have a default warehouse for your user, it is pre-selected. Make sure your warehouse is running.

6. Write the Snowpark Python code as part of the main function:

   import snowflake.snowpark as snowpark
   
   def main(session: snowpark.Session):
     tableName = "range_table"
     df_range = session.range(1, 10, 2).to_df('a')
     df_range.write.mode("overwrite").save_as_table(tableName)
     return tableName + " table successfully created"
   

   Copy

7. Select Settings and for the Return type, select String for the type returned by the handler function.

8. Run the code.

Example: Transform Data in a Python Worksheet¶

In this example, write Python code that aggregates the entries in the TASK_HISTORY view in the ACCOUNT_USAGE schema of the SNOWFLAKE database by scheduled time and state and saves the aggregated output to a table, aggregate_task_history.

1. Sign in to Snowsight.

2. Open Projects » Worksheets.

3. Select + » Python Worksheet.

4. Select a database and schema that you want to add the table to.

5. Select a warehouse to use to run the worksheet. If you have a default warehouse for your user, it is pre-selected. Make sure your warehouse is running.

6. Write the Snowpark Python code as part of the main function:

   import snowflake.snowpark as snowpark
   from snowflake.snowpark.functions import col
   from snowflake.snowpark.dataframe_reader import *
   from snowflake.snowpark.functions import *
   
   def main(session: snowpark.Session):
   
     inputTableName = "snowflake.account_usage.task_history"
     outputTableName = "aggregate_task_history"
   
     df = session.table(inputTableName)
     df.filter(col("STATE") != "SKIPPED")\
       .group_by(("SCHEDULED_TIME"), "STATE").count()\
       .write.mode("overwrite").save_as_table(outputTableName)
     return outputTableName + " table successfully written"
   

   Copy

7. Select Settings and for the Return type, select String for the type returned by the handler function.

8. Run the code.

After you run your code in a Python worksheet, you can open a SQL worksheet and query the table. See Querying data using worksheets.

Example: Read Files from a Stage with Python Worksheets¶

Snowpark Python lets you read files from a stage and write the contents to a table or save them as a view in Snowflake. In this example, the Python code reads the contents of a compressed CSV-formatted file containing employee data, data_0_0_0.csv.gz from the db1.public.files named stage and writes the contents to a table called employees.

1. Sign in to Snowsight.

2. Open Projects » Worksheets.

3. Select + » Python Worksheet.

4. Select a database and schema that you want to add the table to.

5. Select a warehouse to use to run the worksheet. If you have a default warehouse for your user, it is pre-selected. Make sure your warehouse is running.

6. Write the Snowpark Python code as part of the main function:

   import snowflake.snowpark as snowpark
   from snowflake.snowpark.types import *
   
   schema_for_file = StructType([
     StructField("name", StringType()),
     StructField("role", StringType())
   ])
   
   fileLocation = "@DB1.PUBLIC.FILES/data_0_0_0.csv.gz"
   outputTableName = "employees"
   
   def main(session: snowpark.Session):
     df_reader = session.read.schema(schema_for_file)
     df = df_reader.csv(fileLocation)
     df.write.mode("overwrite").save_as_table(outputTableName)
   
     return outputTableName + " table successfully written from stage"
   

   Copy

7. Select Settings and for the Return type, select String for the type returned by the handler function.

8. Run the code.

After you run your code in a Python worksheet, you can open a SQL worksheet and query the table. See Querying data using worksheets.

For more details about working with files in a stage using Snowpark, see Working with DataFrames in Snowpark Python.