Writing a Java UDF

This topic contains information on:

Creating UDFs in Java

In the SQL UDF topic, we created four user-defined functions using SQL. Though SQL is perfectly sufficient for these functions, each of them could also have been created using Java. For the sake of comparison, we’ll do that now.

Creating user-defined functions can be a multi-step process, especially when implemented externally via Java code.

We need a Java method (in a class, of course) that implements the conversions. To do so, we create a Degree.java file containing the following code under a com/sqlstream/examples/geolocate directory:

package com.sqlstream.examples.geolocate;
public class Degree {
    public static double getFraction( double degrees ) {
        return( degrees - Math.floor(degrees) );
    }
    public static int getMinutes( double degrees ) {
        return( (int)(getFraction(degrees) * 60.0) );
    }
    public static int getSeconds( double degrees ) {
        return (int)(((getFraction(degrees) * 3600.0)
                       - ((getMinutes(degrees) * 60)) ) );
    }
    public static String toDegMinSec( double degrees) {
        int degs = (int)degrees;
        double mins = getMinutes(degrees);
        double secs = getSeconds(degrees);
        return(degs + "deg " + mins + "m " + secs + "s");
    }
}

NOTES:

  • The method used must be declared as public static, accept simple scalar parameters, and return a simple scalar result.
  • Instead of writing your own methods, of course you may use existing libraries. See the Java String example below.

We recommend building the UDF with mvn package or mvn install.

Installing the Java UDF

First, we create a new schema to handle our extensions and set the schema and path to point to it:

CREATE SCHEMA geolocate_java;
SET SCHEMA 'geolocate_java';
SET PATH 'geolocate_java';

Create a JAR

To register your Java method you need to create a JAR object using SQL along the following lines to your SQL script to install the JAR:

CREATE JAR "GPSFuncsJavaLib"
    LIBRARY 'file:/home/aspen/gps.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:

$SQLSTREAM_HOME/examples/sdk/makeTarball.sh

Unpack the tarball into the directory $SQLSTREAM_HOME/plugins.

In the created directory, you should find install.sql. To install your plugin with this method, invoke sqllineClient (available in $SQLSTREAM_HOME/bin) with this script.

Create a SQL function to reference the Java method

In order for Java UDFs 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. A typical UDF definition in SQL appears below.


CREATE FUNCTION get_fraction( degrees DOUBLE )
    RETURNS DOUBLE
    LANGUAGE JAVA
    NO SQL
    EXTERNAL NAME 'GPSFuncsJavaLib:com.sqlstream.examples.geolocate.Degree.getFraction'
;
CREATE FUNCTION get_minutes( degrees DOUBLE )
    RETURNS INTEGER
    LANGUAGE JAVA
    NO SQL
    EXTERNAL NAME 'GPSFuncsJavaLib:com.sqlstream.examples.geolocate.Degree.getMinutes'
;
CREATE FUNCTION get_seconds( degrees DOUBLE )
    RETURNS INTEGER
    LANGUAGE JAVA
    NO SQL
    EXTERNAL NAME 'GPSFuncsJavaLib:com.sqlstream.examples.geolocate.Degree.getSeconds'
;
CREATE FUNCTION to_dms( degrees DOUBLE )
    RETURNS VARCHAR(20)
    LANGUAGE JAVA
    NO SQL
    EXTERNAL NAME 'GPSFuncsJavaLib:com.sqlstream.examples.geolocate.Degree.toDegMinSec'
;

Example - Great Circle distance between lat/lon points

So far, all we’ve done is duplicate user defined functions in Java that we could have created in SQL. Java functions, however, allow access to a much richer set of capabilities.

As an example, let’s create a user-defined function that calculates the great-circle distance in miles between two geographic locations, each of which is measured in degrees latitude and longitude. We’ll do this using a simple form of the great-circle distance equation, calculating the central angle using the spherical law of cosines, and multiplying by the earth’s radius in miles. This requires the use of cosine, sine, and arcosine function which do not exist in SQL, but do exist in the java.lang.Math package.

Create a Waypoint.java in the same directory as Degree.java containing the following code:

package com.sqlstream.examples.geolocate;
import java.lang.Math;
public class Waypoint {
    public static double getDistanceMiles( double lat1,
                                   double long1,
                                   double lat2,
                                   double long2 ) {
        -- Use the Great Circle Distance Formula
        -- to calculate distance in miles
 double rlat1 = Math.sin(Math.toRadians(lat1));
 double rlat2 = Math.sin(Math.toRadians(lat2));
 double rlong1 = Math.sin(Math.toRadians(long1));
 double rlong2 = Math.sin(Math.toRadians(long2));
 double cangle = Math.acos( Math.sin(rlat1) * Math.sin(rlat2) +
                                   Math.cos(rlat1) * Math.cos(rlat2) *
                                   Math.cos(rlong2-rlong1) );
        return ( 3963.0 * cangle );
    }
}

Install the UDF using the steps above.

Now, drop and recreate the jar file.


DROP JAR GPSFuncsJavaLib
  OPTIONS(0)
  CASCADE;
CREATE JAR GPSFuncsJavaLib
  LIBRARY 'file:/home/aspen/gps.jar'
  OPTIONS(0);

Finally, use CREATE FUNCTION to access the new Java extension:

CREATE FUNCTION find_distance
                  ( lat1 DOUBLE,  long1 DOUBLE, lat2 DOUBLE, long2 DOUBLE )
    RETURNS DOUBLE
    LANGUAGE JAVA
    NO SQL
    EXTERNAL NAME
      'GPSFuncsJavaLib:com.sqlstream.examples.geolocate.Waypoint.getDistanceMiles'
;

Note that dropping the jar file also required dropping all of the functions that depend on it, which we accomplished using the CASCADE keyword. The degree functions can be recreated using the same CREATE FUNCTION calls we used the first time around; that step is omitted here for brevity.

Calling Java UDFs from SQL

Finally, it’s worth noting that a UDF defined in SQL can call a UDF defined in Java:

CREATE FUNCTION to_miles( lat1 DOUBLE, long1 DOUBLE, lat2 DOUBLE, long2 DOUBLE )
RETURNS VARCHAR(30)
CONTAINS SQL
RETURN ( CAST( find_distance(lat1, long1, lat2, long2) AS VARCHAR(20) ) || ' miles' );
;