Java language simple table function

The code in this example creates a function that splits a string into multiple strings based on the delimiter. This example may return more rows of output than input. Because of this, it cannot use the handler model and requires additional error handling logic. Note the try catch block and the loop logic in run. Also note that code must be supplied to retrieve the record objects, output the object, and delete the record objects.

import org.netezza.ae.*;
import java.util.concurrent.Executor;
import java.util.concurrent.Executors;
public class TestJavaSplit {
private static final Executor exec =
Executors.newCachedThreadPool();
public static final void main(String [] args) {
try {
mainImpl(args);
} catch (Throwable t) {
System.err.println(t.toString());
NzaeUtil.logException(t, "main");
}
}
public static final void mainImpl(String [] args) {
NzaeApiGenerator helper = new NzaeApiGenerator();
while (true) {
final NzaeApi api = helper.getApi(NzaeApi.FUNCTION);
if (api.apiType == NzaeApi.FUNCTION) {
if (!helper.isRemote()) {
run(api.aeFunction);
break;
} else {
Runnable task = new Runnable() {
public void run() {
try {
TestJavaSplit.run(api.aeFunction);
} finally {
api.aeFunction.close();
}
}
};
exec.execute(task);
}
}
}
helper.close();
}
public static int run(Nzae ae) {
try {
int ret =runReal(ae);
ae.done();
return ret;
}
catch (NzaeException ex) {
ae.userError(ex.getMessage());
ae.done();
throw ex;
}
catch (Throwable ex) {
ae.userError(ex.getMessage());
ae.done();
throw new NzaeException(ex.getMessage());
}
}
public static int runReal(Nzae ae) {
final NzaeMetadata meta = ae.getMetadata();
if (meta.getOutputColumnCount() != 1 ||
meta.getOutputNzType(0) != NzaeDataTypes.NZUDSUDX_VARIABLE
) {
throw new NzaeException("expecting one output columns of type "+
"string");
}
if (meta.getInputColumnCount() != 1) {
throw new NzaeException("expecting one input column");
}
if (meta.getInputNzType(0) != NzaeDataTypes.NZUDSUDX_FIXED &&
meta.getInputNzType(0) != NzaeDataTypes.NZUDSUDX_VARIABLE) {
throw new NzaeException("first input column expected to be a "+
"string type");
}
for (;;) {
NzaeRecord input = ae.next();
if (input == null)
break;
NzaeRecord output = ae.createOutputRecord();
String field = (String) input.getField(0);
if (field == null)
throw new NzaeException("first input column may not be null");
String[] words = field.split(",");
for (int i=0; i < words.length; i++) {
output.setField(0, words[i]);
ae.outputResult(output);
}
input = null;
output = null;
}
return 0;
}
}

Use the standard compile

$NZ_EXPORT_DIR/ae/utilities/bin/compile_ae --language java \
--template compile TestJavaSplit.java --version 3

Register the newly created file:

$NZ_EXPORT_DIR/ae/utilities/bin/register_ae --sig "splitae(VARCHAR(ANY))" \
--return "table(val varchar(2000))" --class AeUdtf --language java \
--template udtf --version 3 --define "java_class=TestJavaSplit"

Run the query in nzsql:

SELECT * FROM TABLE WITH FINAL(splitae('12:30:15'));
VAL
----------
12:30:15
(1 row)
SELECT * FROM TABLE WITH FINAL(splitae('12:30:15,two,three'));
VAL
----------
12:30:15
two
three
(3 rows)
SELECT * FROM TABLE WITH FINAL(splitae(NULL));
ERROR: first input COLUMN may NOT be NULL