Introduction to Python UDFs | Snowflake Documentation (original) (raw)

You can write the handler for a user-defined function (UDF) in Python. Topics in this section describe how to design and write a Python handler. You’ll also find examples.

For an introduction to UDFs, including a list of languages in which you can write a UDF handler, refer to User-defined functions overview.

Once you have a handler, you create the UDF with SQL. For information on using SQL to create or call a UDF, refer toCreating a user-defined function or Executing a UDF.

Snowflake currently supports writing UDFs in the following versions of Python:

• 3.9
• 3.10
• 3.11
• 3.12 (Preview Feature — Open and available to all accounts.)

How a Python handler works¶

When a user calls a UDF, the user passes UDF’s name and arguments to Snowflake. Snowflake calls the associated handler code (with arguments, if any) to execute the UDF’s logic. The handler method then returns the output to Snowflake, which passes it back to the client.

For each row passed to a UDF, the UDF returns either a scalar (i.e. single) value or, if defined as a table function, a set of rows.

Python UDFs can contain both new code and calls to existing packages, allowing you both flexibility and code reuse. For example, if you already have data analysis code in Python, then you can probably incorporate that into a Python UDF handler.

Example¶

Code in the following example creates a UDF called addone with a handler method addone_py. The Python argument and return types are converted to and from SQL by Snowflake according to mappings described inSQL-Python Data Type Mappings.

CREATE OR REPLACE FUNCTION addone(i INT) RETURNS INT LANGUAGE PYTHON RUNTIME_VERSION = '3.9' HANDLER = 'addone_py' AS defaddonepy(i):returni+1def addone_py(i): return i+1defaddonep​y(i):returni+1;

Design considerations¶

Keep in mind the following for designing a useful handler.

• General considerations. For considerations common to UDFs and procedures, refer toDesign Guidelines and Constraints for Functions and Procedures.
• SQL-Python type mapping. When exchanging argument and return values with a UDF, Snowflake converts between the handler language and SQL. For more information on choosing data types for your handler code, refer to Choosing your data types.
• Code packaging. You can make your handler code available either in-line with the CREATE FUNCTION statement or on a stage. For more information on the difference, refer to Keeping handler code in-line or on a stage.
• Code optimization. For information about optimizing your handler code, such as when the code handles state shared across rows, refer toOptimizing initialization and controlling global state in scalar UDFs and Optimizing for scale and performance.
• Best practices. For information about best practices, refer to Security Practices for UDFs and Procedures.

Handler coding¶

From basics to detailed examples, the following topics describe how to write a UDF handler in Python.

• Python module definition. You write the logic for a UDF in a Python module. For more about how Snowflake interacts with your code, refer to Designing the module.
• Error handling. For information about how Snowflake surfaces errors generated by handlers, refer toHandling errors.
• Tabular return values. You can return tabular values as well as scalar (single) values from a UDF. For information on how to write a handler that returns tabular values, refer to Writing a UDTF in Python.
• Logging and event tracing. For information on capturing log and trace data as your handler code executes, refer toLogging, tracing, and metrics.
• Dependencies. You can make dependences available to your code at run time by uploading them to a stage. For more informaiton, refer to Making dependencies available to your code.
• Code examples For a range of handler examples in Python, refer to Python UDF handler examples.