Creating a Table Java User Defined Function using 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 a Table Java User Defined Function using the Teradata Plug-in for Eclipse

Teradata 13.0 supports Table Java User Defined Functions (JUDF). A Table Java User Defined Function returns a table of data a row at a time in a loop to the caller of the function. This article will show you how to use the Teradata Plug-in for Eclipse to quickly create a Table Java User Defined Function.

Prerequisite

If you have not worked through the guide Getting Started with Teradata Plug-in for Eclipse, do so now before you continue.

Create a Table JUDF

In this example, a Table JUDF takes in raw data for sales personnel for a sales region and returns a table of sales people based on a region.

Teradata Project

Create a Teradata Project as described in the article Create a Teradata Project using the Teradata Plug-in for Eclipse. You will need a Teradata Project for the source code and properties file location for your Java User Defined Function. The Teradata Project also 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 table JUDF.

Launch Wizard

Once you have created your Teradata Project, you will need to launch the Teradata JUDF Wizard.  In Eclipse, go to the top menu bar and select the menu option Window-> Open Perspective -> Other.  The Open Perspective pop up dialog will come up. Select the Database Development perspective.  Now open the connection for the database where you wish to create your table JUDF in the Data Source Explorer (DSE). Expand the DSE tree to the schema where you want your JUDF. Select the User-Defined Function DSE Tree Node and right click.  An option menu will come up. Select the 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 "SalesPeople". 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 “SalesPeople”. It is seen as best practice not to use the default package name. 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 as shown in Appendix A.

Now, enter the method name “getSalesPeople”. Next go to the "New" button for the parameter list and then add the parameter name “region”. Pick the data type INTEGER.

Now select the "New" button for the parameter list again and then add the parameter name “rawData”.  Pick the data type VARCHAR. Enter in the size of 256 and hit 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 Table option from the Return Type Combo box, the table column specification panel will appear. The panel is similar in usage as the parameter panel in the Define JUDF Method Wizard Page. You now will be able to define table columns from this panel.  Add the following columns to your return table using the columns panel:

id  INTEGER

salesRegion INTEGER

name VACHAR(64)

colType VARCHAR(64)

You will also have the option to define a context class and a table phase code template to be generated with the JUDF. The context class option specifies that a context class should be generated with the Java User Defined Function. The context class is used to store context information needed to create output rows between calls to the Java User Defined function. The code template option specifies that a case statement is to be generated in the Java User Defined Function. The phase code case statement manages the life cycle of the call to the Java User Defined Function. These options will be on by 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 Table JUDF.

Source Page

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

The table phase code case statement will be generated with the Table 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 table rows being returned.  First you will want a helper class to process the raw data.

Create a class using the following sample code in your Teradata Project.

package util;

import java.sql.SQLException;

public class GetData {
public static String[] getRows(String rawData) throws SQLException {
if (rawData == null) {
throw new SQLException("Error proccessing data ", "38T09");
} else if (rawData.length() == 0) {
throw new SQLException("Error proccessing data", "38T09");
}
String[] returnValue = rawData.split(",");

return returnValue;
}

public static String[] getColumns(String rawData) throws SQLException {
if (rawData == null) {
throw new SQLException("Error proccessing data ", "38T09");
} else if (rawData.length() == 0) {
throw new SQLException("Error proccessing data", "38T09");
}

String[] returnValue = rawData.split(":");

return returnValue;
}
}

Next replace the context class with the following code:

class GenCtx implements Serializable {

private int region;
private String[] rows;
int count = 0;

public GenCtx() {

}

public GenCtx(int maxRows, int MaxRowSize) {
rows = new String[maxRows];
for (int i = 0; i < maxRows; i++) {
rows[i] = new String(new char[100]);
}
}

/**
* Get Context Class member region
*
* @return region
*/
public int getRegion() {
return this.region;
}

/**
* Set Context class member region
*
* @param region
*/
public void setRegion(int region) {
this.region = region;
}

/**
* Set Context class member rawData
*
* @param rawData
* @throws SQLException
*/
public void setRawData(java.lang.String rawData)
throws SQLException {
rows = GetData.getRows(rawData);
count = rows.length;
}

/**
* Get Context Class member rawData
*
* @return rawData
*/
public java.lang.String[] getColumns() throws SQLException {
count--;
if (count < 0) {
throw new SQLException("EOF", "02000");
}
return GetData.getColumns(this.rows[count]);
}
}

The new context class will use the GetData class to process data. The context class now has context information needed to create output rows for the Table JUDF.

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

int[] phase = new int[1];
Tbl tbl = new Tbl();
GenCtx obj = null;
try {

switch (tbl.getPhase(phase)) {

case Tbl.TBL_MODE_CONST:

switch (phase[0]) {
case Tbl.TBL_PRE_INIT:

if (tbl.firstParticipant()) {
/* participant */
return;
} else {
/* don't participate */
if (!tbl.optOut()) {
throw new SQLException("Opt-out failed.", "38T06");
}
return;
}
case Tbl.TBL_INIT:
obj = new GenCtx();
obj.setRegion(region);
obj.setRawData(rawData);
tbl.allocCtx(obj);
tbl.setCtxObject(obj);

break;
case Tbl.TBL_BUILD:

obj = (GenCtx) tbl.getCtxObject();
String[] columns = obj.getColumns();
try {
id[0] = Integer.parseInt(columns[0]);
} catch (Exception e) {
throw new SQLException("Error proccessing data ",
"38T09");
}
saleRegion[0] = obj.getRegion();
name[0] = columns[1];
colType[0] = "Const";
tbl.setCtxObject(obj);

break;
case Tbl.TBL_END:
// TODO Put in ending logic
break;
}
break;
/**********************************/
/* Process the varying expression */
/**********************************/
case Tbl.TBL_MODE_VARY:
switch (phase[0]) {
case Tbl.TBL_PRE_INIT:
obj = new GenCtx(10, 100);
tbl.allocCtx(obj);
tbl.setCtxObject(obj);
break;
case Tbl.TBL_INIT:
obj = (GenCtx) tbl.getCtxObject();
obj.setRegion(region);
obj.setRawData(rawData);
tbl.setCtxObject(obj);
break;
case Tbl.TBL_BUILD:

obj = (GenCtx) tbl.getCtxObject();
String[] columns = obj.getColumns();
try {
id[0] = Integer.parseInt(columns[0]);
} catch (Exception e) {
throw new SQLException("Error proccessing data ",
"38T09");
}
saleRegion[0] = obj.getRegion();
name[0] = columns[1];
colType[0] = "Vary";
tbl.setCtxObject(obj);

break;
case Tbl.TBL_FINI:
// TODO Finish logic
break;
case Tbl.TBL_END:
// TODO Ending logic

break;
case Tbl.TBL_ABORT:
// TODO Abort logic
break;
}
return;

}

} catch (IOException eio) {
throw new SQLException("IO Exception", "U0002");
} catch (ClassNotFoundException e) {
throw new SQLException("ClassNotFoundException", "U0003");
}

The phase code case statement uses the class com.teradata.fnc.Tbl from the javFnc JAR in the Teradata Project. You allocate and set the context in the Tbl class. You manage the life cycle of your JUDF by accessing the phases that are stored in the Tbl class. For instance, the Tbl.TBL_INIT phase is where you allocate the memory and initialize the context class.  The Tbl.TBL_BUILD phase is where you create the table rows to be returned by the JUDF.

The Table JUDF can handle two types of modes. The first case is the constant mode. This is where data being passed to the JUDF is a literal string.

The second case is table mode varying.  This is where the data passed into the JUDF is from a query.

JAR Files

Once you are done editing your Table JUDF, select the JAR files tab. A prompt will come up asking if you want to save the changes to your JUDF. Respond yes to the prompt.  In the JAR file tab, add the classes file GetData, in the Additional Classes list box.

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 table JUDF DDL on your target database.

Run Table JUDF

As mentioned earlier, there are two types of modes for Table JUDFs, constant and varying. To run the Table JUDF mode in the constant mode enter the following SQL in the DTP SQL editor:

select  * from  Table (getSalesPeople(1,'6:John Smith,2:Mark Bishop,3:Jack Brown')) as t1;

Once this is done, right click in the SQL editor and select the Execute All menu option. The results will show up in the SQL Results tab in the Eclipse IDE.

To run the Table JUDF in varying mode, you will first need a table of data. Enter the following SQL in a new SQL editor window.

CREATE SET TABLE raw_sales_people, NO FALLBACK,      
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
region INTEGER,
pending_data VARCHAR(1024) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX (region);

INSERT INTO raw_sales_people
VALUES(2,'1:Joe bishop,2:Mark Bishop');
INSERT INTO raw_sales_people
VALUES(1,'3:Jack Brown,4:Mark Brown,5:Harry Brown');
INSERT INTO raw_sales_people
VALUES(1,'6:John Smith,7:Sara Smith,9:Mathew Smith,10:Peter Smith"');

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 DISTINCT 
sales_people.id,
sales_people.name,
sales_people.colType,
sales_people.saleRegion
FROM raw_sales_people,
TABLE (
getSalesPeople(
raw_sales_people.region,
raw_sales_people.pending_data)) AS sales_people
WHERE sales_people.saleRegion = 1;

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.

For more information on how to use javaFnc with Table JUDFs please refer to the online publication "SQL External Routine Programming" Release 13.0 (B035-1147-098A).

The next article in this series will show you how to create an Aggregate JUDF with the JUDF Wizard and Multi-page Editor.

Appendix A

SQL Data Types Mapping

The following table defines how data is mapped between SQL and Java.  The SQL data type is converted to/from the corresponding Java data type based on the type of parameter mapping. The JUDF defaults to simple mapping. The user may specify object mapping via the External Name clause.

SQL data type

Java data type

Simple map (Java primitive symbol)

Object map

CHARACTER

N/A

Java.lang.String

VARCHAR

 N/A

Java.lang.String

NUMERIC

 N/A

Java.math.BigDecimal

DECIMAL

N/A

Java.math.BigDecimal

BIGINT

 long(J)

Java.lang.Long

SMALLINT

short (S)

Java.lang.Short

INTEGER

int (I)

Java.lang.Integer

REAL

double (D)*

Java.lang .Double

FLOAT

double (D)*

Java.lang .Double

DOUBLE PRECISION

double (D)

Java.lang .Double

BYTE

 byte[] ([B)

N/A

VARBYTE

 byte[] ([B)

N/A

DATE

 N/A

Java.sql.Date

TIME

N/A

Java.sql.Time*

TIMESTAMP

N/A

Java.sql.Timestamp

BYTEINT

byte (B)

Java.lang.Byte

CLOB

 N/A

Java.sql.Clob

BLOB

N/A

Java.sql.Blob

VARGRAPHIC

 N/A

N/A

GRAPHIC

 N/A

N/A

INTERVAL

N/A

Java.lang.String

PERIOD**

N/A

N/A

UDT**

N/A

N/A

9 REPLIES
Enthusiast

Re: Creating a Table Java User Defined Function using the Teradata Plug-in for Eclipse

I cannot see the option of "Teradata -> Create Java User Defined Function" in the DSE tree.
Can anyone let me know what wrong am i doing.?
I have imported external libray javfnc.jar in my project ..
Teradata Employee

Re: Creating a Table Java User Defined Function using the Teradata Plug-in for Eclipse

First check to see if you are running the 13.0 version of the Teradata database, it is required for this feature. If you are still having problems, this usually indicates an installation problem with Teradata Plug-in for Eclipse. You can follow the instructions in the Article "Getting Started with Teradata Plug-n for Eclipse" which should help you with any installation issues. http://developer.teradata.com/tools/articles/getting-started-with-teradata-plug-in-for-eclipse
Teradata Employee

Re: Creating a Table Java User Defined Function using the Teradata Plug-in for Eclipse

Is it possible to run an sql statement in a Java UDF? I am particularly interested in select statements and stored procedure calls that would return a result set.
Teradata Employee

Re: Creating a Table Java User Defined Function using the Teradata Plug-in for Eclipse

It is not allowed to run a SQL statement in a Java UDF.

Re: Creating a Table Java User Defined Function using the Teradata Plug-in for Eclipse

When I click on "Create Java User Defined Function", I get an error alert saying "User does not have permission to access SQLJ on the current database". Teradata database version is 13.10.0.05. How do I resolve this issue?
Teradata Employee

Re: Creating a Table Java User Defined Function using the Teradata Plug-in for Eclipse

See the example in the appendix for the article titled “Hadoop DFS to Teradata.” (http://developer.teradata.com/extensibility/articles/hadoop-dfs-to-teradata). It shows you how to grant permissions to SQLJ.
Teradata Employee

Re: Creating a Table Java User Defined Function using the Teradata Plug-in for Eclipse

Is is possible to write a table function that pulls data from a remote Teradata system to combine it with data in a local Teradata table?
Teradata Employee

Re: Creating a Table Java User Defined Function using the Teradata Plug-in for Eclipse

You cannot run SQL in a Java UDF. However, you can with a C UDF but it may have performance issues. You can use a stored procedure to merge your local Teradata table with data from a remote Teradata system. Also you can always use Teradata Data Mover to bring data from your remote Teradata system to your local system.
Enthusiast

Re: Creating a Table Java User Defined Function using the Teradata Plug-in for Eclipse

I've created a post on my blog about the permissions issue with SQLJ resulting in the "User does not have permission to access SQLJ on the current database" error:

http://teradatadba.wordpress.com/2013/09/05/sqlj-permissions-for-teradata-java-udf-judf-compilation-...

It's hopefully a bit clearer than trying to figure it out from the earlier link about "Hadoop DFS to Teradata".  I think I've got it right but if there are any issues feel free to post in the comments.