Writing Stored Procedures in Java¶

Prerequisites¶

You must use version 1.3.0 or a more recent version of the Snowpark library.

If you are writing a stored procedure, you must compile your classes to run in Java version 11.x.

Setting Up Your Development Environment for Snowpark¶

Set up your development environment to use the Snowpark library. See Setting Up Your Development Environment for Snowpark Java.

Structuring and Building Handler Code¶

You can keep handler source code in-line with the SQL that creates the procedure or keep handler compiled result in a separate location and reference it from the SQL. For more information, see Keeping Handler Code In-line or on a Stage.

For more on building handler source code for use with a procedure, see Packaging Handler Code.

Limitations¶

Snowpark Stored Procedures have the following limitations:

• Concurrency is not supported. For example, from within your code, you cannot submit queries from multiple threads. Code that concurrently issues multiple queries will produce an error.

• If you are executing your stored procedure from a task, you must specify a warehouse when creating the task. (You cannot use Snowflake-managed compute resources to run the task.)

• Reading and writing files from stored procedure handler code in Java is not yet fully supported. Reading and writing files may result in unstable behavior. This includes receiving an InputStream as an argument and using methods available from the FileOperation class (which you typically access via the Session.file method), including put and get.

• Keep in mind the following limitations for using some Snowpark APIs in your stored procedure.

  • When you use APIs that execute PUT and GET commands (including Session.sql("PUT ...") and Session.sql("GET ...")), you may write only to the /tmp directory in the memory-backed file system provided for the query calling the procedure.

  • Do not use APIs for asynchronous actions.

  • Do not use APIs that create new sessions (for example, Session.builder().configs(...).create()).

  • Using session.jdbcConnection (and the connection returned from it) is not supported because it may result in unsafe behavior.

• Creating named temp objects is not supported in an owner’s rights stored procedure. An owner’s rights stored procedure is a stored procedure that runs with the privileges of the stored procedure owner. For more information, refer to caller’s rights or owner’s rights.

Planning to Write Your Stored Procedure¶

Writing the Class¶

The method that you define should be part of a class.

When writing the class, note the following:

• The class and method must not be protected or private.

• If the method is not static and you want to define a constructor, define a zero-argument constructor for the class. Snowflake invokes this zero-argument constructor at initialization time to create an instance of your class.

• You can define different methods for different stored procedures in the same class.

Writing the Method¶

When writing the method for the stored procedure, note the following:

• Specify the Snowpark Session object as the first argument of your method.

  When you call your stored procedure, Snowflake automatically creates a Session object and passes it to your stored procedure. (You cannot create the Session object yourself.)

• For the rest of the arguments and for the return value, use the Java types that correspond to Snowflake data types.

• Your method must return a value. For stored procedures in Java, a return value is required.

• Stored procedure execution will time out unless the timer is reset by the code’s activity. In particular, the timeout timer is reset by the code’s interactions with data, including file operations, queries, and iterating through a result set.

Making Dependencies Available to Your Code¶

If your handler code depends on code defined outside the handler itself (such as classes in a JAR file) or on resource files, you can make those dependencies available to your code by uploading them to a stage. When creating the procedure, you can reference these dependencies using the IMPORTS clause.

For more information, see Making Dependencies Available to Your Code.

Accessing Data in Snowflake from Your Stored Procedure¶

To access data in Snowflake, use the Snowpark library APIs.

When handling a call to your Java stored procedure, Snowflake creates a Snowpark Session object and passes the object to the method for your stored procedure.

As is the case with stored procedures in other languages, the context for the session (e.g. the privileges, current database and schema, etc.) is determined by whether the stored procedure runs with caller’s rights or owner’s rights. For details, see Accessing and Setting the Session State.

You can use this Session object to call APIs in the Snowpark library. For example, you can create a DataFrame for a table or execute an SQL statement.

See the Snowpark Developer Guide for Java for more information.

import com.snowflake.snowpark_java.*;

public class MyClass
{
  public String myMethod(Session session, String fromTable, String toTable, int count)
  {
    session.table(fromTable).limit(count).write().saveAsTable(toTable);
    return "Success";
  }
}

Accessing Other Classes and Resource Files¶

If your code depends on classes defined outside of the stored procedure (e.g. classes in a separate JAR file) or resource files, you can make those dependencies available to the handler by uploading them to a stage. For more information, see Making Dependencies Available to Your Code.

Later, when CREATE PROCEDURE statement, use the IMPORTS clause to point to these files.

Compiling and Packaging Your Java Code¶

To make it easier to set up your stored procedure, build a JAR file that contains all of the dependencies needed for your stored procedure. Later, you’ll need to upload the JAR file to a stage and point to the JAR file from your CREATE PROCEDURE statement. This process is simpler if you have fewer JAR files to upload and point to.

For more information on choosing a location for your code, see Keeping Handler Code In-line or on a Stage.

Uploading Files to a Stage¶

To make your procedure’s logic (and other dependencies, if any) available to the procedure you’ll need to upload the files required to a stage. For more information, see Making Dependencies Available to Your Code.

Example¶

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

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');

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
$$
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;
  }
}
$$;

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

CALL test_return_geography_table_1();

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

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

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
$$
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;
  }
}
$$;

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

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