Creating a user-defined function | Snowflake Documentation (original) (raw)

You can create a user-defined function (UDF) using any of several methods available with Snowflake. These methods are described in this topic.

Create a UDF¶

1. Write function logic as a handler using one of several supported languages, including Python, Java, and Scala.
2. Choose a tool or API to create the function with the handler you wrote.
   For more information about each of these, see Tools for creating UDFs.

   SQL	Use SQL and write logic in one of several languages.
   Snowpark	Use the Snowpark API for Java, Python, or Scala.
   Command line	Execute CLI commands to create the function.
   Python API	Execute client-side Python commands to create the function.
   REST	Make requests to a RESTful API to create the function.

3. Execute the function using one of several tools, depending on your needs.

Tools for creating UDFs¶

You can create a UDF using any of several methods available with Snowflake, depending on the language and skill set you have available. Choose the tool that’s right for your needs from the following table.

Key properties¶

The following describes some of the properties required or typically used when creating a function.

Function name:

The function name does not need to match the name of the handler. For more about name constraints and conventions, seeNaming and overloading procedures and UDFs.

Arguments:

For more information on requirements, see Defining arguments for UDFs and stored procedures.

Return type:

For information about how Snowflake maps SQL data types to handler data types, seeData Type Mappings Between SQL and Handler Languages.

Handler name:

When required, this is the name of the class or method containing code that executes when the function is executed. You need specify a handler name only for handlers written in Java, Python, and Scala. For JavaScript and SQL handlers, all code specified in-line will be executed as the handler.

Dependencies:

For a handler written in Java, Python, or Scala, you might also need to specify the Snowpark library, such as when creating the function.

For more about making dependencies available to your handler, see Making dependencies available to your code.

Handler language runtime:

When the handler language is Java, Python, or Scala, specify the runtime version to indicate which supported runtime version to use. Keep in mind that if you use the default version, that default will change over time.

Access control requirements¶

A role used to execute this operation must have the followingprivileges at a minimum:

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions onsecurable objects, see Overview of Access Control.

Usage notes¶

The following notes describe usage notes relevant to the languages supported for writing handlers. Although notes in the following sections refer to clauses of the SQL CREATE FUNCTION command, these clauses are typically represented in other ways in other tools you can use to create functions.

All languages¶

• _functiondefinition_ has size restrictions. The maximum allowable size is subject to change.
• The delimiters around the _functiondefinition_ can be either single quotes or a pair of dollar signs.
  Using $$ as the delimiter makes it easier to write functions that contain single quotes.
  If the delimiter for the body of the function is the single quote character, then any single quotes within _functiondefinition_ (such as string literals) must be escaped by single quotes.
• If using a UDF in a masking policy, ensure the data type of the column, UDF, and masking policy match. For more information, see User-defined functions in a masking policy.
• If you specify the CURRENT_DATABASE or CURRENT_SCHEMA function in the handler code of the UDF, the function returns the database or schema that contains the UDF, not the database or schema in use for the session.
• Regarding metadata:
  Attention
  Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.
• The OR REPLACE and IF NOT EXISTS clauses are mutually exclusive. They can’t both be used in the same statement.
• CREATE OR REPLACE statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.

Java¶

• In Java, primitive data types don’t allow NULL values, so passing a NULL for an argument of such a type results in an error.
• In the HANDLER clause, the method name is case-sensitive.
• In the IMPORTS and TARGET_PATH clauses:
  • Package, class, and file name(s) are case-sensitive.
  • Stage name(s) are case-insensitive.
• You can use the PACKAGES clause to specify package names and version numbers for Snowflake system-defined dependencies, such as those from Snowpark. For other dependencies, specify dependency JAR files with the IMPORTS clause.
• Snowflake validates that:
  • The JAR file specified in the CREATE FUNCTION statement’s HANDLER exists and contains the specified class and method.
  • The input and output types specified in the UDF declaration are compatible with the input and output types of the Java method.
    Validation can be done at creation time or execution time, depending on whether you are connected to an active Snowflake warehouse.
  • Creation time — If you are connected to an active Snowflake warehouse at the time the CREATE FUNCTION statement is executed, the UDF is validated at creation time.
  • Execution time — If you are not connected to an active Snowflake warehouse, the UDF is created, but is not validated immediately, and Snowflake returns the following message:
    Function <name> created successfully, but could not be validated since there is no active warehouse.

JavaScript¶

• Snowflake does not validate JavaScript code at UDF creation time. In other words, creation of the UDF succeeds regardless of whether the code is valid. If the code is not valid, Snowflake returns errors when the UDF is called at query time.

Python¶

• In the HANDLER clause, the handler function name is case-sensitive.
• In the IMPORTS clause:
  • File name(s) are case-sensitive.
  • Stage name(s) are case-insensitive.
• You can use the PACKAGES clause to specify package names and version numbers for dependencies, such as those from Snowpark. For other dependencies, specify dependency files with the IMPORTS clause.
• Snowflake validates that:
  • The function or class specified in the CREATE FUNCTION statement’s HANDLER exists.
  • The input and output types specified in the UDF declaration are compatible with the input and output types of the handler.

Scala¶

• In the HANDLER clause, the method name is case-sensitive.
• In the IMPORTS and TARGET_PATH clauses:
  • Package, class, and file name(s) are case-sensitive.
  • Stage name(s) are case-insensitive.
• You can use the PACKAGES clause to specify package names and version numbers for Snowflake system-defined dependencies, such as those from Snowpark. For other dependencies, specify dependency JAR files with the IMPORTS clause.
• Snowflake validates that:
  • The JAR file specified in the CREATE FUNCTION statement’s HANDLER exists and contains the specified class and method.
  • The input and output types specified in the UDF declaration are compatible with the input and output types of the Scala method.
    Validation can be done at creation time or execution time, depending on whether you are connected to an active Snowflake warehouse.
  • Creation time — If you are connected to an active Snowflake warehouse at the time the CREATE FUNCTION statement is executed, the UDF is validated at creation time.
  • Execution time — If you are not connected to an active Snowflake warehouse, the UDF is created, but is not validated immediately, and Snowflake returns the following message:
    Function <name> created successfully, but could not be validated since there is no active warehouse.

SQL¶

• Currently, the NOT NULL clause is not enforced for SQL UDFs.

Create a UDF with SQL¶

You can create a UDF with SQL using the following steps.

You create a UDF with the following steps:

1. Write handler code that executes when the UDF is called.
   You can use one of the supported handler languages. For more information, see Supported languages and tools.
2. Choose whether you’ll keep the handler code in-line with the CREATE FUNCTION SQL statement or refer to it on a stage.
   Each has its advantages. For more information, see Keeping handler code in-line or on a stage.
3. Execute a CREATE FUNCTION statement in SQL, specifying properties of the function.
   Code in the following example creates a UDF called function_name with the in-line handlerHandlerClass.handlerMethod.
   create function function_name(x integer, y integer)
   returns integer
   language java
   handler='HandlerClass.handlerMethod'
   target_path='@~/HandlerCode.jar'
   as
   classHandlerClasspublicstaticinthandlerMethod(intx,inty)returnx+y;class HandlerClass {
   public static int handlerMethod(int x, int y) {
   return x + y;
   }
   }classHandlerClasspublicstaticinthandlerMethod(intx,inty)returnx+y;;
   The following describes some of the properties required or typically used when creating a function.
   • Function name.
     The UDF name does not need to match the name of the handler. The CREATE FUNCTION statement associates the UDF name with the handler.
     For more about name constraints and conventions, see Naming and overloading procedures and UDFs.
   • Function arguments, if any.
     See Defining arguments for UDFs and stored procedures.
   • Return type with the RETURNS clause.
     For a scalar return value, the RETURNS clause will specify a single return type; for a tabular return value, RETURNS will specify the TABLE keyword specifying column type in the tabular return value.
     For information about how Snowflake maps SQL data types to handler data types, seeNaming and overloading procedures and UDFs.
   • Handler name with the HANDLER clause.
     When required, this is the name of the class or method containing code that executes when the UDF is called. You need specify a handler name only for handlers written in Java and Python. For JavaScript and SQL handlers, all code specified in-line will be executed as the handler.
     The following table describes the form of the HANDLER clause’s value based on the handler language and function type.

     Handler Language	UDF	UDTF
     Java	Class and method name. For example: MyClass.myMethod	Class name only. Handler method name is predetermined by the required interface.
     JavaScript	None.	None.
     Python	Class and method name if a class is used; otherwise, function name. For example: module.my_function or my_function	Class name only. Handler method name is predetermined by the required interface.
     SQL	None.	None.

   • Dependencies required by the handler, if any, using the IMPORTS or PACKAGES clauses.
     For more about making dependencies available to your handler, see Making dependencies available to your code.
   • Handler language runtime with RUNTIME_VERSION clause.
     When the handler language is Java or Python, use the RUNTIME_VERSION clause to specify which supported runtime version to use. Omitting the clause will prompt Snowflake to use the default, which may change in the future.