Java UDF handler examples¶

Passing a NULL to an in-line Java UDF¶

This uses the echo_varchar() UDF defined above. The SQL NULL value is implicitly converted to Java null, and that Java null is returned and implicitly converted back to SQL NULL:

Call the UDF:

SELECT echo_varchar(NULL);
+--------------------+
| ECHO_VARCHAR(NULL) |
|--------------------|
| NULL               |
+--------------------+

Passing via an ARRAY¶

Declare the Java parameter as an array. For example, the third parameter in the following method is a String array:

static int myMethod(int fixedArgument1, int fixedArgument2, String[] stringArray)

Below is a complete example:

Create and load the table:

CREATE TABLE string_array_table(id INTEGER, a ARRAY);
INSERT INTO string_array_table (id, a) SELECT
        1, ARRAY_CONSTRUCT('Hello');
INSERT INTO string_array_table (id, a) SELECT
        2, ARRAY_CONSTRUCT('Hello', 'Jay');
INSERT INTO string_array_table (id, a) SELECT
        3, ARRAY_CONSTRUCT('Hello', 'Jay', 'Smith');

Create the UDF:

CREATE OR REPLACE FUNCTION concat_varchar_2(a ARRAY)
  RETURNS VARCHAR
  LANGUAGE JAVA
  HANDLER = 'TestFunc_2.concatVarchar2'
  TARGET_PATH = '@~/TestFunc_2.jar'
  AS
  $$
  class TestFunc_2 {
      public static String concatVarchar2(String[] strings) {
          return String.join(" ", strings);
      }
  }
  $$;

Call the UDF:

SELECT concat_varchar_2(a)
  FROM string_array_table
  ORDER BY id;
+---------------------+
| CONCAT_VARCHAR_2(A) |
|---------------------|
| Hello               |
| Hello Jay           |
| Hello Jay Smith     |
+---------------------+

Passing via varargs¶

Using varargs is very similar to using an array.

In your Java code, use Java’s varargs declaration style:

static int myMethod(int fixedArgument1, int fixedArgument2, String ... stringArray)

Below is a complete example. The only significant difference between this example and the preceding example (for arrays) is the declaration of the parameters to the method.

Create and load the table:

CREATE TABLE string_array_table(id INTEGER, a ARRAY);
INSERT INTO string_array_table (id, a) SELECT
        1, ARRAY_CONSTRUCT('Hello');
INSERT INTO string_array_table (id, a) SELECT
        2, ARRAY_CONSTRUCT('Hello', 'Jay');
INSERT INTO string_array_table (id, a) SELECT
        3, ARRAY_CONSTRUCT('Hello', 'Jay', 'Smith');

Create the UDF:

CREATE OR REPLACE FUNCTION concat_varchar(a ARRAY)
  RETURNS VARCHAR
  LANGUAGE JAVA
  HANDLER = 'TestFunc.concatVarchar'
  TARGET_PATH = '@~/TestFunc.jar'
  AS
  $$
  class TestFunc {
      public static String concatVarchar(String ... stringArray) {
          return String.join(" ", stringArray);
      }
  }
  $$;

Call the UDF:

SELECT concat_varchar(a)
    FROM string_array_table
    ORDER BY id;
+-------------------+
| CONCAT_VARCHAR(A) |
|-------------------|
| Hello             |
| Hello Jay         |
| Hello Jay Smith   |
+-------------------+

Prerequisites¶

Before your Java handler code can read a file on a stage, you must do the following to make the file available to the code:

1. Create a stage that’s available to your handler.

   You can use an external stage or internal stage. If you use an internal stage, it must be a user or named stage; Snowflake does not currently support using a table stage for UDF dependencies. For more on creating a stage, see CREATE STAGE. For more on choosing an internal stage type, see Choosing an internal stage for local files.

   Keep in mind that adequate privileges on the stage must be assigned to roles performing SQL actions that read from the stage. For more information, see Granting privileges for user-defined functions.

2. To the stage, copy the file that will be read by code.

   You can copy the file from a local drive to a stage by using the PUT command. For command reference, see PUT. For information on staging files with PUT, see Staging data files from a local file system.

Reading a file specified statically in IMPORTS¶

Your handler can read a file whose stage path has been specified in the IMPORTS clause of the CREATE FUNCTION command.

When you specify a file in the IMPORTS clause, Snowflake copies that file from the stage to the UDF’s home directory (also called the import directory), which is the directory from which the UDF actually reads the file.

Because imported files are copied to a single directory and must have unique names within that directory, each file in the IMPORTS clause must have a distinct name, even if the files start out in different stages or different subdirectories within a stage.

The following example creates and calls a Java UDF that reads a file.

The Java source code below creates a Java method named readFile. This UDF uses this method.

import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.stream.Stream;

class TestReadRelativeFile {
  public static String readFile(String fileName) throws IOException {
    StringBuilder contentBuilder = new StringBuilder();
    String importDirectory = System.getProperty("com.snowflake.import_directory");
    String fPath = importDirectory + fileName;
    Stream<String> stream = Files.lines(Paths.get(fPath), StandardCharsets.UTF_8);
    stream.forEach(s -> contentBuilder.append(s).append("\n"));
    return contentBuilder.toString();
  }
}

The following SQL code creates the UDF. This code assumes that the Java source code has been compiled and put into a JAR file named TestReadRelativeFile.jar, which the UDF imports. The second and third imported files, my_config_file_1.txt and my_config_file_2.txt, are configuration files that the UDF can read.

CREATE FUNCTION file_reader(file_name VARCHAR)
  RETURNS VARCHAR
  LANGUAGE JAVA
  IMPORTS = ('@my_stage/my_package/TestReadRelativeFile.jar',
             '@my_stage/my_path/my_config_file_1.txt',
             '@my_stage/my_path/my_config_file_2.txt')
  HANDLER = 'my_package.TestReadRelativeFile.readFile';

This code calls the UDF:

SELECT file_reader('my_config_file_1.txt') ...;
...
SELECT file_reader('my_config_file_2.txt') ...;

... IMPORTS = ('@MyStage/MyData.txt.gz', ...)

Reading a dynamically-specified file with SnowflakeFile¶

Using methods of the SnowflakeFile class, you can read files from a stage with your Java handler code. The SnowflakeFile class is included on the classpath available to Java UDF handlers on Snowflake.

To develop your UDF code locally, add the Snowpark JAR containing SnowflakeFile to your code’s class path. For information about snowpark.jar, see Setting Up Your Development Environment for Snowpark Java. Note that Snowpark client applications cannot use this class.

When you use SnowflakeFile, it isn’t necessary to also specify either the staged file or the JAR containing SnowflakeFile with an IMPORTS clause when you create the UDF, as in SQL with a CREATE FUNCTION statement.

Code in the following example uses SnowflakeFile to read a file from a specified stage location. Using an InputStream from the getInputStream method, it reads the file’s contents into a String variable.

CREATE OR REPLACE FUNCTION sum_total_sales(file STRING)
  RETURNS INTEGER
  LANGUAGE JAVA
  HANDLER = 'SalesSum.sumTotalSales'
  TARGET_PATH = '@jar_stage/sales_functions2.jar'
  AS
  $$
  import java.io.InputStream;
  import java.io.IOException;
  import java.nio.charset.StandardCharsets;
  import com.snowflake.snowpark_java.types.SnowflakeFile;

  public class SalesSum {

    public static int sumTotalSales(String filePath) throws IOException {
      int total = -1;

      // Use a SnowflakeFile instance to read sales data from a stage.
      SnowflakeFile file = SnowflakeFile.newInstance(filePath);
      InputStream stream = file.getInputStream();
      String contents = new String(stream.readAllBytes(), StandardCharsets.UTF_8);

      // Omitted for brevity: code to retrieve sales data from JSON and assign it to the total variable.

      return total;
    }
  }
  $$;

Call the UDF, passing the location of the file in a scoped URL to reduce the likelihood of file injection attacks.

SELECT sum_total_sales(BUILD_SCOPED_FILE_URL('@sales_data_stage', '/car_sales.json'));

String filename = "@my_stage/filename.txt";
String sfFile = SnowflakeFile.newInstance(filename, false);

Reading a dynamically-specified file with InputStream¶

You can read file contents directly into a java.io.InputStream by making your handler function’s argument an InputStream variable. This can be useful when the function’s caller will want to pass a file path as an argument.

Code in the following example has a handler function sumTotalSales that takes an InputStream and returns an int. At run time, Snowflake automatically assigns the contents of the file at the file variable’s path to the stream argument variable.

CREATE OR REPLACE FUNCTION sum_total_sales(file STRING)
  RETURNS INTEGER
  LANGUAGE JAVA
  HANDLER = 'SalesSum.sumTotalSales'
  TARGET_PATH = '@jar_stage/sales_functions2.jar'
  AS
  $$
  import java.io.InputStream;
  import java.io.IOException;
  import java.nio.charset.StandardCharsets;

  public class SalesSum {

    public static int sumTotalSales(InputStream stream) throws IOException {
      int total = -1;
      String contents = new String(stream.readAllBytes(), StandardCharsets.UTF_8);

      // Omitted for brevity: code to retrieve sales data from JSON and assign it to the total variable.

      return total;
    }
  }
  $$;

Call the UDF, passing the location of the file in a scoped URL to reduce the likelihood of file injection attacks.

SELECT sum_total_sales(BUILD_SCOPED_FILE_URL('@sales_data_stage', '/car_sales.json'));

Create and compile the Java handler code¶

1. In the root directory of your project (here, my_udf), create a src subdirectory to hold the source .java files and a classes subdirectory to hold the generated .class files.

   You should have a directory hierarchy similar to the following:

   CopyExpand

   my_udf/
   |-- classes/
   |-- src/
   

   Show lessSee more

   Scroll to top

2. In the src directory, create a directory called mypackage to hold .java files whose classes are in the mypackage package.

3. In the mypackage directory, create a MyUDFHandler.java file that contains your source code.

   CopyExpand

   package mypackage;
   
   public class MyUDFHandler {
   
     public static int decrementValue(int i)
     {
       return i - 1;
     }
   
     public static void main(String[] argv)
     {
       System.out.println("This main() function won't be called.");
     }
   }
   

   Show lessSee more

   Scroll to top

4. From your project root directory (here, my_udf), use the javac command to compile the source code.

   The javac command in the following example compiles MyUDFHandler.java to generate a MyUDFHandler.class file in the classes directory.

   CopyExpand

   javac -d classes src/mypackage/MyUDFHandler.java
   

   Show lessSee more

   Scroll to top

   This example includes the following arguments:

   • -d classes – Directory into which generated class files should be written.

   • src/mypackage/MyUDFHandler.java – Path to the .java file in the form: source_directory/package_directory/Java_file_name.

Package the compiled code into a JAR file¶

1. Optionally, in the project root directory create a manifest file named my_udf.manifest that contains the following attributes:

   CopyExpand

   Manifest-Version: 1.0
   Main-Class: mypackage.MyUDFHandler
   

   Show lessSee more

   Scroll to top

2. From your project root directory, run the jar command to create a JAR file containing the .class file and manifest.

   The jar command in the following example puts the generated MyUDFHandler.class file in a mypackage package folder into a .jar file called my_udf.jar. The -C ./classes flag specifies the location of the .class files.

   CopyExpand

   jar cmf my_udf.manifest my_udf.jar -C ./classes mypackage/MyUDFHandler.class
   

   Show lessSee more

   Scroll to top

   This example includes the following arguments:

   • cmf – Command arguments: c to create a JAR file, m to use the specified .manifest file, and f to give the JAR file the specified name.

   • my_udf.manifest – Manifest file.

   • my_udf.jar – Name of the JAR file to create.

   • -C ./classes – Directory containing the generated .class files.

   • mypackage/MyUDFHandler.class – Package and name of .class file to include in the JAR.

Upload the JAR file with the compiled handler to a stage¶

1. In Snowflake, create a stage called jar_stage to store the JAR file containing your UDF handler.

   For more information on creating a stage, see CREATE STAGE.

2. Use the PUT command to copy the JAR file from the local file system to a stage.

   CopyExpand

   put
       file:///Users/Me/my_udf/my_udf.jar
       @jar_stage
       auto_compress = false
       overwrite = true
       ;
   

   Show lessSee more

   Scroll to top

   You can store the PUT command in a script file and then execute that file through SnowSQL.

   The snowsql command looks similar to the following:

   CopyExpand

   snowsql -a <account_identifier> -w <warehouse> -d <database> -s <schema> -u <user> -f put_command.sql
   

   Show lessSee more

   Scroll to top

   This example assumes that the user’s password is specified in the SNOWSQL_PWD environment variable.

Create the UDF with the compiled code as handler¶

Create the UDF:

CREATE FUNCTION decrement_value(i NUMERIC(9, 0))
  RETURNS NUMERIC
  LANGUAGE JAVA
  IMPORTS = ('@jar_stage/my_udf.jar')
  HANDLER = 'mypackage.MyUDFHandler.decrementValue'
  ;

Call the UDF:

SELECT decrement_value(-15);

+----------------------+
| DECREMENT_VALUE(-15) |
|----------------------|
|                  -16 |
+----------------------+