Skip to main content
Skip table of contents

Translators

The CData Virtuality Server Connector Architecture (DVSCA) provides the CData Virtuality Server with a robust mechanism for integrating with external systems. The DVSCA defines a common client interface between the CData 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 CData 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 determine 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

allowsSpecialCharsInColumnName
  • When set to FALSE, special characters in column names are replaced with underscores;
  • When set to TRUE, the following characters are allowed in column names: -, *, (, ), space.

These characters in column names must be supported by the data source.

FALSE
columnNameType

The value determines the way table column names are presented in the CData 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

maxStringSizeMaximum 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

CData 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

SupportsAdvancedOlapOperationsReturns TRUE if Advanced OLAP operations are supported including the aggregate function filter clause


SupportsAggregatesAvgSupport indicates connector can accept the AVG aggregate function


SupportsAggregatesCountSupport indicates connector can accept the COUNT aggregate function


SupportsAggregatesCountStarSupport indicates connector can accept the COUNT(*) aggregate function


SupportsAggregatesDistinctSupport indicates connector can accept DISTINCT within aggregate functions


SupportsAggregatesEnhancedNumericSupport indicates connector can accept STDDEV_POP, STDDEV_VAR, VAR_POP, VAR_SAMP


SupportsAggregatesLeadLagFirstLastValueIndicates whether the source supports lead(), lag(), firstValue(), and lastValue() aggregation symbols


SupportsAggregatesMaxSupport indicates connector can accept the MAX aggregate function


SupportsAggregatesMinSupport indicates connector can accept the MIN aggregate function


SupportsAggregatesSumSupport indicates connector can accept the SUM aggregate function


SupportsAliasedTable

Support indicates connector can accept groups with aliases


SupportsArrayAggReturns TRUE if array_agg is supported


SupportsCommonTableExpressionsReturns TRUE if the WITH clause is supported


SupportsCompareCriteriaEqualsSupport indicates connector accepts criteria of form (element = constant)


SupportsCompareCriteriaOrderedSupport indicates connector accepts criteria of form (element <= | >= constant). The query engine will may pushdown queries containing < or > if NOT is also supported


SupportsCorrelatedSubqueriesSupport indicates connector can accept correlated subqueries wherever subqueries are accepted


SupportsElementaryOlapOperationsReturns TRUE if Elementary OLAP operations are supported including window functions and inline window specifications that include simple expressions in partitioning and ordering


SupportsExceptSupport indicates that the connector supports EXCEPT of two queries


SupportsExistsCriteriaSupport indicates connector accepts the EXISTS criteria


SupportsFunctionsInGroupBySupport indicates that the connector supports non-column expressions in GROUP BY, such as: SELECT dayofmonth(theDate), COUNT(*) FROM table GROUP BY dayofmonth(theDate)


SupportsGroupByWhether the source supports an explicit GROUP BY clause


SupportsHavingWhether the source supports the HAVING clause


SupportsInCriteriaSupport indicates connector accepts criteria of form (element IN set)


SupportsInCriteriaSubquerySupport indicates connector accepts IN criteria with a subquery on the right side


SupportsInlineViewsSupport indicates connector can accept inline views (subqueries in the FROM clause)


SupportsInnerJoins

Set to TRUE to indicate that INNER JOINs are supported


SupportsIntersectSupport indicates that the connector supports INTERSECT of two queries


SupportsIsNullCriteriaSupport indicates connector accepts criteria of form (element IS NULL)


SupportsLikeCriteriaSupport indicates connector accepts criteria of form (element LIKE constant)


SupportsLikeCriteriaEscapeCharacterSupport indicates connector accepts criteria of form (element LIKE constant ESCAPE char)


SupportsLikeRegexReturns TRUE if the LIKE_REGEX predicate is supported


SupportsMultipleOpenExecutionsSet 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


SupportsNotCriteriaSupport indicates connector accepts logical criteria NOT


SupportsQuantifiedCompareCriteriaAllSupport indicates connector accepts the quantified comparison criteria that use ALL


SupportsQuantifiedCompareCriteriaSomeSupport indicates connector accepts the quantified comparison criteria that use SOME


SupportsOnlySingleTableGroupByWhether the source supports grouping only over a single table


SupportsOrCriteriaSupport indicates connector accepts logical criteria connected by OR


SupportsOrderBy

Set to TRUE to indicate that the ORDER BY clause is supported

TRUE for

  • MongoDB

FALSE for

  • JDBC Connectors
    • access
    • jdbc-simple
  • others
SupportsOrderByNullOrderingReturns whether the database supports explicit join ordering


SupportsOrderByUnrelatedSupport indicates connector accepts ORDER BY clause with columns not from SELECT


SupportsOuterJoins

Set to TRUE to indicate that OUTER JOINs 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

SupportsRowLimitGets whether the connector can limit the number of rows returned by a query


SupportsRowOffsetGets 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


SupportsScalarSubqueriesSupport indicates connector can accept scalar subqueries in the SELECT, WHERE, and HAVING clauses


SupportsSearchedCaseExpressionsSupport 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


SupportsSelfJoinsSupport indicates connector can accept self-joins where a group is joined to itself with aliases.  The connector must also support


SupportsSetQueryOrderBySupport indicates that the connector supports an ORDER BY on a SetQuery


SupportsSimilarToReturns TRUE if the SIMILAR TO predicate is supported


SupportsUnionsSupport indicates that the connector supports UNION of two queries


tableNameType

The value determines the way table name is presented in the CData 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 threadFALSE
useDoubleSlashToEscapeRegexUsed to change the default escaping behaviour in LIKE_REGEX expressions
  • TRUE for Redshift
  • FALSE for others

allowsSpecialCharsInColumnName available since v4.0.8

Space allowed in column name since v4.1

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.