Java

This article explains how to use Java for UDF scripting in Exasol.

The following describes using Java 11 with OpenJDK 11. For more information about the Java language, refer to the Official Java Documentation.

Java main class

Guidelines for using Java:

• 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, it depends on the UDF script's API for Java. You must include the UDF script's API with the Java package. That package can be found on Exasol's artifactory.

• By default, the script main class (which includes the methods run() and optionally init() and cleanup()) must be named exactly like the name of the script. Make sure to consider the general rules for identifiers.

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

All classes that are defined directly in the script go implicitly inside the Java package package com.exasol. The statement package com.exasol; is implicitly added to the beginning of the script code.

All callback functions (run(), init(), cleanup(), getDefaultOutputColumns(), and generateSqlForImportSpec()) have to be implemented within the script main class.

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

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

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

run(), init() and cleanup() methods

The method static <Type> run(ExaMetadata, ExaIterator) is called for each input tuple (SCALAR) or each group (SET). Its parameter is a metadata object (ExaMetadata) and an iterator for the data access (ExaIterator).

To initialize expensive steps (opening external connections), you can define the method static void init(ExaMetadata). This will be executed once at the start by each virtual machine. For deinitialization purposes, the method static void cleanup(ExaMetadata) exists which is called once for each virtual machine, at the end of execution.

Data Iterator

The following methods are provided in the class ExaIterator:

• 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)

Parameters

The internal Java data types and the database SQL types are not identical. Therefore, casts must be done for the input and output data:

The value null is the equivalent of the SQL NULL.

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

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, ctx.getString(0) for the first parameter. The number of parameters and their data types, which are both determined during the call of the script, are part of the metadata.

Metadata

You can access the following metadata through the object ExaMetadata.

Exceptions

The following Exasol-specific exceptions can be thrown:

• ExaCompilationException
• ExaDataTypeException
• ExaIterationException

Import of other scripts

Besides importScript() method of class ExaMetadata, other scripts can be imported using the keyword %import. After that, the script is 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();

Access connection definitions

The data that has been specified when defining connections with CREATE CONNECTION is available in Java UDF scripts through method ExaMetadata.getConnection(String connectionName). The result is a Java object that implements the Interface com.exasol.ExaConnectionInformation which features the following methods:

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

Example

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, the %jvmoption keyword can be used to specify the following Java VM options:

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

Example, %jvmoption -Xms128m -Xmx1024m -Xss512k;

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

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

Dynamic output parameters callback function

If the UDF script was defined with dynamic output parameters and the output parameters can not be determined (by specifying EMITS in the query or through INSERT INTO SELECT), the database calls the method public static String getDefaultOutputColumns(ExaMetadata exa) which you can implement. The expected return value is a String with the names and types of the output columns, for example, "a int, b varchar(100)". See Dynamic input and output parameters for an explanation when this method is called including examples.

The method will be executed only once on a single node.

This function should be implemented within the script's main class.

User Defined Import Callback Function

To support a user defined import you can implement the callback method public static String generateSqlForImportSpec(ExaMetadata meta, ExaImportSpecification importSpec). See Dynamic input and output parameters and the IMPORT for the syntax. The parameter importSpec contains all information about the executed IMPORT FROM SCRIPT statement. The function has to generate and return a SELECT statement which will retrieve the data to be imported.

importSpec is an object with the following fields:

User Defined Export Callback Function

To support a user defined export, you can implement the callback method public static String generateSqlForExportSpec(ExaMetadata meta, ExaExportSpecification export_spec). See Dynamic input and output parameters and the EXPORT for the syntax. The parameter export_spec contains all information about the executed EXPORT INTO SCRIPT statement. The function has to generate and return a SELECT statement which will generate the data to be exported. The FROM part of that string can be a dummy table (DUAL) since the export command is aware which table should be exported. But it has to be specified to be able to compile the SQL string successfully.

export_spec is an object with the following fields:

Example

/*
    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;