Creating an Aggregate Java User Defined Function with the Teradata Plug-in for Eclipse

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

Creating an Aggregate Java User Defined Function with the Teradata Plug-in for Eclipse

Teradata 13.0 supports a Java User Defined Function (JUDF) capability, which returns an Aggregate. Aggregate functions produce summary results. In the last Article, you were shown how simple it is to create a Table JUDF using Teradata tools. Now, in this article you will be shown how easy it is to create an Aggregate JUDF using the Teradata Plug-in from Eclipse. The Teradata JUDF Wizard and Editor simplify the process of creating, installing and editing an Aggregate JUDF.

Prerequisite

If you have not worked through the article Creating a Table JUDF with the Teradata Plug-in for Eclipse, do so now before you continue.

Create an Aggregate JUDF

In this example, an aggregate JUDF takes a list of prices from a Product database table and returns the average price from that list.

Teradata Project

Re-use the Teradata Project you created in the last article or create a new Teradata Project as described in the article Create a Teradata Project using the Teradata Plug-in for Eclipse. The Teradata Project gives you access to the Teradata Libraries. The Teradata Libraries contains the Java User Defined function utilities JAR (javFnc). You will need the javaFnc.jar to implement your aggregate JUDF.

Launch Wizard

In Eclipse, open the Data Base Perspective.  Now open the connection for the database where you wish to create your aggregate JUDF in the Data Source Explorer (DSE). Expand the DSE tree to the schema where you want your JUDF. Select the User-Defined Function from the DSE Tree Node and right click.  An option menu will come up. Select the menu optionTeradata->Create Java User Defined Function.

JUDF Wizard

At this point, the JUDF Wizard will come up. The first page of the Wizard defines the properties file for the Table JUDF. You will need to enter the container name "/SalesProject" and the name of the JUDF properties file "AveragePrice". Once you have done this, select the “Next” button.

JUDF Class Definition

The next page is where the JUDF class is defined. Enter the source folder “SalesProject/src/java”, package name “sales” and the class name “AveragePrice”. Once this is done hit the “Next” button.

Define JUDF Method

This page allows you to define the function method for the JUDF. SQL data types are used to define the parameter types in this panel. The SQL data types are mapped to specific Java types.

Now, enter the method name “getAveragePrice”. Next go to the "New" button for the parameter list and then add the parameter name “price”. Pick the data type FLOAT and choose the Apply button. Now select the "Next" button.

Define return type Wizard Page

The next page allows you to define the return type for your JUDF. The default for the return type is scalar. Select the Aggregate option from the Return Type Combo box, the aggregate column specification panel will appear. Now select FLOAT as the return type for the aggregate JUDF. You will also have the options to define an aggregate storage class and an aggregate phase code template to be generated with the JUDF.  The aggregate storage option specifies that a storage class should be generated with the Java User Defined Function. The storage class is used to store context information needed to create an aggregate sum for the Java User Defined Function. The aggregate phase code template option specifies that a case statement is to be generated in the Java User Defined Function. The aggregate phase code switch statement manages the life cycle of the call to the Java User Defined Function. These options are selected by default.

UDF Options

Once you have set the return type for your JUDF, hit the next button until you get to the UDF Option Wizard Page. The Options Wizard Page allows the user to specify the DDL options for a JUDF. The Storage Size numeric option allows the user to specify the intermediate storage area for an aggregate function. For this example you can leave the storage size value to the default.

Preview SQL

Now hit the next button until you get to the Preview SQL Wizard Page. Validate the SQL and then select the Finish button.

Using the JUDF Multi-Page Editor

Once the Finish button is selected, the JUDF Multi-Page editor will come up.The JUDF Multi-Page Editor brings up the contents of the JUDF properties defined in the JUDF Wizard. The Multi-Page editor allows you to edit and deploy a JUDF. 

The first page in the editor will show the Class Definition of the aggregate JUDF.

Source Page

Select the source tab of the Multi-page Editor. This will bring up the source page. This editor page will have the generated code for the table JUDF. The storage class will be created if the option was selected in the JUDF Wizard. The storage class will use the JUDF input parameters as class members.

The aggregate phase code case statement will be generated with the Aggregate JUDF if this option was selected in the Wizard. This code is only a template. You will need to add in the logic to create the aggregate sum to return.  First you will want to change your storage class to keep a count and add a method to calculate the average price. Replace the AveStor class with the following code:

/**
* Storage class for AveragePrice Java User Defined Function
*
*
*/

class AveStor implements Serializable {
private double price;
int count = 0;

/**
* Constructor
*
*
*/

public AveStor() {
// TODO Initialize to context object to maximum size
}

/**
* Get storage Class member price
*
* @return price
*/
public double getPrice() {
return this.price;
}

/**
* Set storage class member price
*
* @param price
*/
public void setPrice(double price) {
this.price = price;
}

/**
* @return the count
*/
public int getCount() {
return count;
}

/**
* @param count
* the count to set
*/
public void setCount(int count) {
this.count = count;
}

/**
*
* @return average
*/
public double getAverage() {
return (price / ((double) count));

}
}

Now you will need to update the phase code switch statement to use the new storage class. Replace the phase code switch statement with the following code:

try {

AveStor s1 = null;

/*
* AGR_DETAIL, AGR_COMBINE, and AGR_FINAL phases use the value from
* the aggregate storage area.
*/
if (phase.getPhase() > Phase.AGR_INIT
&& phase.getPhase() < Phase.AGR_NODATA) {

s1 = (AveStor) context[0].getObject(1);

}
switch (phase.getPhase()) {
/* The AGR_INIT phase is executed once. */
case Phase.AGR_INIT:

s1 = new AveStor();

/* Fall through to detail phase also. */
case Phase.AGR_DETAIL:
// Perform SUM
double total = s1.getPrice() + price;
s1.setPrice(total);
int countTotal = s1.getCount() + 1;
s1.setCount(countTotal);
break;
case Phase.AGR_COMBINE:

AveStor s2 = (AveStor) context[0].getObject(2);

// TODO SUM between AMPs.
double totalCombine = s1.getPrice() + s2.getPrice();
s1.setPrice(totalCombine);
int countTotalCombine = s1.getCount() + s2.getCount();
s1.setCount(countTotalCombine);
break;
case Phase.AGR_FINAL:
// Final, return SUM.

return s1.getAverage();
case Phase.AGR_NODATA:
/* Not expecting no data. */
return -1;
default:
throw new SQLException("Invalid Phase", "38U05");
}

/* Save the intermediate SUM in the aggregate storage. */
context[0].setObject(1, s1);

} catch (Exception ex) {
throw new SQLException(ex.getMessage(), "38101");
}
return returnValue;
}

The phase code case statement uses the class com.teradata.fnc.Phase from the javFnc JAR in the Teradata Project. You allocate and set the context in this Phase class. You manage the life cycle of your JUDF by accessing the phases that are stored in the Phase class. For instance, the Phase.AGR_INIT phase is where you allocate the memory for your storage class.  The Phase.AGR_DETAIL phase is where you sum values for the aggregate JUDF.

JAR Files

Once you are done editing your aggregate JUDF, select the JAR files tab. A prompt will come up asking if you want to save the changes to your JUDF. Now select the deploy button. This will create a JAR for your JUDF and deploy it on the target database.

SQL

The next step is to run the SQL to create the DDL for your table JUDF. Select the SQL tab. Now, select the RUN SQL button on the SQL editor page. This will install the aggregate JUDF DDL on your target database.

Execute Aggregate JUDF

To run your aggregate JUDF, you will first need a database table of data. Enter the following SQL in a new DTP SQL editor window.

CREATE TABLE products (
id INTEGER NOT NULL PRIMARY KEY,
description varchar(255),
price decimal(15,2)
);

INSERT INTO products (id, description, price) values(1, 'Lamp', 25.0);
INSERT INTO products (id, description, price) values(2, 'Table', 50.0);
INSERT INTO products (id, description, price) values(3, 'Chair', 15.0);

Now right click in the SQL editor and select the menu option Execute all as Individual Statements.

Once you created and populated your table with data, enter the following SQL in the SQL editor.

select getAveragePrice(price) from products; 

Now select the new SQL in the editor and right click and choose the menu option Execute Selected Text.

The results will show up in the Results tab in the Eclipse IDE.

The Teradata Plug-in has facilitated the implementation of your aggregate JUDF by creating and managing the associated source, JAR and DDL for your JUDF. This example has shown that Teradata JUDF Wizard and Editor takes the busy work out of creating an Aggregate JUDF. This will  give you more time implementing the business logic for your JUDF. For more information on how to create aggregate JUDFs please refer to the online publication "SQL External Routine Programming" Release 13.0 (B035-1147-098A).

2 REPLIES
Enthusiast

Re: Creating an Aggregate Java User Defined Function with the Teradata Plug-in for Eclipse

is there any documentation that I can check to find the different phase code and their definitions?
Teradata Employee

Re: Creating an Aggregate Java User Defined Function with the Teradata Plug-in for Eclipse

Please refer to the online publication "SQL External Routine Programming" Release 13.0 (B035-1147-098A).