Designing Java UDFs¶

SQL-Java data type mappings for parameters and return types¶

For information on how Snowflake converts between Java and SQL data types, see Data Type Mappings Between SQL and Handler Languages.

TIMESTAMP_LTZ values and time zones¶

A Java UDF is largely isolated from the environment in which it is called. However, the timezone is inherited from the calling environment. If the caller’s session set a default time zone before calling the Java UDF, then the Java UDF has the same default time zone. Java UDF uses the same IANA Time Zone Database data as the native TIMEZONE Snowflake SQL uses (i.e. data from release 2021a of the Time Zone Database).

NULL values¶

Snowflake supports two distinct NULL values: SQL NULL and VARIANT’s JSON null. (For information about Snowflake VARIANT NULL, see NULL Values.)

Java supports one null value, which is only for non-primitive data types.

A SQL NULL argument to a Java UDF translates to the Java null value, but only for Java data types that support null.

A returned Java null value translates back to SQL NULL.

Arrays and variable number of arguments¶

Java UDFs can receive arrays of any of the following Java data types:

• String

• boolean

• double

• float

• int

• long

• short

The data type of the SQL values passed must be compatible with the corresponding Java data type. For details about data type compatibility, see SQL-Java Data Type Mappings.

The following additional rules apply for each of the specified Java data types:

• boolean: The Snowflake ARRAY must contain only BOOLEAN elements, and must not contain any NULL values.

• int/short/long: The Snowflake ARRAY must contain only fixed-point elements with a scale of 0, and must not contain any NULL values.

• float/double: The Snowflake ARRAY must contain either:

  • FLOAT elements.

  • Fixed-point elements (with any scale).

  The ARRAY must not contain any NULL values.

Java methods can receive these arrays in either of two ways:

• Using Java’s array feature.

• Using Java’s varargs (variable number of arguments) feature.

In both cases, your SQL code must pass an ARRAY.

Sharing state across calls¶

Snowflake expects scalar UDFs to be processed independently. Relying on state shared between invocations can result in unexpected behavior. This is because the system can process rows in any order and spread those invocations across several JVMs (for handlers written in Java or Scala) or instances (for handlers written in Python).

UDFs should avoid relying on shared state across calls to the handler method. However, there are two situations in which you might want a UDF to store shared state:

• Code that contains expensive initialization logic that you do not want to repeat for each row.

• Code that leverages shared state across rows, such as a cache.

If you need to share state across multiple rows, and if that state does not change over time, then use a constructor to create shared state by setting instance-level variables. The constructor is executed only once per instance, while the handler is called once per row, so initializing in the constructor is cheaper when a handler processes multiple rows. And because the constructor is called only once, the constructor does not need to be written to be thread-safe.

If your UDF stores shared state that changes, then your code must be prepared to handle concurrent access to that state. The next two sections provide more information about parallelism and shared state.

Understanding Java UDF parallelization¶

To improve performance, Snowflake parallelizes both across and within JVMs.

• Across JVMs:

  Snowflake parallelizes across workers in a warehouse. Each worker runs one (or more) JVMs. This means that there is no global shared state. At most, state can be shared only within a single JVM.

• Within JVMs:

  • Each JVM can execute multiple threads that can call the same instance’s handler method in parallel. This means that each handler method needs to be thread-safe.

  • If a UDF is IMMUTABLE and a SQL statement calls the UDF more than once with the same arguments for the same row, then the UDF returns the same value for each call for that row. For example, the following returns the same value twice for each row if the UDF is IMMUTABLE:

    select
           my_java_udf(42),
           my_java_udf(42)
        from table1;
    

    Copy

    If you would like multiple calls to return independent values even when passed the same arguments, and if you do not want to declare the function VOLATILE, then bind multiple separate UDFs to the same handler method. For example:

    1. Create a JAR file named @java_udf_stage/rand.jar with code:

       class MyClass {
       
           private double x;
       
           // Constructor
           public MyClass()  {
               x = Math.random();
           }
       
           // Handler
           public double myHandler() {
               return x;
           }
       }
       

       Copy

    2. Create the Java UDFs as shown below. These UDFs have different names, but use the same JAR file and the same handler within that JAR file.

       create function my_java_udf_1()
           returns double
           language java
           imports = ('@java_udf_stage/rand.jar')
           handler = 'MyClass.myHandler';
       
       create function my_java_udf_2()
           returns double
           language java
           imports = ('@java_udf_stage/rand.jar')
           handler = 'MyClass.myHandler';
       

       Copy

    3. The following code calls both UDFs. The UDFs point to the same JAR file and handler. These calls create two instances of the same class. Each instance returns an independent value, so the example below returns two independent values, rather than returning the same value twice:

       select
               my_java_udf_1(),
               my_java_udf_2()
           from table1;
       

       Copy

Storing JVM state information¶

One reason to avoid relying on dynamic shared state is that rows are not necessarily processed in a predictable order. Each time a SQL statement is executed, Snowflake can vary the number of batches, the order in which batches are processed, and the order of rows within a batch. If a scalar UDF is designed so that one row affects the return value for a subsequent row, then the UDF can return different results each time that the UDF is executed.