User-Defined Functions Overview

You can write user-defined functions (UDFs) to extend the system to perform operations that are not available through the built-in system-defined functions provided by Snowflake. Once you create a UDF, you can reuse it multiple times.

A UDF is just one way to extend Snowflake. For others, see the following:

Note

A UDF is like a stored procedure, but the two differ in important ways. For more information, see Choosing Whether to Write a Stored Procedure or a User-Defined Function.

What is a User-Defined Function (UDF)?

A user-defined function (UDF) is a function you define so you can call it from SQL. As with built-in functions you can call from SQL, a UDF’s logic typically extends or enhances SQL with functionality that SQL doesn’t have or doesn’t do well. A UDF also gives you a way to encapsulate functionality so that you can call it repeatedly from multiple places in code.

You write a UDF’s logic – its handler – in one of the supported languages. Once you have a handler, you can create a UDF with a CREATE FUNCTION command, then call the UDF with a SELECT statement.

Scalar and Tabular Functions

You can write a UDF that returns a single value (a scalar UDF) or that returns a tabular value (a user-defined table function, or UDTF).

  • A scalar function (UDF) returns one output row for each input row. The returned row consists of a single column/value.

  • A tabular function (UDTF) returns a tabular value for each input row. In the handler for a UDTF, you write methods that conform to an interface required by Snowflake. These methods will:

    • Process each row in a partition (required).

    • Initialize the handler once for each partition (optional).

    • Finalize processing for each partition (optional).

    The names of the methods vary by handler language. For a list of supported languages, see Supported Languages.

Get Started

For a tutorial through which you write a UDTF with a handler written in SQL, see Quickstart: Getting Started With User-Defined SQL Functions

UDF Example

Code in the following example creates a UDF called addone with a handler written in Python. The handler function is addone_py. This UDF returns an int.

create or replace function addone(i int)
returns int
language python
runtime_version = '3.8'
handler = 'addone_py'
as
$$
def addone_py(i):
  return i+1
$$;

Code in the following example executes the addone UDF.

select addone(3);

Supported Languages

You write a function’s handler – its logic – in any of several programming languages. Each language allows you to manipulate data within the constraints of the language and its runtime environment. Regardless of the handler language, you create the procedure itself in the same way using SQL, specifying your handler and handler language.

You can write a handler in any of the following languages:

Language

Developer Guides

Java

JavaScript

Python

SQL

Language Choice

You write a UDF’s handler – its logic – in any of several programming languages. Each language allows you to manipulate data within the constraints of the language and its runtime environment.

You might choose a particular language if:

  • You already have code in that language.

    For example, if you already have a Java method that will work as a handler, and the method’s object is in a .jar file, you could copy the .jar to a stage, specify the handler as the class and method, then specify the language as Java.

  • The language has capabilities that others don’t have.

  • The language has libraries that can help you do the processing that you need to do.

When choosing a language, consider also the following:

  • Handler locations supported. Not all languages support referring to the handler on a stage (the handler code must instead by in-line). For more information, see Keeping Handler Code In-line or on a Stage.

  • Whether the handler results in a UDF that’s sharable. A sharable UDF can be used with the Snowflake Secure Data Sharing feature.

Language

Handler Location

Sharable

Java

In-line or staged

No 1

JavaScript

In-line

Yes

Python

In-line or staged

No 2

SQL

In-line

Yes
1

For more information about limits on sharing Java UDFs, see General Limitations.

2

For more information about limits on sharing Python UDFs, see General Limitations.

Developer Guides

Guidelines and Constraints

You can ensure stability within the Snowflake environment by developing within Snowflake constraints. For more information, see Designing Handlers that Stay Within Snowflake-Imposed Constraints

Be sure to name functions in a way that avoids collisions with other procedures. For more information, see Naming Conventions for Procedures and UDFs

Handler Writing

For language-specific content on writing a handler, see Supported Languages.

Security

You can grant privileges on objects needed for them to perform specific SQL actions with a UDF or UDTF. For more information, see Granting Privileges for User-Defined Functions

Functions share certain security concerns with stored procedures. For more information, see the following:

Handler Code Deployment

When creating a function, you can specify its handler – which implements the function’s logic – as code in-line with the CREATE FUNCTION statement or as code external to the statement, such as compiled code packaged and copied to a stage.

For more information, see Keeping Handler Code In-line or on a Stage.

Data Type Mappings

For each handler language, there’s a separate set of mappings between the language’s data types and the SQL types used for arguments and return values. For more about the mappings for each language, see Data Type Mappings Between SQL and Handler Languages.

Create and Call Functions

You use SQL to create and call a user-defined function.

  • To create a function, execute the CREATE FUNCTION statement, specifying the function’s handler. For more information, see Creating a UDF.

  • To call a function, execute a SQL SELECT statement that specifies the function as a parameter. For more information, see Calling a UDF.

Language: English