SQLstream provides a number of built-in functions, and also allows users to extend s-Server’s capabilities by means of user-defined functions (UDFs) and user-defined transformations (UDXs). You define both using CREATE FUNCTION. Functions must be declared within schemas, but can be added to a SQL path.
Values passed to (or returned from) a user-defined function or transformation must be exactly the same data types as the corresponding parameter definitions. In other words, implicit casting is not allowed in passing parameters to (or returning values from) a user-defined function or a user-defined transform.
s-Server follows standard SQL rules for how SQL scalar parameters in the CREATE FUNCTION statement are mapped to Java method arguments. These are summarized in a table below. See https://db.apache.org/derby/docs/10.12/ref/rrefsqljargmatching.html for more details.
This topic contains information on the following subtopics:
A user-defined function can implement complex calculations or interact with an external system, taking zero or more scalar parameters and returning a scalar result. UDFs operate like built-in functions such as FLOOR() or LOWER(). For each occurrence of a user-defined function within a SQL statement, that UDF is called once per row with scalar parameters (constants or column values in that row).
Note: any Java class referenced by a UDF must be explicitly defined as part of a package (using a package clause in the source file for the Java class).
CREATE FUNCTION ''<function_name>'' ( ''<parameter_list>'' ) RETURNS ''<data type>'' LANGUAGE SQL [ SPECIFIC ''<specific_function_name>'' ] [ [NOT] DETERMINISTIC ] CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] RETURN ''<SQL-defined function body>''
A SQL-defined function body consists only of a single RETURN statement.
CREATE FUNCTION get_fraction( degrees DOUBLE ) RETURNS DOUBLE CONTAINS SQL RETURN degrees - FLOOR(degrees) ;
CREATE FUNCTION <function_name> ( <parameter_list> ) RETURNS <built-in scalar type> LANGUAGE JAVA PARAMETER STYLE [SYSTEM DEFINED] JAVA [ SPECIFIC ''<specific_function_name>'' ] [ [NOT] DETERMINISTIC ] NO SQL [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] EXTERNAL NAME ['class <fully qualified static method name> ' | '<qualified jar name>:<fully qualified static method name>']
Any parameters to a Java-defined external function must be specified using built-in data types. While the Java function can return a value, changes to any values passed to the function as parameters only exist internal to the Java function. They are not passed back to the calling SQL.
The Java class containing the method must live inside a package.
The following EXTERNAL NAME clauses will work:
EXTERNAL NAME '"FOO_SCHEMA":foo.VarbinaryFunctions.toUTF8String' <para styleclass="Code Example"></para> EXTERNAL NAME '"FOO_SCHEMA":foo.bar.VarbinaryFunctions.toUTF8String'
but the following declaration will raise an error when you try to invoke the function:
EXTERNAL NAME '"FOO_SCHEMA":VarbinaryFunctions.toUTF8String'
CREATE JAR "getFraction" LIBRARY 'file:/home/aspen/getFraction.jar' OPTIONS(0); --- This code loads the installed Java JAR file CREATE FUNCTION get_fraction(degrees DOUBLE ) RETURNS DOUBLE LANGUAGE JAVA NO SQL EXTERNAL NAME 'GPSFuncsJavaLib:com.sqlstream.examples.geolocate.Degree.getFraction' ; CREATE FUNCTION get_fraction( degrees DOUBLE ) RETURNS DOUBLE LANGUAGE JAVA NO SQL EXTERNAL NAME 'class com.sqlstream.examples.geolocate.Degree.getFraction' ;
Note that the above examples only cover the sql CREATE FUNCTION call. To fully define a Java UDF, you need to create and define a Java class (such as com.sqlstream.examples.geolocate.Degree) using CREATE JAR. See the topic GeoLocation: An Example of User-Defined Functions in the s-Server Integration Guide for full examples covering the entire process.
A user-defined transform is a user-defined function that returns a set of rows or a stream of rows. Its input arguments can be scalars or cursors. A cursor is an object representing a subquery, which the user-defined transform can use to read the subquery results. The subquery can be relational or streaming.
A user-defined transform is always implemented by a Java class, which must be located in a java package.
A UDX is declared by a sql CREATE FUNCTION statement, much like a user-defined function.
The salient differences are:
A stream always has a rowtime column. Therefore, a cursor based on a streaming query will have a rowtime, but a cursor based on a relational query will not. As a result, the SQL that calls the UDX differs:
CURSOR(SELECT STREAM....) has a rowtime CURSOR(SELECT....) has no rowtime.
The declaration of a UDX states its output rowtype, although with the “c.*” construct the output can depend dynamically on the input type. The rowtype of an input cursor is always dynamic.
A UDX is required to set the value of every output column, matching its declared data type. ROWTIME is special:
Good practice for coding a Java UDX is:
The following examples only cover the CREATE FUNCTION call. To fully define a Java UDX, the Java class specified in the EXTERNAL NAME clause must also be created and loaded into SQLstream s-Server. See the SDK topic of the SQLstream s-Server Integration Guide for full examples covering the entire process. The examples presented in this topic refer to the following stream definitions:
CREATE STREAM transmitStream ( bytesSent INTEGER, data_sent VARBINARY(1024)); CREATE STREAM receiveStream ( bytesRecieved INTEGER, data_received VARBINARY(1024));
The first example defines a UDX named addTimeStamp. The input is the result of a query expression, and the output is the same set of columns as the input with the addition of a new column named ts of type TIMESTAMP. Note the use of the c.* construct. It indicates that all columns from the input expression will be present in the output, in this case with the addition of one column for a new TIMESTAMP value. Note that does not mean that the values are echoed from input to output unchanged. It only indicates that all columns defined for the input relation will be present in the output.
Before you can call a UDX as a function, you first need to install it.
We recommend building the UDX with mvn package or mvn install
In your pom add the following property override:
<properties> <plugin.factory>your.PluginFactorySubclassname</plugin.factory> </properties>
Build the plugin by invoking the following code. The makeTarball.sh script is available in /s-Server/examples/sdk/makeTarball.sh.
makeTarball.sh <plugin dir name>
Unpack this tarball into the directory *\$SQLSTREAM_HOME/plugins.
Next, add code along the following lines to your SQL script to install the JAR:
CREATE JAR "SimpleUDX" LIBRARY 'file:/home/aspen/simpleUDX.jar' OPTIONS(0); --- This code loads the installed Java JAR file
Alternately, you can use a prepackaged script called makeTarball.sh. This script runs mvn package and adds a CREATE OR REPLACE JAR statement to install.sql, which you can then run to install the UDF. makeTarball.sh is located here:
Unpack the tarball into the directory $SQLSTREAM_HOME/plugins.
In the created directory, you should find install.sql. To install your plugin, invoke sqllineClient (available in $SQLSTREAM_HOME/bin) with this script.
In order for UDXs to be available in s-Server, you need to declare them in SQL. See the topic CREATE FUNCTION in the Streaming SQL Reference Guide for more details. The following code declares a UDX for the JAR defined above.
This example shows the declaration and use of a UDX that has three inputs: one scalar integer value and two query expressions. Also note that in this example the output is fixed at 3 columns.
CREATE OR REPLACE FUNCTION crossRef(bias INTEGER, c1 CURSOR, c2 CURSOR) RETURNS TABLE(leftVal INTEGER, rightVal INTEGER, position DECIMAL(9,2)) LANGUAGE JAVA PARAMETER STYLE SYSTEM DEFINED JAVA NO SQL EXTERNAL NAME 'class com.sqlstream.udxsample.UdxSample.crossRef';
SELECT STREAM res.position FROM TABLE(crossRef( 33, CURSOR(SELECT STREAM * FROM transmitStream), CURSOR(SELECT TREAM * FROM recieveStream))) AS res;
leftVal INTEGER rightVal INTEGER position DECIMAL(9,2)
The following example has two relational inputs. The output row consists of all columns defined by both CURSORs as well as an additional DECIMAL column.
Note: In functions with multiple relational inputs, column names must be distinct for each relational input.
CREATE OR REPLACE FUNCTION confirmResults(c1 CURSOR, c2 CURSOR) RETURNS TABLE(c1.*, c2.*, confidence DECIMAL(5,2)) LANGUAGE JAVA PARAMETER STYLE SYSTEM DEFINED JAVA NO SQL EXTERNAL NAME 'class com.sqlstream.udxsample.UdxSample.confirmResults';
SELECT STREAM res.position FROM TABLE(confirmResults(CURSOR(SELECT STREAM * FROM transmitStream), CURSOR(SELECT STREAM * FROM receiveStream))) AS res;
bytesSent INTEGER data_sent VARBINARY(1024) bytesRecieved INTEGER data_received VARBINARY(1024) confidence DECIMAL(5,2)
s-Server follows the SQL standard in regards to mapping SQL scalar parameters to Java method arguments. These are described in detail here https://db.apache.org/derby/docs/10.12/ref/rrefsqljargmatching.html and summarized in the table below.
Note: s-Server does not support the creation of columns with interval data types. This is consistent with the JDBC specification, which also does not have support for the INTERVAL data type.
|SQL Type||Primitive Match||Wrapper Match|