Translators

You are looking at an older version of the documentation. The latest version is found here.

The Data Virtuality Server Connector Architecture (DVSCA) provides the Data Virtuality Server with a robust mechanism for integrating with external systems. The DVSCA defines a common client interface between the Data Virtuality Server and an external system that includes metadata as to what SQL constructs are supported for pushdown and the ability to import metadata from the external system. A Translator is the heart of the DVSCA and acts as the bridge logic between the Data Virtuality Server and an external system. It has its own translation properties, which are described in the following Connector descriptions. Connectors can have a number of configurable properties. These are broken down into translator properties, which determine aspects of how data is retrieved, and import settings, which determines what metadata is read for import. The translator properties for a translator typically have reasonable defaults. For specific translator types, e.g. the Derby translator, base translator properties are already tuned to match the source. In most cases, the user will not need to adjust their values.

Translator Properties Shared by All Connectors

(Properties listed in alphabetical order)

To view the full table, click the expand button in its top right corner

Name	Description	Default
`columnNameType`	The value determines the way table column names are presented in the Data Virtuality Server: `NONE`: all column names are presented in the lower case `PRESERVE`: column names appear in the same way they appear in the source (e.g. coLuMnName) `UPPERCASE`: all column names are presented in the upper case String values for `columnNameType` should be written in uppercase. Usage example: `columnNameType=UPPERCASE`	`NONE`
`Immutable`	Set to `TRUE` to indicate that the source never changes	`FALSE`
`MaxInCriteriaSize`	If in criteria are supported, defines what the maximum number of in entries are per predicate. -1 indicates no limit	`-1`
`MaxDependentInPredicates`	If in criteria are supported, defines what the maximum number of predicates that can be used for a dependent join. Values less than `1` indicate to use only one in predicate per dependent value pushed	`-1`
`maxStringSize`	Maximum size of string data type columns. This value is used when the user creates a column of string data type without specifying a size for it	PostgreSQL: `1048000` SQL Server: `2147483647` MySQL, MemSQL: `65535` Oracle: `2000` Redshift: `65528` Snowflake: `16777216` Exasol: `1999998` Vertica: `32500` Teradata: `32000` BigQuery: `100000000` IBM DB2: `16336` Hive: `65534` SAP Hana: `5000` Data Virtuality view: `2147483647` Other data sources: `4000`
`oversizeStringProcessing`	Defines the system behaviour for table creation when data inserted into a `STRING` column is longer than the value of the `maxStringSize` translator property. `CUT` - on table creation and data insertion string column size is set and inserted values are cut to `maxStringSize` if their sizes exceed the `maxStringSize` value `CONVERT` - on table creation string columns are converted to `CLOB` data type if source column size is higher than `maxStringSize` value. On inserting string values into an already existing table system cuts them to `maxStringSize` `FAIL` - on table creation string column size is set to `maxStringSize` if its size exceeds the `maxStringSize` value. Processing of `INSERT` queries is delegated to the target DBMS (in most cases such queries just fail but, for example, MySQL cuts strings exceeding the column size)	MySQL, MemSQL, Exasol, Hive, IBM DB2, SAP Hana: `FAIL` Other data sources: `CUT`
`RequiresCriteria`	Set to `TRUE` to indicate that source `SELECT`/`UPDATE`/`DELETE` queries require a `WHERE` clause	`FALSE`
`SupportedJoinCriteria`	If joins are supported, defines what criteria may be used as the join criteria. Can be one of the following: `ANY`, `THETA`, `EQUI`, or `KEY`	`ANY`
`SupportsAdvancedOlapOperations`	Returns `TRUE` if Advanced OLAP operations are supported including the aggregate function filter clause
`SupportsAggregatesAvg`	Support indicates connector can accept the `AVG` aggregate function
`SupportsAggregatesCount`	Support indicates connector can accept the `COUNT` aggregate function
`SupportsAggregatesCountStar`	Support indicates connector can accept the `COUNT(*)` aggregate function
`SupportsAggregatesDistinct`	Support indicates connector can accept `DISTINCT` within aggregate functions
`SupportsAggregatesEnhancedNumeric`	Support indicates connector can accept `STDDEV_POP`, `STDDEV_VAR`, `VAR_POP`, `VAR_SAMP`
`SupportsAggregatesLeadLagFirstLastValue`	Indicates whether the source supports lead(), `lag()`, `firstValue()`, and `lastValue()` aggregation symbols
`SupportsAggregatesMax`	Support indicates connector can accept the `MAX` aggregate function
`SupportsAggregatesMin`	Support indicates connector can accept the `MIN` aggregate function
`SupportsAggregatesSum`	Support indicates connector can accept the `SUM` aggregate function
`SupportsAliasedTable`	Support indicates connector can accept groups with aliases
`SupportsArrayAgg`	Returns `TRUE` if `array_agg` is supported
`SupportsCommonTableExpressions`	Returns `TRUE` if the `WITH` clause is supported
`SupportsCompareCriteriaEquals`	Support indicates connector accepts criteria of form (element = constant)
`SupportsCompareCriteriaOrdered`	Support indicates connector accepts criteria of form (element <= \| >= constant). The query engine will may pushdown queries containing < or > if `NOT` is also supported
`SupportsCorrelatedSubqueries`	Support indicates connector can accept correlated subqueries wherever subqueries are accepted
`SupportsElementaryOlapOperations`	Returns `TRUE` if Elementary OLAP operations are supported including window functions and inline window specifications that include simple expressions in partitioning and ordering
`SupportsExcept`	Support indicates that the connector supports `EXCEPT` of two queries
`SupportsExistsCriteria`	Support indicates connector accepts the `EXISTS` criteria
`SupportsFullOuterJoins`	If outer joins are supported, `TRUE` indicates that `FULL OUTER JOIN`s are supported
`SupportsFunctionsInGroupBy`	Support indicates that the connector supports non-column expressions in `GROUP BY`, such as: `SELECT dayofmonth(theDate), COUNT(*) FROM table GROUP BY dayofmonth(theDate)`
`SupportsGroupBy`	Whether the source supports an explicit `GROUP BY` clause
`SupportsHaving`	Whether the source supports the `HAVING` clause
`SupportsInCriteria`	Support indicates connector accepts criteria of form (element `IN` set)
`SupportsInCriteriaSubquery`	Support indicates connector accepts `IN` criteria with a subquery on the right side
`SupportsInlineViews`	Support indicates connector can accept inline views (subqueries in the `FROM` clause)
`SupportsInnerJoins`	Set to `TRUE` to indicate that `INNER JOIN`s are supported
`SupportsIntersect`	Support indicates that the connector supports `INTERSECT` of two queries
`SupportsIsNullCriteria`	Support indicates connector accepts criteria of form (element `IS NULL`)
`SupportsLikeCriteria`	Support indicates connector accepts criteria of form (element `LIKE` constant)
`SupportsLikeCriteriaEscapeCharacter`	Support indicates connector accepts criteria of form (element `LIKE` constant `ESCAPE` char)
`SupportsLikeRegex`	Returns `TRUE` if the `LIKE_REGEX` predicate is supported
`SupportsMultipleOpenExecutions`	Set to `TRUE` to indicate that multiple executions may be open against a single connection at a time	`FALSE` for: MySQL Netezza Redshift `TRUE` for all others
`SupportsNotCriteria`	Support indicates connector accepts logical criteria `NOT`
`SupportsQuantifiedCompareCriteriaAll`	Support indicates connector accepts the quantified comparison criteria that use `ALL`
`SupportsQuantifiedCompareCriteriaSome`	Support indicates connector accepts the quantified comparison criteria that use `SOME`
`SupportsOnlySingleTableGroupBy`	Whether the source supports grouping only over a single table
`SupportsOrCriteria`	Support indicates connector accepts logical criteria connected by `OR`
`SupportsOrderBy`	Set to `TRUE` to indicate that the `ORDER BY` clause is supported	`TRUE` for most JDBC Connectors MongoDB `FALSE` for JDBC Connectors access jdbc-simple others
`SupportsOrderByNullOrdering`	Returns whether the database supports explicit join ordering
`SupportsOrderByUnrelated`	Support indicates connector accepts ORDER BY clause with columns not from `SELECT`
`SupportsOuterJoins`	Set to `TRUE` to indicate that `OUTER JOIN`s are supported
`SupportsRecursiveCommonTableExpressions`	When set to `TRUE`, recursive common table expressions are pushed down to the data source. Currently supported by PostgreSQL, Snowflake, and Redshift	`TRUE` for: PostgreSQL Snowflake Redshift `FALSE` for all others
`SupportsRowLimit`	Gets whether the connector can limit the number of rows returned by a query
`SupportsRowOffset`	Gets whether the connector supports a SQL clause (similar to `LIMIT` with an offset) that can return result sets that start in the middle of the resulting rows returned by a query
`SupportsScalarSubqueries`	Support indicates connector can accept scalar subqueries in the `SELECT`, `WHERE`, and `HAVING` clauses
`SupportsSearchedCaseExpressions`	Support indicates connector can accept queries with searched `CASE WHEN <criteria> ... END`
`SupportsSelectExpression`	Support indicates connector can accept expressions other than element symbols in the `SELECT` clause. Specific support for the expression type are still checked
`SupportsSelfJoins`	Support indicates connector can accept self-joins where a group is joined to itself with aliases. The connector must also support
`SupportsSetQueryOrderBy`	Support indicates that the connector supports an `ORDER BY` on a `SetQuery`
`SupportsSimilarTo`	Returns `TRUE` if the `SIMILAR TO` predicate is supported
`SupportsUnions`	Support indicates that the connector supports `UNION` of two queries
`tableNameType`	The value determines the way table name is presented in the Data Virtuality Server: `NONE`: table name appears in the same way they appear in the source `LOWERCASE`: table name is presented in the lower case `UPPERCASE`: table name is presented in the upper case String values for `tableNameType` should be written in uppercase. Usage example: `tableNameType=UPPERCASE`	`NONE`
`ThreadBound`	Set to `TRUE` to indicate the translator's Executions should be processed by only a single thread	`FALSE`
`useDoubleSlashToEscapeRegex`	Used to change the default escaping behaviour in `LIKE_REGEX` expressions	`TRUE` for Redshift `FALSE` for others

maxStringSize and oversizeStringProcessing properties are available since v2.4.7

SupportsMultipleOpenExecutions default value for Redshift and Snowflake is TRUE since v2.4.16

SupportsMultipleOpenExecutions default value for Redshift is FALSE since v2.4.17

SupportsRecursiveCommonTableExpressions available since v2.4.32