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
}
}
$$;
Call the UDF¶
SELECT echo_varchar('Hello');
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
}
}
$$;
Call the UDF¶
SELECT return_a_null();
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"} }');
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)
}
}
$$;
Call the UDF¶
SELECT extract_from_object(o, 'outer_key'),
extract_from_object(o, 'outer_key')['inner_key'] FROM OBJECTIVES;
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
}
}
$$;
Call the UDF¶
SELECT generate_greeting(ARRAY_CONSTRUCT('Hello', 'world'));