Calling Functions and Stored Procedures in Snowpark Scala¶
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.
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 Scalar User-Defined Functions (UDFs)¶
The method for calling a UDF depends on how the UDF was created:
To call an anonymous UDF, call the
apply
method of the UserDefinedFunction object that was returned when you created the UDF.The arguments that you pass to a UDF must be Column objects. If you need to pass in a literal, use
lit()
, as explained in Using Literals as Column Objects.To call UDFs that you registered by name and UDFs that you created by executing CREATE FUNCTION, use the
callUDF
function in thecom.snowflake.snowpark.functions
object.Pass the name of the UDF as the first argument and any UDF parameters as additional arguments.
Calling a UDF returns a Column
object containing the return value of the UDF.
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 Table Functions (System Functions and UDTFs)¶
To call a table function or a user-defined table function (UDTF):
Construct a TableFunction object, passing in the name of the table function.
If you are creating a UDTF in Snowpark, you can just use the
TableFunction
object returned by theUDTFRegistration.registerTemporary
orUDTFRegistration.registerPermanent
method. See Creating User-Defined Table Functions (UDTFs).Call session.tableFunction, passing in the
TableFunction
object and aMap
of input argument names and values.
table?Function
returns a DataFrame that contains the output of the table function.
For example, suppose that you executed the following command to create a SQL UDTF:
CREATE OR REPLACE FUNCTION product_by_category_id(cat_id INT)
RETURNS TABLE(id INT, name VARCHAR)
AS
$$
SELECT id, name
FROM sample_product_data
WHERE category_id = cat_id
$$
;
The following code calls this UDTF and creates a DataFrame for the output of the UDTF. The example prints the first 10 rows of output to the console.
val dfTableFunctionOutput = session.tableFunction(TableFunction("product_by_category_id"), Map("cat_id" -> lit(10)))
dfTableFunctionOutput.show()
If you need to join the output of a table function with a DataFrame, call the DataFrame.join method that passes in a TableFunction.
Calling Stored Procedures¶
To call a stored procedure, use the sql
method of the Session
class. See
Executing SQL Statements.