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

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

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

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


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 JOINs 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

  • 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 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

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 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 thread

FALSE

useDoubleSlashToEscapeRegex

Used 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