Java

Java main class

Usage notes

• Do not use inline Java. It is difficult to test, has no IDE support, and is slow.

• Always use separate JARs for your Java UDF scripts.

• If you build an external JAR, you must include the Exasol UDF API for Java with the Java package.

• All classes that are defined directly in the script go implicitly inside the com.exasol Java package.

  The statement package com.exasol; is implicitly added to the beginning of the script code.

• The callback functions run(), init(), cleanup(), getDefaultOutputColumns(), and generateSqlForImportSpec() must be implemented within the main class in the script.

• The name of the script main class must be identical to the name of the script and must follow the general rules for identifiers.

• You can specify the script class explicitly using the keyword %scriptclass. For example, %scriptclass com.mycompany.MyScriptClass;).

For more details about the syntax for %jvmoption, %jar, %import, %scriptclass, and other script options, see Exasol UDF Script Options: User Documentation.

Exasol UDF API for Java

The Exasol UDF API for Java library facilitates the development of Java code. All released versions of the library artifact as well as examples of use with different package managers can be found on Maven Central.

The library is used by specifying the groupId, artifactId, and version as a dependency in your project.

Examples

Replace the version number used in the examples (1.0.5) with the number of the latest released version on Maven Central.

Example 1 - Maven:

<dependency>
    <groupId>com.exasol</groupId>
    <artifactId>udf-api-java</artifactId>
    <version>1.0.5</version>
</dependency>

Example 2 - Gradle (Groovy):

implementation 'com.exasol:udf-api-java:1.0.5'

Example 3 - Gradle (Kotlin):

implementation("com.exasol:udf-api-java:1.0.5")

Example of a complete Maven POM file

https://github.com/exasol/virtual-schema-common-java/blob/main/pom.xml.

API documentation

https://exasol.github.io/udf-api-java/com/exasol/package-summary.html.

Parameters

The internal Java data types are not identical to SQL data types, which means that casts are necessary for input and output data.

The Java literal null is the equivalent of the SQL value NULL.

The input data can be accessed through get() functions. For example, ctx.getString("url"), where ctx refers to the name of the context parameter that is passed to the run() function.

You can use a dynamic number of parameters through the notation (...), for example, CREATE JAVA SCALAR SCRIPT my_script (...). The parameters can then be accessed through an index where ctx.getString(0) is the first parameter.

The number of parameters and their data types are part of the metadata and are determined during the call of the script.

Metadata

You can access the following metadata through the object ExaMetadata:

Callback functions

/*
    This example loads from a webserver
    and processes the following file goalies.xml:

    <?xml version='1.0' encoding='UTF-8'?>
    <users>
        <user active="1">
        <first_name>Manuel</first_name>
        <last_name>Neuer</last_name>
        </user>
        <user active="1">
        <first_name>Joe</first_name>
        <last_name>Hart</last_name>
        </user>
        <user active="0">
        <first_name>Oliver</first_name>
        <last_name>Kahn</last_name>
        </user>
    </users>
*/
 
--/
    CREATE JAVA SCALAR SCRIPT process_users(url VARCHAR(500))
    EMITS (firstname VARCHAR(20), lastname VARCHAR(20)) AS
  
    import java.net.URL;
    import java.net.URLConnection;
    import javax.xml.parsers.DocumentBuilder;
    import javax.xml.parsers.DocumentBuilderFactory;
    import org.w3c.dom.Document;
    import org.w3c.dom.NodeList;
    import org.w3c.dom.Node;
    import org.w3c.dom.Element;
   
  public class PROCESS_USERS {
      public static void run(ExaMetadata exa, ExaIterator ctx) throws Exception {
      URL url = new URL(ctx.getString("url"));
      URLConnection conn = url.openConnection();
      DocumentBuilder docBuilder =
      DocumentBuilderFactory.newInstance().newDocumentBuilder();
      Document doc = docBuilder.parse(conn.getInputStream());
      NodeList nodes =
      doc.getDocumentElement().getElementsByTagName("user");
      for (int i = 0; i < nodes.getLength(); i++) {
             if (nodes.item(i).getNodeType() != Node.ELEMENT_NODE)
             continue;
             Element elem = (Element)nodes.item(i);
             if (!elem.getAttribute("active").equals("1"))
                continue;
             Node name = elem.getElementsByTagName("first_name").item(0);
         String firstName = name.getChildNodes().item(0).getNodeValue();
         name = elem.getElementsByTagName("last_name").item(0);
         String lastName = name.getChildNodes().item(0).getNodeValue();
         ctx.emit(firstName, lastName);
      }
       }
}
/
SELECT process_users ('http://www.my_valid_webserver/goalies.xml')
FROM DUAL;

Data iterator

The following functions are provided in the ExaIterator class:

• next()
• emit(Object... values)
• reset()
• size()
• getInteger(String name) and getInteger(int column)
• getLong(String name) and getLong(int column)
• getBigDecimal(String name) and getBigDecimal(int column)
• getDouble(String name) and getDouble(int column)
• getString(String name) and getString(int column)
• getBoolean(String name) and getBoolean(int column)
• getDate(String name) and getDate(int column)
• getTimestamp(String name) and getTimestamp(int column)

Access connection definitions

When you define a connection with CREATE CONNECTION, the data in the connection object can be obtained by a Java UDF script through the function ExaMetadata.getConnection(String connectionName). The result is a Java object that implements the Interface com.exasol.ExaConnectionInformation, which features the following functions:

Example:

The following example shows how to access the connection definitions in a Java UDF.

CREATE CONNECTION MY_CONNECTION
TO 'http://my-server.com'
USER 'my_user'
IDENTIFIED BY 'my_password';

--/
CREATE OR REPLACE JAVA SCALAR SCRIPT RETURN_CONNECTION()
EMITS (TYPE_FIELD VARCHAR(2000000),ADDRESS_FIELD VARCHAR(2000000),USER_FIELD VARCHAR(2000000),PW_FIELD VARCHAR(2000000)) AS
public class RETURN_CONNECTION {
    public static void run(ExaMetadata exa, ExaIterator ctx) throws Exception {
        String connectionName = "MY_CONNECTION";
        ExaConnectionInformation connection = exa.getConnection(connectionName);
        ExaConnectionInformation.ConnectionType type=connection.getType();
        String address = connection.getAddress();
        String userName = connection.getUser();
        String pass = connection.getPassword();
        ctx.emit(type.toString(),address,userName,pass);
    }
}
/

SELECT RETURN_CONNECTION() FROM DUAL;

JVM options

To enable tuning script performance depending on its memory requirements, you can use the %jvmoption keyword to specify the following Java VM options:

• Initial heap size (-Xms)
• Maximum heap size (-Xmx)
• Thread stack size (-Xss)

Example:

This example sets the initial Java heap size to 128 MiB, maximum heap size to 1024 MiB, and thread stack size to 512 KiB.

%jvmoption -Xms128m -Xmx1024m -Xss512k;

If multiple values are given for a single option (for example, resulting from the import of another script), the last value will be used.

Import other scripts

In addition to the importScript() function of class ExaMetadata, you can import other scripts by using the keyword %import. When a script has been imported it will be accessible in the namespace. For example: OTHER_SCRIPT.my_method().

Syntax

%import <schema>.<script>

Examples:

CREATE SCHEMA IF NOT EXISTS TEST;

--/
CREATE OR REPLACE JAVA SCALAR SCRIPT TEST.JAVADEMO() RETURNS VARCHAR(2000) AS

class JAVADEMO {
  static String run(ExaMetadata exa, ExaIterator ctx) throws Exception {
    return "Minimal Java UDF";
  }
}
/

SELECT TEST.JAVADEMO();

CREATE SCHEMA IF NOT EXISTS LIB;

--/
CREATE OR REPLACE JAVA SCALAR SCRIPT LIB.MYLIB() RETURNS VARCHAR(2000) AS class MYLIB {
 public static String helloWorld(){
        return "Hello Java World!";
 }
}
/

CREATE SCHEMA IF NOT EXISTS TEST;

--/
CREATE OR REPLACE JAVA SCALAR SCRIPT TEST.MYHELLOWORLD() RETURNS VARCHAR(2000) AS 

%import LIB.MYLIB;

public class MYHELLOWORLD {
 public static String run(ExaMetadata exa, ExaIterator ctx) throws Exception {
 return MYLIB.helloWorld();
 }
}
/

select TEST.MYHELLOWORLD();

Exceptions

The following Exasol-specific exceptions can be thrown:

ExaCompilationException

This exception happens if the Java code of the UDF cannot be compiled. Possible reasons are:

• Invalid Java code
• Missing dependencies (%jar options)
• Invalid script options

ExaDataTypeException

This exception happens if the input/output data types of the UDF declaration do not match with the Java source code. For example:

--/
CREATE OR REPLACE java SCALAR SCRIPT test() RETURNS DOUBLE AS 
class TEST {
    static String run(ExaMetadata exa, ExaIterator ctx) throws Exception
    {
        return "Hello World";
    }
};
/

results in:

SQL Error [22002]: VM error: F-UDF-CL-LIB-1127: F-UDF-CL-SL-JAVA-1002: F-UDF-CL-SL-JAVA-1013: 
com.exasol.ExaDataTypeException: E-UDF-CL-SL-JAVA-1114: emit column 'RETURN' is of type DOUBLE but data given have type java.lang.String (Session: 1832716691549061120)

ExaIterationException

This exception happens if the UDF tries to access the data iterator after the iteration has ended (calling next after the iterator returned false).