Teiid 9.1.2 - Create an UDAF (Teiid User Defined Aggregate Support)

Here are the steps necessary to create an UDAF in Teiid 9.1.2.

Create UDAF

pom.xml

Add this to pom (probably you have to add the jar yourself to your repository. Look for the jar in /modules/system/layers/dv/org/jboss/teiid/api/main/teiid-api-9.1.2.jar. See http://javari.blogspot.pt/2009/10/instalar-um-jar-no-repositorio-maven.html):

<dependencies>
    <dependency>
        <groupId>org.jboss.teiid</groupId>
        <artifactId>teiid-api</artifactId>
        <version>9.1.2</version>
    </dependency>
</dependencies>

Implement UDAF

package my.udaf;

import org.teiid.CommandContext;
import org.teiid.UserDefinedAggregate;


public class MyAggregateFunction implements UserDefinedAggregate{


  public MyAggregateFunction(){

    reset();
  }

  public void addInput(Long val, char separator){

    // Use val (in this case Long) in someway. Example: add it to a list.
  }

  @Override  public void reset(){

    group = new ArrayList<>();
    separator = null;
  }

  @Override  public String getResult(CommandContext commandContext){
    

    // do your work. Use all inputs and return them as you want.
  }
}

Create Artifact

mvn clean install

Create Module

Here are the steps necessary to create a module in Teiid 9.1.2:

Run the following in the server console (in /bin):

./jboss-cli.sh --connect

and run the following commands:

module add --name=module_name --resources=udaf-1.0.jar --dependencies=org.jboss.teiid.api

:reload

Where:

• module_name  - Name of th module. We will use this name in vdb's;
• udaf-1.0.jar - Jar being added
• org.jboss.teiid.api - The udaf jar depends of this module.

Use UDAF in a VDB

<vdb name="MyVdb" version="1">
<description>My VDB</description>


<property name ="lib" value ="module_name "></property>

           <model name="MyAggregateFunction" type="VIRTUAL">
         <metadata type="DDL"><![CDATA[

CREATE VIRTUAL FUNCTION MyAggregateFunction(val long, separator char) RETURNS string OPTIONS (JAVA_CLASS 'my.udaf.MyAggregateFunction', JAVA_METHOD 'addInput', AGGREGATE 'true', "NULL-ON-NULL" 'true', "ALLOWS-DISTINCT" 'true');

]]>
</metadata>
    </model>

   

   

...

<model name="CountryServiceList" type="VIRTUAL"> <metadata type="DDL"><![CDATA[ CREATE VIEW my_view ( column_1 varchar(10), column_2 varchar(10), column_3 varchar(100), ) AS SELECT MyAggregateFunction(DISTINCT column_1, ',') as column_1, column_2 , column_3 FROM my_table GROUP BY column_1 , column_2 ]]> </metadata> </model>