SELECT score(1), ID, FREEDATA FROM Docs WHERE contains(freedata, 'nick', 1) > 0Source Supported 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 source function allows for calling a source specific function. The semantics of defining the source function as similar or same to one of defining the UDF.
For example, suppose you want to use the Oracle-specific functions score and contains like:
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 order to configure Teiid to push the above function evaluation to Oracle, Teiid provides a few different ways one can configure their instance.
Extending the Translator
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 functions 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.
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 theorg.teiid.translator.Translatorannotation 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 Packaging and 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.
If you source handing of the function can be described by simple parameter substitution into a string, then you may not need to extend the translator for a source specific function. You can use the extension property teiid_rel:native-query to define the syntax handling - see also DDL Metadata for functions.
See defining the metadata using DDL, you can define your source function in the VDB as
CREATE DATABASE "{vdb-name}";
USE DATABASE "{vdb-name}";
CREATE SERVER AccountsDB FOREIGN DATA WRAPPER oracle OPTIONS ("resource-name" 'java:/oracleDS');
CREATE SCHEMA "{model-name}" SERVER AccountsDB;
SET SCHEMA "{model-name}";
CREATE FOREIGN FUNCTION SCORE (val integer) RETURNS integer;In an XML VDB:
<vdb name="{vdb-name}" version="1">
<model name="{model-name}" type="PHYSICAL">
<source name="AccountsDB" translator-name="oracle" connection-jndi-name="java:/oracleDS"/>
<metadata type="DDL"><![CDATA[
CREATE FOREIGN FUNCTION SCORE (val integer) RETURNS integer;
.... (other tables, procedures etc)
]]>
</metadata>
</model>
</vdb>By default when a source can provide metadata, the Source model’s metadata is automatically retrieved from the source if they were JDBC, File, WebService. The File and WebService sources are static, so one can not add additional metadata on them. However on the JDBC sources you can retrieve the metadata from source and then user can append additional metadata on top of them. For example
CREATE DATABASE "{vdb-name}";
USE DATABASE "{vdb-name}";
CREATE SERVER AccountsDB FOREIGN DATA WRAPPER oracle OPTIONS ("resource-name" 'java:/oracleDS');
CREATE SCHEMA "{model-name}" SERVER AccountsDB;
SET SCHEMA "{model-name}";
IMPORT FROM AccountsDB INTO "{model-name}";
CREATE FOREIGN FUNCTION SCORE (val integer) RETURNS integer;
In an XML VDB:
<vdb name="{vdb-name}" version="1">
<model name="{model-name}" type="PHYSICAL">
<source name="AccountsDB" translator-name="oracle" connection-jndi-name="java:/oracleDS"/>
<metadata type="NATIVE"/>
<metadata type="DDL"><![CDATA[
CREATE FOREIGN FUNCTION SCORE (val integer) RETURNS integer;
]]>
</metadata>
</model>
</vdb>The above example uses NATIVE metadata type (NATIVE is the default for source/physical models) first to retrieve schema information from source, then uses DDL metadata type to add additional metadata. Only metadata not available via the NATIVE translator logic would need to be specified via DDL.
Alternatively, if you are using custom MetadataRepository with your VDB, then provide the "function" metadata directly from your implementation. ex.
<vdb name="{vdb-name}" version="1">
<model name="{model-name}" type="PHYSICAL">
<source name="AccountsDB" translator-name="oracle" connection-jndi-name="java:/oracleDS"/>
<metadata type="{metadata-repo-module}"></metadata>
</model>
</vdb>In the above example, user can implement MetadataRepository interface and package the implementation class along with its dependencies in a WildFly module and supply the module name in the above XML. For more information on how to write a Metadata Repository refer to Custom Metadata Repository.