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

A Teradata Database Java User Defined Function (JUDF) is a program that operates on data stored in relational tables. UDFs allow users to add their own extensions to the Teradata SQL language. JUDFs are implemented as external functions. This means the source is compiled externally to the DBS and kept in Java Archive (JAR) files. These JAR files are installed into the database using stored procedures in the SQLJ database. Once a JAR file is installed a JUDF can be defined to use a Java class and method within the JAR file. The JUDF is executed via a protected mode server separate from the database process. Parameters passed from the DBS are converted to their Java form and the Java return type from the JUDF  is converted back to its DBS form.

A JUDF is referred to as a function in the SQL-99 standard. It provides a rich object oriented feature set including networking. It is also platform independent which allows code developed on one platform to be easily moved to another. Java objects lend themselves to be developed as components for business logic. Applications written in Java and User Defined Functions can be written in the same style and language.

This article will show how the Teradata Plug-in for Eclipse facilitates the process of creating and installing the Java parts and DDL for a scalar JUDF using the JUDF Wizard and Multi-page Editor.

Prerequisite for this Article

If you have not work through the guide Getting Started with Teradata Plug-in for Eclipse, do so now before you continue.  Also the 13.0 version of the Teradata database is required for this feature.

Using JUDF Wizard

In this example, you will be creating a JUDF Function which takes a full name as a parameter and returns the last name from the name passed into the function. You will use the JUDF Wizard to implement this JUDF.

Create a Java Project

You will need to create a Java project to store the Java source of the JUDF. Go to the top of the Eclipse IDE and select the pull down menu Window ->Perspective->Other... ->Java (Default). Then go to the package explorer in Eclipse and right click New->Java Project. Enter “teradata_judf” for the Java project name. Once this is done, select the "Next" button.

Remove “src” Folder

Remove your default source folder by selecting “src” and then picking the "Remove source folder" option.

Add “src/java” Folder

In this example, the “src” folder is going to be deleted and the “src/java” folder will be added. This is done to give the Java project structure. Later in following articles different source folders will be added such as “src/config” and “src/test”.

Now select the “Create a new source folder” option and enter “src/java” in for the folder name and select “Finish”. 

Now select “Finish” again in the New Project Wizard.

Launch JUDF Wizard

Go to the top of the Eclipse IDE and select the pull down menu Window ->Perspective->Other…->Database Development. Select the database you created a profile for in the Data Source Explorer. Right click on the database node and select connect. Open the tree in the Explorer and select the “User Defined Function” tree node for the schema in which you wish to create your JUDF. Right click on the “User Defined Function” tree node and select the menu item Teradata->Create Java User Defined Function.

JUDF Wizard

At this point, the JUDF Wizard will come up. You will need to enter the container name "/teradata_judf" and the name of the JUDF properties file "ReturnLastName". 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 “teradata_judf/src/java”, package name “judf” and the class name “ReturnLastName”. It is the best practice not to use the default package name. Once this is done hit the “Next” button.

Define JUDF Method

This page allows the user 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 “returnLastName”. Next go to the "New" button for the parameter list and then add the parameter name “name”. Pick the data type VARCHAR. Enter in the size of 256 and hit the "Apply" button. Now select the "Next" button.

Define Return Type

The Next page of the Wizard defines the return type for the JUDF. The return types use SQL data types. The SQL data types are mapped the same as the JUDF parameters to specific Java types as shown in Appendix A.

Now select the VARCHAR from the “Type” combo box. Then enter the size of 256 for the type option. Once this is done, select the “Finish” button.

 

Using the JUDF Multi-Page Editor

The JUDF Multi-Page Editor brings up the contents of the JUDF properties defined in the JUDF Wizard. The Multi-Page editor allows the user to edit and deploy a JUDF.

JUDF Function Source

Go to the “Source” page of the editor. You will see the generated Java code for the JUDF. In this case, it is up to you to enter the content of the JUDF.

Change Source

Edit the Java source on this page entering the code shown below to get the last name from a full name.

Once this is done, go to the “JAR File” page in the editor. A popup dialog will come up asking you if you want to save the source you just changed. Now select the “Yes” button.

Deploy JAR

On the JAR Files page, you will see the JAR file and JAR ID for the JUDF. Select the “Deploy” button. This will deploy the JAR for the JUDF on the database server.

Install DDL

Go to the “SQL” page in the editor. Select the “Run SQL” button. This will install your JUDF on the database server.

Run JUDF

Once your JUDF is installed you can run it. Go to the DTP tree in the Data Source Explorer. Select the User Defined Function tree node in which you launched the Wizard and select refresh. You will now see the “returnLastName” function. Select the procedure and right click. Now select the "Run" menu item. A popup dialog will come up called "Configure Parameters". Enter in the value column of the Dialog “John Smith” and hit the “OK” button.

Results

Your results of running your JUDF will end up in the bottom of the Eclipse IDE. Select the "Results1" tab and see the results of the execution of your JUDF.

Conclusion

This article has shown how Teradata Plug-in for Eclipse facilitates the process of creating and installing the Java parts and DDL for a JUDF using the JUDF Wizard and Multi-page Editor. Using these tools, will help the user create, edit and run JUDFs easily and efficiently.

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.

 

Appendix B

 JUDF Example Source Code

/**
*
*/
package judf;

/**
* @author js185064
*
*/
public class ReturnLastName {

/**
* @param name
* @return
*/
public static String returnLastName(String name) {

String returnValue = null;
if (name != null) {
int index = name.indexOf(" ");
if (index == -1) {
returnValue = name;
} else {
returnValue = name.substring(index, name.length());
}
}
return returnValue;
}
}
27 REPLIES

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

when I right click on [User Defined Functions --> Teradata] I don't get the option to "Create Java User Defined Function".
Teradata Employee

Re: Creating a Simple 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

Enthusiast

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

Thanks for the great post. I installed Teradata 13 and got it working with my first JUDF.

I have the following concerns:
1) how java udf will perform - is it all AMP parallel execution when i select from table? if am calling an out of the box sql function vs java function (say subString), what would be the difference in performance ?
2) can i do I/O in java method ? can i make a web-service call in java method ? can i make a jdbc data connection in java method before returning the response.

Thanks
Anish
Teradata Employee

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

A Java UDF execution is in parallel. The performance because the JVM is in a separate process is considered about two times slower than a C UDF. C UDFs are slower than an intrinsic function like TRIM by a fair amount. A Java UDF trim maybe four times slower than an intrinsic function. You can do I/O in the Java UDF method if the tdat user on the box has the correct permissions and the JVM is configured for that kind of access. Just keep in mind that you have multiple threads doing the I/O and that each node has its own file system. You could also use a Web service to obtain some external data, but if you had 1000 amps all of them could invoke the Web service at once. A better strategy would be to invoke the Web service once either from the application layer that then invokes the JUDF or from a Stored Procedure which again can then invoke the JUDF.
Enthusiast

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

When I want to run the jar, I get the following error:

-----

Executed as Single statement. Failed [7583 : HY000] The secure mode processes had a set up error.
Elapsed time = 00:00:07.242

STATEMENT 1: Select Statement failed.

-----

with the VMWare version of Teradata Express (Linux); using Eclipse EE under Windows.

Could you please tell me what I have to do to make it run?
Teradata Employee

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

The following might be causing the problem:
/etc/passwd - Reason for failure

tdatuser:x:0:1000:UDF user:/home/tdatuser:/bin/bash

The above setting is making the 'tdatuser' as 'root' user and preventing UDF secure server tasks invocation.The UID should be other than ' 0' on all nodes
Enthusiast

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

I changed the line
tdatuser:x:1000:1000:UDF user:/home/tdatuser:/bin/bash
to
tdatuser:x:0:1000:UDF user:/home/tdatuser:/bin/bash

but the problem remains.
Teradata Employee

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

You misunderstood me, the UID should be something other than ' 0'. It looks like that was already the case.
Highlighted
Teradata Employee

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

Make sure you comple your project with JDK 1.5.