Calling Functions and Stored Procedures in Snowpark Java¶
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 static methods in the Functions class.
The following example calls the
upper static method in the
Functions class (the equivalent of the system-defined
UPPER function) to return the values in the name column with the letters in uppercase:
DataFrame df = session.table("sample_product_data"); df.select(Functions.upper(Functions.col("name"))).show();
If a system-defined SQL function is not available in the
Functions class, you can use the
static method to call the system-defined function.
callUDF, 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
The following example calls the system-defined function RADIANS, passing in the value from the
// Call the system-defined function RADIANS() on degrees. DataFrame dfDegrees = session.range(0, 360, 45).rename("degrees", Functions.col("id")); dfDegrees.select(Functions.col("degrees"), Functions.callUDF("radians", Functions.col("degrees"))).show();
callUDF method returns a
Column, which you can pass to the
DataFrame transformation methods (e.g. filter, select, etc.).
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
applymethod 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
Functions.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
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
doubleUdf, passing in the value from the columns
example passes the return value from
doubleUdf to the
select method of the DataFrame.
import com.snowflake.snowpark_java.types.*; ... // Create and register a temporary named UDF // that takes in an integer argument and returns an integer value. UserDefinedFunction doubleUdf = session .udf() .registerTemporary( "doubleUdf", (Integer x) -> x + x, DataTypes.IntegerType, DataTypes.IntegerType); // Call the named UDF, passing in the "quantity" column. // The example uses withColumn to return a DataFrame containing // the UDF result in a new column named "doubleQuantity". DataFrame df = session.table("sample_product_data"); DataFrame dfWithDoubleQuantity = df.withColumn("doubleQuantity", doubleUdf.apply(Functions.col("quantity"))); dfWithDoubleQuantity.show();
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.
Call the tableFunction method of the Session object, passing in the
TableFunctionobject and a
Mapof 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.
import java.util.HashMap; import java.util.Map; ... Map<String, Column> arguments = new HashMap<>(); arguments.put("cat_id", Functions.lit(10)); DataFrame dfTableFunctionOutput = session.tableFunction(new TableFunction("product_by_category_id"), arguments); dfTableFunctionOutput.show();
If you need to join the output of a table function with a DataFrame, call the 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.