Scala UDF Handler Examples¶

This topic includes simple examples of UDF handler code written in Scala.

For information on using Scala to create a scalar UDF handler, refer to Writing a Scalar UDF in Scala. For general coding guidelines, refer to General Scala UDF Handler Coding Guidelines.

Creating and Calling a Simple In-line Scala UDF¶

The following statements create and call an in-line Scala UDF. This code returns the VARCHAR passed to it.

This function is declared with the optional CALLED ON NULL INPUT clause to indicate that the function is called even if the value of the input is NULL. (This function would return NULL with or without this clause, but you could modify the code to handle NULL another way, for example, to return an empty string.)

Create the UDF¶

CREATE OR REPLACE FUNCTION echo_varchar(x VARCHAR)
RETURNS VARCHAR
LANGUAGE SCALA
CALLED ON NULL INPUT
RUNTIME_VERSION = 2.12
HANDLER='Echo.echoVarchar'
AS
$$
class Echo {
  def echoVarchar(x : String): String = {
    return x
  }
}
$$;

Copy

Call the UDF¶

SELECT echo_varchar('Hello');

Copy

Passing a NULL to an In-line Scala UDF¶

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

Call the UDF:

SELECT echo_varchar(NULL);

Copy

Returning NULL Explicitly from an In-Line UDF¶

The following code shows how to return a NULL value explicitly. The Scala value Null is converted to SQL NULL.

Create the UDF¶

CREATE OR REPLACE FUNCTION return_a_null()
RETURNS VARCHAR
LANGUAGE SCALA
RUNTIME_VERSION = 2.12
HANDLER='TemporaryTestLibrary.returnNull'
AS
$$
class TemporaryTestLibrary {
  def returnNull(): String = {
    return null
  }
}
$$;

Copy

Call the UDF¶

SELECT return_a_null();

Copy

Passing an OBJECT to an In-line Scala UDF¶

The following example uses the SQL OBJECT data type and the corresponding Scala data type (Map[String, String]), and extracts a value from the OBJECT. This example also shows that you can pass multiple parameters to a Scala UDF.

Create and load a table that contains a column of type OBJECT:

CREATE TABLE objectives (o OBJECT);
INSERT INTO objectives SELECT PARSE_JSON('{"outer_key" : {"inner_key" : "inner_value"} }');

Copy

Create the UDF¶

CREATE OR REPLACE FUNCTION extract_from_object(x OBJECT, key VARCHAR)
RETURNS VARIANT
LANGUAGE SCALA
RUNTIME_VERSION = 2.12
HANDLER='VariantLibrary.extract'
AS
$$
import scala.collection.immutable.Map

class VariantLibrary {
  def extract(m: Map[String, String], key: String): String = {
    return m(key)
  }
}
$$;

Copy

Call the UDF¶

SELECT extract_from_object(o, 'outer_key'),
  extract_from_object(o, 'outer_key')['inner_key'] FROM OBJECTIVES;

Copy

Passing an ARRAY to an In-line Scala UDF¶

The following example uses the SQL ARRAY data type.

Create the UDF¶

CREATE OR REPLACE FUNCTION generate_greeting(greeting_words ARRAY)
RETURNS VARCHAR
LANGUAGE SCALA
RUNTIME_VERSION = 2.12
HANDLER='StringHandler.handleStrings'
AS
$$
class StringHandler {
  def handleStrings(strings: Array[String]): String = {
    return concatenate(strings)
  }
  private def concatenate(strings: Array[String]): String = {
    var concatenated : String = ""
    for (newString <- strings)  {
        concatenated = concatenated + " " + newString
    }
    return concatenated
  }
}
$$;

Copy

Call the UDF¶

SELECT generate_greeting(ARRAY_CONSTRUCT('Hello', 'world'));

Copy