Returning tabular data from a Java stored procedure (original) (raw)

You can write a procedure that returns data in tabular form. To write a procedure that returns tabular data, do the following:

• Specify TABLE(...) as the procedure’s return type in your CREATE PROCEDURE statement.
  As TABLE parameters, you can specify the returned data’s column names and types if you know them. If you don’t know the returned columns when defining the procedure – such as when they’re specified at run time – you can leave out the TABLE parameters. When you do, the procedure’s return value columns will be converted from the columns in the dataframe returned by its handler. Column data types will be converted to SQL according to the mapping specified in SQL-Java Data Type Mappings.
• Write the handler so that it returns the tabular result in a Snowpark DataFrame.
  For more information about dataframes, see Working with DataFrames in Snowpark Java.

Note

A procedure will generate an error at runtime if either of the following is true:

• It declares TABLE as its return type but its handler does not return a DataFrame.
• Its handler returns a DataFrame but the procedure doesn’t declare TABLE as its return type.

Example¶

The examples in this section illustrate returning tabular values from a procedure that filters for rows where a column matches a string.

Defining the data¶

Code in the following example creates a table of employees.

CREATE OR REPLACE TABLE employees(id NUMBER, name VARCHAR, role VARCHAR); INSERT INTO employees (id, name, role) VALUES (1, 'Alice', 'op'), (2, 'Bob', 'dev'), (3, 'Cindy', 'dev');

Declaring a procedure to filter rows¶

Code in the following two examples create a stored procedure that takes the table name and role as arguments, returning the rows in the table whose role column value matches the role specified as an argument.

Specifying return column names and types¶

This example specifies column names and types in the RETURNS TABLE() statement.

CREATE OR REPLACE PROCEDURE filter_by_role(table_name VARCHAR, role VARCHAR) RETURNS TABLE(id NUMBER, name VARCHAR, role VARCHAR) LANGUAGE JAVA RUNTIME_VERSION = '11' PACKAGES = ('com.snowflake:snowpark:latest') HANDLER = 'Filter.filterByRole' AS importcom.snowflake.snowparkjava.∗;publicclassFilterpublicDataFramefilterByRole(Sessionsession,StringtableName,Stringrole)DataFrametable=session.table(tableName);DataFramefilteredRows=table.filter(Functions.col("role").equalto(Functions.lit(role)));returnfilteredRows;import com.snowflake.snowpark_java.*;

public class Filter { public DataFrame filterByRole(Session session, String tableName, String role) { DataFrame table = session.table(tableName); DataFrame filteredRows = table.filter(Functions.col("role").equal_to(Functions.lit(role))); return filteredRows; } }importcom.snowflake.snowparkj​ava.∗;publicclassFilterpublicDataFramefilterByRole(Sessionsession,StringtableName,Stringrole)DataFrametable=session.table(tableName);DataFramefilteredRows=table.filter(Functions.col("role").equalt​o(Functions.lit(role)));returnfilteredRows;;

Note

Currently, in the RETURNS TABLE(...) clause, you can’t specify GEOGRAPHY as a column type. This applies whether you are creating a stored or anonymous procedure.

CREATE OR REPLACE PROCEDURE test_return_geography_table_1() RETURNS TABLE(g GEOGRAPHY) ...

WITH test_return_geography_table_1() AS PROCEDURE RETURNS TABLE(g GEOGRAPHY) ... CALL test_return_geography_table_1();

If you attempt to specify GEOGRAPHY as a column type, calling the stored procedure results in the error:

Stored procedure execution error: data type of returned table does not match expected returned table type

To work around this issue, you can omit the column arguments and types in RETURNS TABLE().

CREATE OR REPLACE PROCEDURE test_return_geography_table_1() RETURNS TABLE() ...

WITH test_return_geography_table_1() AS PROCEDURE RETURNS TABLE() ... CALL test_return_geography_table_1();

Omitting return column names and types¶

Code in the following example declares a procedure that allows return value column names and types to be extrapolated from columns in the handler’s return value. It omits the column names and types from the RETURNS TABLE() statement.

CREATE OR REPLACE PROCEDURE filter_by_role(table_name VARCHAR, role VARCHAR) RETURNS TABLE() LANGUAGE JAVA RUNTIME_VERSION = '11' PACKAGES = ('com.snowflake:snowpark:latest') HANDLER = 'FilterClass.filterByRole' AS importcom.snowflake.snowparkjava.∗;publicclassFilterClasspublicDataFramefilterByRole(Sessionsession,StringtableName,Stringrole)DataFrametable=session.table(tableName);DataFramefilteredRows=table.filter(Functions.col("role").equalto(Functions.lit(role)));returnfilteredRows;import com.snowflake.snowpark_java.*;

public class FilterClass { public DataFrame filterByRole(Session session, String tableName, String role) { DataFrame table = session.table(tableName); DataFrame filteredRows = table.filter(Functions.col("role").equal_to(Functions.lit(role))); return filteredRows; } }importcom.snowflake.snowparkj​ava.∗;publicclassFilterClasspublicDataFramefilterByRole(Sessionsession,StringtableName,Stringrole)DataFrametable=session.table(tableName);DataFramefilteredRows=table.filter(Functions.col("role").equalt​o(Functions.lit(role)));returnfilteredRows;;

Calling the procedure¶

The following example calls the stored procedure:

CALL filter_by_role('employees', 'dev');

The procedure call produces the following output:

+----+-------+------+ | ID | NAME | ROLE | +----+-------+------+ | 2 | Bob | dev | | 3 | Cindy | dev | +----+-------+------+