Calling Functions and Stored Procedures in Snowpark

To process data in a DataFrame, you can call system-defined SQL functions, user-defined functions, and stored procedures. This topic explains how to call these in Snowpark.

In this Topic:

Calling System-Defined Functions

If you need to call system-defined SQL functions, use the equivalent functions in the com.snowflake.snowpark.functions object.

The following example calls the upper function in the functions object (the equivalent of the system-defined UPPER function) to return the values in the name column with the letters in uppercase:

// Import the upper function from the functions object.
import com.snowflake.snowpark.functions._
...
session.table("products").select(upper(col("name"))).show()

If a system-defined SQL function is not available in the functions object, you can use one of the following approaches:

  • Use the callBuiltin function to call the system-defined function.

  • Use the builtin function to create a function object that you can use to call the system-defined function.

callBuiltin and builtin are defined in the com.snowflake.snowpark.functions object.

For callBuiltin, pass the name of the system-defined function as the first argument. If you need to pass the values of columns to the system-defined function, define and pass Column objects as additional arguments to the callBuiltin function.

The following example calls the system-defined function RADIANS, passing in the value from the column col1:

// Import the callBuiltin function from the functions object.
import com.snowflake.snowpark.functions._
...
// Call the system-defined function RADIANS() on col1.
val result = df.select(callBuiltin("radians", col("col1"))).collect()

The callBuiltin function returns a Column, which you can pass to the DataFrame transformation methods (e.g. filter, select, etc.).

For builtin, pass the name of the system-defined function, and use the returned function object to call the system-defined function. For example:

// Import the callBuiltin function from the functions object.
import com.snowflake.snowpark.functions._
...
// Create a function object for the system-defined function RADIANS().
val radians = builtin("radians")
// Call the system-defined function RADIANS() on col1.
val result = df.select(radians(col("col1"))).collect()

Calling User-Defined Functions (UDFs)

To call UDFs that you registered by name and UDFs that you created by executing CREATE FUNCTION, use the callUDF function in the com.snowflake.snowpark.functions object. Pass the name of the UDF as the first argument and any UDF parameters as additional arguments.

The following example calls the UDF function myFunction, passing in the values from the columns col1 and col2. The example passes the return value from myFunction to the select method of the DataFrame.

// Import the callUDF function from the functions object.
import com.snowflake.snowpark.functions._
...
// Runs the scalar function 'myFunction' on col1 and col2 of df.
val result =
    df.select(
        callUDF("myDB.schema.myFunction", col("col1"), col("col2"))
    ).collect()

Calling Stored Procedures

To call a stored procedure, use the sql method of the Session class. See Executing SQL Statements.