FunctionMethod addPushDownFunction(String qualifier, String name, String returnType, String...paramTypes)
Translator Capabilities
The ExecutionFactory
class defines all the methods that describe the capabilities of a Translator. These are used by the Connector Manager to determine what kinds of commands the translator is capable of executing. A base ExecutionFactory
class implements all the basic capabilities methods, which says your translator does not support any capabilities. Your extended ExecutionFactory
class must override the the necessary methods to specify which capabilities your translator supports. You should consult the debug log of query planning (set showplan debug) to see if desired pushdown requires additional capabilities.
Capability Scope
Note capabilities are determined and cached for the lifetime of the translator. Capabilities based on connection/user are not supported.
Capabilities
The following table lists the capabilities that can be specified in the ExecutionFactory
class.
Capability | Requires | Description |
---|---|---|
SelectDistinct |
Translator can support SELECT DISTINCT in queries. |
|
SelectExpression |
Translator can support SELECT of more than just column references. |
|
SelectExpressionArrayType |
SelectExpression, ArrayType |
Translator can support SELECT of array expressions. |
SelectWithoutFrom |
Translator can support a SELECT of scalar values without a FROM clause |
|
AliasedTable |
Translator can support Tables in the FROM clause that have an alias. |
|
InnerJoins |
Translator can support inner and cross joins |
|
SelfJoins |
AliasedGroups and at least one of the join type supports. |
Translator can support a self join between two aliased versions of the same Table. |
OuterJoins |
Translator can support LEFT and RIGHT OUTER JOIN. |
|
FullOuterJoins |
Translator can support FULL OUTER JOIN. |
|
DependentJoins |
Base join and criteria support |
Translator supports key set dependent join pushdown. See Dependent Join Pushdown. When set the MaxDependentInPredicates and MaxInCriteriaSize values are not used by the engine, rather all independent values are made available to the pushdown command. |
FullDependentJoins |
Base join and criteria support |
Translator supports full dependent join pushdown. See Dependent Join Pushdown. When set the MaxDependentInPredicates and MaxInCriteriaSize values are not used by the engine, rather the entire independent dataset is made available to the pushdown command. |
LateralJoin |
Translator supports lateral join pushdown with sideways correlation. |
|
LateralJoinCondition |
LateralJoin |
Translator supports lateral join pushdown with a join condition. |
OnlyLateralJoinProcedure |
LateralJoin |
Translator supports only lateral join to a procedure or table valued function. |
SubqueryInOn |
Join and base subquery support, such as ExistsCriteria |
Translator can support subqueries in the ON clause. Defaults to true. |
InlineViews |
AliasedTable |
Translator can support a named subquery in the FROM clause. |
ProcedureTable |
Translator can support a table that returns a table in the FROM clause. |
|
ProcedureParameterExpression |
Translator can support an expression, not just a literal, as a procedure parameter. |
|
BetweenCriteria |
Not currently used - between criteria is rewriten as compound comparisions. |
|
CompareCriteriaEquals |
Translator can support comparison criteria with the operator |
|
CompareCriteriaOrdered |
Translator can support comparison criteria with the operator |
|
CompareCriteriaOrderedExclusive |
Translator can support comparison criteria with the operator |
|
LikeCriteria |
Translator can support LIKE criteria. |
|
LikeCriteriaEscapeCharacter |
LikeCriteria |
Translator can support LIKE criteria with an ESCAPE character clause. |
SimilarTo |
Translator can support SIMILAR TO criteria. |
|
LikeRegexCriteria |
Translator can support LIKE_REGEX criteria. |
|
InCriteria |
MaxInCriteria |
Translator can support IN predicate criteria. |
InCriteriaSubquery |
Translator can support IN predicate criteria where values are supplied by a subquery. |
|
IsNullCriteria |
Translator can support IS NULL predicate criteria. |
|
OrCriteria |
Translator can support the OR logical criteria. |
|
NotCriteria |
Translator can support the NOT logical criteria. IMPORTANT: This capability also applies to negation of predicates, such as specifying IS NOT NULL, |
|
ExistsCriteria |
Translator can support EXISTS predicate criteria. |
|
QuantifiedCompareCriteriaAll |
Translator can support a quantified comparison criteria using the ALL quantifier. |
|
QuantifiedCompareCriteriaSome |
Translator can support a quantified comparison criteria using the SOME or ANY quantifier. |
|
OnlyLiteralComparison |
If only Literal comparisons (equality, ordered, like, etc.) are supported for non-join conditions. |
|
Convert(int fromType, int toType) |
Used for fine grained control of convert/cast pushdown. The |
|
OrderBy |
Translator can support the ORDER BY clause in queries. |
|
OrderByUnrelated |
OrderBy |
Translator can support ORDER BY items that are not directly specified in the select clause. |
OrderByNullOrdering |
OrderBy |
Translator can support ORDER BY items with NULLS FIRST/LAST. |
OrderByWithExtendedGrouping |
OrderBy |
Translator can support ORDER BY directly over a GROUP BY with an extended grouping element such as a ROLLUP. |
GroupBy |
Translator can support an explicit GROUP BY clause. |
|
GroupByRollup |
GroupBy |
Translator can support GROUP BY (currently a single) ROLLUP. |
GroupByMultipleDistinctAggregates |
GroupBy |
Translator can support GROUP BY to create multiple distinct aggregates (See IMPALA-110). |
Having |
GroupBy |
Translator can support the HAVING clause. |
AggregatesAvg |
Translator can support the AVG aggregate function. |
|
AggregatesCount |
Translator can support the COUNT aggregate function. |
|
AggregatesCountBig |
AggregatesCount, AggregatesCountStar |
Translator supports a separate COUNT function that returns a long value. If false COUNT will be pushed instead. |
AggregatesCountStar |
Translator can support the COUNT(*) aggregate function. |
|
AggregatesDistinct |
At least one of the aggregate functions. |
Translator can support the keyword DISTINCT inside an aggregate function. This keyword indicates that duplicate values within a group of rows will be ignored. |
AggregatesMax |
Translator can support the MAX aggregate function. |
|
AggregatesMin |
Translator can support the MIN aggregate function. |
|
AggregatesSum |
Translator can support the SUM aggregate function. |
|
AggregatesEnhancedNumeric |
Translator can support the VAR_SAMP, VAR_POP, STDDEV_SAMP, STDDEV_POP aggregate functions. |
|
StringAgg |
Translator can support the string_agg aggregate function. |
|
ListAgg |
Translator can support a restricted form (matching Oracle’s listagg) of the string_agg aggregate function. |
|
ScalarSubqueries |
Translator can support the use of a subquery in a scalar context (wherever an expression is valid). |
|
ScalarSubqueryProjection |
ScalarSubqueries |
Translator can support the use of a projected scalar subquery. |
CorrelatedSubqueries |
At least one of the subquery pushdown capabilities. |
Translator can support a correlated subquery that refers to an elementin the outer query. |
CorrelatedSubqueryLimit |
CorrelatedSubqueries |
Defaults to CorrelatedSubqueries support. Translator can support a correlated subquery with a limit clause. |
CaseExpressions |
Not currently used - simple case is rewriten as searched case. |
|
SearchedCaseExpressions |
Translator can support |
|
Unions |
Translator support UNION and UNION ALL |
|
Intersect |
Translator supports INTERSECT |
|
Except |
Translator supports Except |
|
SetQueryOrderBy |
Unions, Intersect, or Except |
Translator supports set queries with an ORDER BY |
SetQueryLimitOffset |
(Unions, Intersect, or Except) and (RowLimit or RowOffset) |
Translator supports set queries with a LIMIT and/or OFFSET which is determined by the respective RowLimit and RowOffset capability. Defaults to true if RowLimit or RowOffset is supported. |
RowLimit |
Translator can support the limit portion of the limit clause |
|
RowOffset |
Translator can support the offset portion of the limit clause |
|
FunctionsInGroupBy |
GroupBy |
Translator can support non-column reference grouping expressions. |
InsertWithQueryExpression |
Translator supports INSERT statements with values specified by an QueryExpression. |
|
BatchedUpdates |
Translator supports a batch of INSERT, UPDATE and DELETE commands to be executed together. |
|
BulkUpdate |
Translator supports updates with multiple value sets |
|
CommonTableExpressions |
Translator supports the WITH clause. |
|
SubqueryCommonTableExpressions |
CommonTableExpressions |
Translator supports a WITH clause in subqueries. |
RecursiveCommonTableExpressions |
CommonTableExpressions |
Translator supports recursive common table expressions |
ElementaryOlapOperations |
Translator supports window functions and analytic functions RANK, DENSE_RANK, and ROW_NUMBER. |
|
WindowFrameClause |
ElementaryOlapOperations |
Translator supports window frame RANGE/ROWS clause. Defaults to ElementaryOlapOperations support value. |
WindowOrderByWithAggregates |
ElementaryOlapOperations |
Translator supports windowed aggregates with a window order by clause. |
WindowDistinctAggregates |
ElementaryOlapOperations, AggregatesDistinct |
Translator supports windowed distinct aggregates. |
AdvancedOlapOperations |
ElementaryOlapOperations |
Translator supports aggregate conditions. |
WindowFunctionCumeDist |
Translator supports CUME_DIST window function. Defaults to the support value for ElementaryOlapOperations |
|
WindowFunctionPercentDist |
Translator supports PERCENT_DIST window function. Defaults to the support value for ElementaryOlapOperations |
|
WindowFunctionNtile |
Translator supports NTILE window function. Defaults to the support value for ElementaryOlapOperations |
|
WindowFunctionNthValue |
Translator supports NTH_VALUE window function. Defaults to the support value for ElementaryOlapOperations |
|
OnlyFormatLiterals |
function support for a parse/format function and an implementation of the supportsFormatLiteral method. |
Translator supports only literal format patterns that must be validated by the supportsFormatLiteral method. |
FormatLiteral(String literal, Format type) |
OnlyFormatLiterals |
Translator supports the given literal format string. |
ArrayType |
Translator supports the push down of array values. |
|
OnlyCorrelatedSubqueries |
CorrelatedSubqueries |
Translator ONLY supports correlated subqueries. Uncorrelated scalar and exists subqueries will be pre-evaluated prior to push-down. |
SelectWithoutFrom |
SelectExpressions |
Translator supports selecting values without a FROM clause, e.g. SELECT 1. |
Upsert |
Translator supports an upsert style insert. |
|
OnlyTimestampAddLiteral |
function support for a timestampadd function. |
Translator supports only a literal interval value. |
MultipleOpenExecutions |
Translator supports multiple open executions against a single connection. If false, in transactional scenarios the execution will be thread bound. |
|
GeographyType |
Translator supports the geograpy type variations of ST_ geospatial functions. |
Note that any pushdown subquery must itself be compliant with the Translator capabilities.
Command Form
The method ExecutionFactory.useAnsiJoin()
should return true if the Translator prefers the use of ANSI style join structure for join trees that contain only INNER and CROSS joins.
The method ExecutionFactory.requiresCriteria()
should return true if the Translator requires criteria for any Query, Update, or Delete. This is a replacement for the model support property Where All
.
Scalar Functions
The method ExecutionFactory.getSupportedFunctions()
can be used to specify which system/user defined scalar and user defined aggregate functions the Translator supports. The constants interface org.teiid.translator.SourceSystemFunctions
contains the string names of all possible built-in pushdown functions, which includes the four standard math operators: +, -, *, and /.
Not all system functions appear in SourceSystemFunctions, since some system functions will always be evaluated in Teiid, are simple aliases to other functions, or are rewritten to a more standard expression.
This documentation for system functions can be found at Scalar Functions. If the Translator states that it supports a function, it must support all type combinations and overloaded forms of that function.
A translator may also indicate support for scalar functions that are intended for pushdown evaluation by that translator, but are not registered as user defined functions via a model/schema. These pushdown
functions are reported to the engine via the ExecutionFactory.getPushDownFunctions()
list as FunctionMethod
metadata objects. The FuncitonMethod
representation allow the translator to control all of the metadata related to the function, including type signature, determinism, varargs, etc. The simplest way to add a pushdown function is with a call to ExecutionFactory.addPushDownFunction
:
This resulting function will be known as sys.qualifier.name, but can be called with just name as long as the function name is unique. The returned FunctionMethod
object may be further manipulated depending upon the needs of the source. An example of adding a custom concat vararg function in an ExecutionFactory
subclass:
public void start() throws TranslatorException {
super.start();
FunctionMethod func = addPushDownFunction("oracle", "concat", "string", "string", "string");
func.setVarArgs(true);
...
}
Physical Limits
The method ExecutionFactory.getMaxInCriteriaSize()
can be used to specify the maximum number of values that can be passed in an IN criteria. This is an important constraint as an IN criteria is frequently used to pass criteria between one source and another using a dependent join.
The method ExecutionFactory.getMaxDependentInPredicates()
is used to specify the maximum number of IN predicates (of at most MaxInCriteriaSize) that can be passed as part of a dependent join. For example if there are 10000 values to pass as part of the dependent join and a MaxInCriteriaSize of 1000 and a MaxDependentInPredicates setting of 5, then the dependent join logic will form two source queries each with 5 IN predicates of 1000 values each combined by OR.
The method ExecutionFactory.getMaxFromGroups()
can be used to specify the maximum number of FROM Clause groups that can used in a join. -1 indicates there is no limit.
The method ExecutionFactory.getMaxProjectedColumns()
can be used to specify the maximum number of columns or expressions in the select clause. -1 indicates there is no limit.
Update Execution Modes
The method ExecutionFactory.supportsBatchedUpdates()
can be used to indicate that the Translator supports executing the BatchedUpdates
command.
The method ExecutionFactory.supportsBulkUpdate()
can be used to indicate that the Translator accepts update commands containg multi valued Literals.
Note that if the translator does not support either of these update modes, the query engine will compensate by issuing the updates individually.
Default Behavior
The method ExecutionFactory.getDefaultNullOrder()
specifies the default null order. Can be one of UNKNOWN, LOW, HIGH, FIRST, LAST. This is only used if ORDER BY is supported, but null ordering is not.
The method ExecutionFactory.getCollation()
specifies the default collation. If set to a value that does not match the collation locale defined by org.teiid.collationLocale, then some ordering may not be pushed down.
The method ExecutionFactory.getRequiredLikeEscape()
specifies the required like escape character. Used only when a source supports a specific escape.
Use of Connections
Method | Description | Default |
---|---|---|
is/setSourceRequired |
True indicates a source connection is required for fetching the metadata of the source or executing queries. |
true |
is/setSourceRequiredForMetadata |
True indicates a source connection is required for fetching the metadata of the source. |
SourceRequired |
Transaction Behavior
ExecutionFactory.get/setTransactionSupport specifies the highest level of transaction supported by connections to the source. This is used as a hint to the engine for deciding when to start a transaction in the autoCommitTxn=DETECT mode. Defaults to XA.