JBoss.orgCommunity Documentation
If you need to extends Teiid's scalar function library, then Teiid provides a means to define custom scalar functions or User Defined Functions(UDF). The following steps need to be taken in creating a UDF.
A {FunctionDefinition}.xmi file provides metadata to the query engine on User Defined Functions. See the Designer Documentation for more on creating a Function Definition Model.
The following are used to define a UDF.
Function Name When you create the function name, keep these requirements in mind:
You cannot overload existing Teiid System functions.
The function name must be unique among user-defined functions in its model for the number of arguments. You can use the same function name for different numbers of types of arguments. Hence, you can overload your user-defined functions.
The function name cannot contain the '.' character.
The function name cannot exceed 255 characters.
Input Parameters - defines a type specific signature list. All arguments are considered required.
Return Type - the expected type of the returned scalar value.
Pushdown - can be one of REQUIRED, NEVER, ALLOWED. Indicates the expected pushdown behavior. If NEVER or ALLOWED are specified then a Java implementation of the function should be supplied. If REQUIRED is used, then user must extend the Translator for the source and add this function to its pushdown function library.
invocationClass/invocationMethod - optional properties indicating the static method to invoke when the UDF is not pushed down.
Deterministic - if the method will always return the same result for the same input parameters.
Even pushdown required functions need to be added as a UDF to allow Teiid to properly parse and resolve the function. Pushdown scalar functions differ from normal user-defined functions in that no code is provided for evaluation in the engine. An exception will be raised if a pushdown required function cannot be evaluated by the appropriate source.
Currently there is no provision to add UDF when you are working with the Dynamic VDBs. However, you can extend the Translator to define source pushdown functions.
While Teiid provides an extensive scalar function library, it contains only those functions that can be evaluated within the query engine. In many circumstances, especially for performance, a user defined function allows for calling a source specific function.
For example, suppose you want to use the Oracle-specific functions score and contains:
SELECT score(1), ID, FREEDATA FROM Docs WHERE contains(freedata, 'nick', 1) > 0
The
score
and
contains
functions are not part of built-in scalar function library. While
you could write your own custom scalar function to mimic their
behavior, it's more likely that you would want to use the actual
Oracle functions that are provided by Oracle when using the Oracle
Free Text functionality.
In addition to the normal steps outlined in the section to create and install a function model (FunctionDefinitions.xmi), you will need to extend the appropriate connector(s).
For example, to extend the Oracle Connector
Required
- extend the OracleExecutionFactory and add SCORE and CONTAINS as
supported pushdown functions by either overriding or adding additional functions in "getPushDownFunctions" method.
For this example, we'll call the class MyOracleExecutionFactory. Add the
org.teiid.translator.Translator
annotation to the class, e.g.
@Translator(name="myoracle")
Optionally register new FunctionModifiers on the start of the ExecutionFactory to handle translation of these functions. Given that the syntax of these functions is same as other typical functions, this probably isn't needed - the default translation should work.
Create a new translator jar containing your custom ExecutionFactory. Refer to Section 3.7, “Packaging” and Section 3.8, “Deployment” for instructions on using the JAR file. Once this is extended translator is deployed in the Teiid Server, use "myoracle" as translator name instead of the "oracle" in your VDB's Oracle source configuration.
Non-pushdown support requires a Java function that matches the metadata supplied in the FunctionDefinitions.xmi file. You must create a Java method that contains the function’s logic. This Java method should accept the necessary arguments, which the Teiid System will pass to it at runtime, and function should return the calculated or altered value.
Code Requirements
The java class containing the function method must be defined public.
As many UDF methods as you want can be declared on the same class.
The function method must be public and static.
Number of input arguments and types must match the function metadata defined in Section 5.1, “UDF Definition”.
Any exception can be thrown, but Teiid will rethrow the exception
as a
FunctionExecutionException
.
You may optionally add an additional org.teiid.CommandContext argument as the first parameter.
The CommandContext interface provides access to information about the current command, such as the executing user, Subject, the vdb, the session id, etc.
This CommandContext parameter should not be delared in the function metadata.
Example 5.1. Sample code
package org.something;
public class TempConv
{
/**
* Converts the given Celsius temperature to Fahrenheit, and returns the
* value.
* @param doubleCelsiusTemp
* @return Fahrenheit
*/
public static Double celsiusToFahrenheit(Double doubleCelsiusTemp)
{
if (doubleCelsiusTemp == null)
{
return null;
}
return (doubleCelsiusTemp)*9/5 + 32;
}
}
Example 5.2. Sample CommandContext Usage
package org.something;
public class SessionInfo
{
/**
* @param context
* @return the created Timestamp
*/
public static Timestamp sessionCreated(CommandContext context)
{
return new Timestamp(context.getSession().getCreatedTime());
}
}
The corresponding UDF would be declared as Timestamp sessionCreated().
Once a user-defined function model (FunctionDefinitions.xmi) has been created in in the Designer Tool, it can be added to the VDB for use by Teiid.
Dynamic VDBs do not use Designer generated artifacts, such as a FunctionDefinition.xmi file.
Even with that limitation dynamic vdbs may still utilize UDFs through custom coding.
The ExecutionFactory.getMetadata call allows for the definition of metadata via a MetadataFactory.
Use the MetadataFactory.addFunction to add function for use only by that translator instance.
Functions added directly to the source schema are specific to that schema - their fully qualified name will include the schema and the function can not be pushed to a different source.
The ExecutionFactory.getPushdownFunctions method can be used to describe functions that are valid against all instances of a given translator type. The function names are expected to be
prefixed by the translator type, or some other logical grouping, e.g. salesforce.includes. The full name of the function once imported into the system will qualified by the SYS schema, e.g. SYS.salesforce.includes.
Any funcitons added via these mechanisms do not need to be declared in ExecutionFactory.getSupportedFunctions. Any of the additional handling, such as adding a FunctionModifier, covered above is also applicable here. All pushdown functions will have function name set to only the simple name. Schema or other qualification will be removed.
Handling, such as function modifiers, can check the function metadata if there is the potential for an ambiguity.