Page History

Contents

Use this page to share code that you have developed for Trafodion and that could be useful for others. This could include, but is not restricted to:

...

The source code can be downloaded from: https://github.com/traflm/trafodion-repos/blob/master/scalar-udf/to_date.c

Table-Mapping UDFs

Execution model of a Table-Mapping UDF (TMUDF).  Syntax for invoking a TMUDF looks like this...

udf( <udf name> ( TABLE ( <select stmt> ) [, <parameters>] ) )

Result rows from <select stmt> will be presented to UDF and user UDF logic decides what to do with it.  

Prior to beginning execution of <select stmt> query, user UDF logic

• examines the columns named in the select-list of <select stmt> and any <parameters> passed in the UDF call
• produces the list of output columns - this may/may not include items from the select-list of <select stmt>; the UDF can add new columns 
• examines predicates and partitioning clauses in <select stmt> (as seen in the Sessionize TMUDF tutorial)
• influences statistics (as seen in Sessionize)

When <select stmt> executes. result rows are returned to the UDF for action.  The UDF consumes each row and, according to its logic, outputs a row corresponding to the output columns that were defined in the previous step.

Reading Kafka Events

This UDF can read a topic from Apache Kafka. It is a basic serial Kafka consumer.

...

The java source can be copied from: https://github.com/esgyn/code-examples/blobtree/master/src/main/java/org/trafodion/examples/udrs/udfs/table_valued/json_columnizer.

Trafodion steps after java compiling, jar'ing, and uploading to every node of the Trafodion cluser:

...

create library <your library name> file '<the full path name of your jar>';

create table_mapping function <what you want> ()
external name '<your class>'                       org.trafodion.examples.udrs.udfs.table_valued.json_columnizer.json_columnizer' -- name of your class
language java
library <your library name>;

FilterProg

This is a UDF to start an arbitrary executable, send it the table-valued input of the UDF (if any) as input, and convert the standard output of the program into the result table of the UDF.

Since we don't know what data the program generates, number and types of the result columns needs to be specified as arguments.

NOTE: This UDF could be a potential security vulnerability, if not set up carefully. It restricts the executables to a "sandbox" directory, which should be carefully managed by the person who creates the UDF.

See more comments in the source file. 

The source code can be downloaded from https://github.com/esgyn/code-examples/tree/master/src/main/cpp/FilterProg.

Text Search using Lucene

Lucene is an Apache project written in Java that provides text indexing and searching capabilities.  In terms of function, the analogy for this particular use case is SQL's "LIKE" predicate (but Lucene offers more capabilities).  This TMUDF shows the basics of how to use those APIs.  A typical text index/search scenario involves many "documents" with a search result consisting of documents that match the search query.  For this UDF, indexing and matching is made row-by-row, that is, the index represents just the current row.  Also, an in-memory directory class is used rather than one on disk.

This example was developed with Lucene 5.5.2, a version using Java 7.  Later Lucene releases (> 6.x) use Java 8 and beyond.

The java source can be copied from:  https://github.com/esgyn/code-examples/tree/master/src/main/java/org/trafodion/examples/udrs/udfs/table_valued/lucene.

Manageability

Tools

SQL Scripts and Queries

Miscellaneous