Writing stored procedures with SQL and Python

You can write a stored procedure whose handler is coded in Python. By using APIs from the Snowpark library within your handler, you can perform queries, updates, and other work on Snowflake tables.

With stored procedures, you can build and run your data pipeline within Snowflake, using a Snowflake warehouse as the compute framework. Build your data pipeline by using the Snowpark API for Python to write stored procedures. To schedule the execution of these stored procedures, you use tasks.

For information about machine learning models and Snowpark Python, see Training Machine Learning Models with Snowpark Python.

You can write stored procedures for Python using a Python worksheet, or using a local development environment.

You can capture log and trace data as your handler code executes. For more information, refer to Logging, tracing, and metrics.

Note

To both create and call an anonymous procedure, use CALL (with anonymous procedure). Creating and calling an anonymous procedure does not require a role with CREATE PROCEDURE schema privileges.

Prerequisites for writing stored procedures locally

To write Python stored procedures in your local development environment, meet the following prerequisites:

  • You must use version 0.4.0 or a more recent version of the Snowpark library.

  • Enable Anaconda Packages so that Snowpark Python can load the required third-party dependencies. Refer to Using third-party packages from Anaconda.

  • The supported versions of Python are:

    • 3.9

    • 3.10

    • 3.11

    • 3.12

Be sure to set up your development environment to use the Snowpark library. Refer to Setting Up Your Development Environment for Snowpark.

Note

While not required, Snowflake recommends Artifact Repository overview to import Python packages. For more information, see below.

Using artifact repository

You can specify packages to install from the Python Package Index (PyPI) and use them with Snowpark Python stored procedures. For more information, see Artifact Repository overview.

Using third-party packages from Anaconda

You can specify Anaconda packages to install when you create Snowpark Python stored procedures. To view the list of third-party packages from Anaconda, see the Anaconda Snowflake channel. These third-party packages are built and provided by Anaconda. You may use the Snowflake conda channel for local testing and development at no cost under the Supplemental Embedded Software Terms to Anaconda’s Terms of Service.

For limitations, see Python stored procedure limitations.

Getting started

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

Note

You must use the ORGADMIN role to accept the terms. You only need to accept the External Offerings Terms once for your Snowflake account. If you do not have access to the ORGADMIN role, see Enabling the ORGADMIN role in an account.

  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.

Note

If you don’t acknowledge the Snowflake External Offerings Terms as described above, you can still use stored procedures, but with these limitations:

  • You can’t use any third-party packages from Anaconda.

  • You can still specify Snowpark Python as a package in a stored procedure, but you can’t specify a specific version.

  • You can’t use the to_pandas method when interacting with a DataFrame object.

Displaying and using packages

You can display all available packages and their version information by querying the PACKAGES view in the Information Schema:

SELECT * FROM information_schema.packages WHERE LANGUAGE = 'python';
Copy

For more information, see Using third-party packages in the Snowflake Python UDF documentation.

Calling your stored procedure

After creating a stored procedure, you can call it in the following ways:

Language: English