13.16. JDBC Translator

This translator provides support for most SQL constructs supported by JBoss Data Virtualization, except for row limit/offset and EXCEPT/INTERSECT. It translates source SQL into ANSI compliant syntax.

This translator can be used when another more specific type is not available.

This translator is the same as jdbc-ansi, except that it disables support for function, UNION and aggregate pushdown.

This translator is for use with Microsoft Access 2003 or later.

This translator is for use with DB2 8 or later (and DB2 for i 5.4 or later).

Execution properties specific to DB2:

The Apache HBase Translator exposes querying functionality to HBase Tables. Apache Phoenix is an SQL interface for HBase. With the Phoenix Data Sources, the translator translates Teiid push-down commands into Phoenix SQL.

The HBase Translator does not support Join commands, because Phoenix has more simple constraints. The only supported is that for the Primary Key, which maps to the HBase Table Row ID. This translator is developed with Phoenix 4.x for HBase 0.98.1+.

If you use the translator for Apache HBase, be aware that insert statements can rewrite data. To illustrate, here is a standard set of SQL queries:

CREATE TABLE TableA (id integer PRIMARY KEY, name varchar(10)); 
INSERT INTO TableA (id, name) VALUES (1, 'name1'); 
INSERT INTO TableA (id, name) VALUES (1, 'name2');

Normally, the second INSERT command would fail as the uniqueness of the primary key would be corrupted. However, with the HBase translator, the command will not fail. Rather, it will rewrite the data in the table, (so "name1" would become "name2"). This is because the translator converts the INSERT command into an UPSERT command.

derby - for use with Derby 10.1 or later.

This translator is for use with Excel 2003 or later via a JDBC-ODBC bridge.

This translator is for use with the Greenplum database.

For use with h2 version 1.1 or later.

This translator is for use with the Hive database. Hive is a data warehousing infrastructure based on Hadoop. Hadoop provides massive scale out and fault tolerance capabilities for data storage and processing (using the map-reduce programming paradigm) on commodity hardware.

Hive has limited support for data types as it supports integer variants, boolean, float, double and string. It does not have native support for time based types, xml or LOBs. These limitations are reflected in the translator capabilities. The view table can use these types, however the translator would need to specify the necessary transformations. Note that in those situations, the evaluations will be done in the JBoss Data Virtualization engine. Another limitation Hive has is, it only supports EQUI join, so using any other join types on its source tables will result in inefficient queries. Currently there is no tooling support for metadata import from Hive in Teiid Designer. Partition columns may be used as criteria within a WHERE clause, however, they cannot be returned as part of the result set. The Hive importer does not have concept of catalog or source schema, nor does it import keys, procedures, indexes and so forth.

Importer properties specific to Hive:

This translator is for use with HSQLDB 1.7 or later.

This translator is for use with Cloudera Impala 1.2.1 or later.

Impala has limited support for data types. It is does not have native support for time/date/xml or LOBs. These limitations are reflected in the translator capabilities. A Teiid view can use these types, however the transformation would need to specify the necessary conversions. Note that in those situations, the evaluations will be done in Teiid engine.

Impala only supports EQUI join, so using any other joins types on its source tables will result in inefficient queries.

To write criteria based on partitioned columns, model them on the source table, but do not include them in selection columns.

Impala specific importer properties:

useDatabaseMetaData - Set to true to use the normal import logic with the option to import index information disabled. Defaults to false.

If false the typical JDBC DatabaseMetaData calls are not used so not all of the common JDBC importer properties are applicable to Impala. (You can still use excludeTables regardless.)

This translator is for use with Ingres 2006 or later.

This translator is for use with Ingres 9.3 or later.

For use with Intersystems Cache Object database (only relational aspect of it)

For use with any Informix version.

This translator is for use with MetaMatrix 5.5.0 or later.

This translator is for use with Modeshape 2.2.1 or later.

The PATH, NAME, LOCALNODENAME, DEPTH, and SCORE functions are accessed as pseudo-columns, e.g. "nt:base"."jcr:path".

JBoss Data Virtualization user defined functions (prefixed by JCR_) are available for CONTAINS, ISCHILDNODE, ISDESCENDENT, ISSAMENODE, REFERENCE. See the JCRFunctions.xmi file.

If a selector name is needed in a JCR function, you can use the pseudo-column "jcr:path". For example, JCR_ISCHILDNODE(foo.jcr_path, 'x/y') would become ISCHILDNODE(foo, 'x/y') in the ModeShape query.

An additional pseudo-column "mode:properties" can be imported by setting the ModeShape JDBC connection property teiidsupport=true. The "mode:properties" column should be used by the JCR_REFERENCE and other functions that expect a .* selector name. For example, JCR_REFERENCE(nt_base.jcr_properties) would become REFERENCE("nt:base".*) in the ModeShape query.

This translator is for use with MySQL version 5 or later. It also works with backwards-compatible MySQL derivatives, including MariaDB.

The MySQL Translator expects the database or session to be using ANSI mode. If the database is not using ANSI mode, an initialization query must be used on the pool to set ANSI mode:

set SESSION sql_mode = 'ANSI'

If you deal with null timestamp values, then set the connection property zeroDateTimeBehavior=convertToNull. Otherwise you'll get conversion errors in Teiid that '0000-00-00 00:00:00' cannot be converted to a timestamp.

This translator is for use with any Netezza version.

Netezza-specific execution properties:

SqlExtensionsInstalled- indicates that SQL Extensions including support fo REGEXP_LIKE are installed. Defaults to false.

This translator is for use with Oracle 9i or later.

Sequences may be used with the Oracle translator. A sequence may be modeled as a table with a name in source of DUAL and columns with the name in source set to this:

					{{<sequence name>.[nextval|currval].}}</code>

Teiid 8.4 and Prior Oracle Sequence DDL

CREATE FOREIGN TABLE seq (nextval integer OPTIONS (NAMEINSOURCE 'seq.nextval'), currval integer options (NAMEINSOURCE 'seq.currval') ) OPTIONS (NAMEINSOURCE 'DUAL')

With Teiid 8.5 it is no longer necessary to rely on a table representation and Oracle specific handling for sequences. See DDL Metadata for representing currval and nextval as source functions.

You can also use a sequence as the default value for insert columns by setting the column to autoincrement and the name in source to this:

<element name>:SEQUENCE=<sequence name>.<sequence value>.

A rownum column can be added to any Oracle physical table to support the rownum pseudo-column. A rownum column must have a name in source of this:

					<code>rownum</code>. 
			

These rownum columns do not have the same semantics as the Oracle rownum construct so care must be taken in their usage.

Oracle specific importer properties:

useGeometryType- Use the Teiid Geomety type when importing columns with a source type of SDO_GEOMETRY. Defaults to false.

useIntegralTypes- Use integral types rather than decimal when the scale is 0. Defaults to false.

Execution properties specific to Oracle:

This translator is for use with 8.0 or later clients and 7.1 or later server.

PostgreSQL specific execution properties:

PostGisVersion- indicate the PostGIS version in use. Defaults to 0 meaning PostGIS is not installed. Will be set automatically if the database version is not set. _ProjSupported- boolean indicating if Proj is support for PostGis. Will be set automatically if the database version is not set.

The PrestoDB translator, known by the type name prestodb, exposes querying functionality to PrestoDB Data Sources. In data integration respect, PrestoDB has very similar capabilities of Teiid, however it goes beyond in terms of distributed query execution with multiple worker nodes. Teiid's execution model is limited to single execution node and focuses more on pushing the query down to sources. Currently Teiid has much more complete query support and many enterprise features.

The PrestoDB translator supports only SELECT statements with a restrictive set of capabilities. This translator is developed with 0.85 version of PrestoDB and capabilities are designed for this version. With new versions of PrestoDB Teiid will adjust the capabilities of this translator. Since PrestoDB exposes a relational model, the usage of this is no different than any RDBMS source like Oracle, DB2 etc. For configuring the PrestoDB consult the PrestoDB documentation.

This translator is for use with SQL Server 2000 or later. A SQL Server JDBC driver version 2.0 or later (or compatible e.g. JTDS 1.2 or later) must be used. The SQL Server DatabaseVersion property may be set to 2000, 2005, 2008, or 2012, but otherwise expects a standard version number, for example, 10.0.

Execution properties specific to SQL Server:

This translator is for use with Sybase version 12.5 or later. If used in a dynamic vdb and no import properties are specified (not recommended, see import properties below), then exceptions can be thrown retrieving system table information. Specify a schemaPattern or use excludeTables to exclude system tables if this occurs.

If the name in source metadata contains quoted identifiers (such as reserved words or words containing characters that would not otherwise be allowed) and you are using a jconnect Sybase driver, you must first configure the connection pool to enable quoted_identifier.

jdbc:sybase:Tds:host.at.some.domain:5000/db_name?SQLINITSTRING=set quoted_identifier on

Execution properties specific to Sybase:

This translator is for use with Sybase IQ version 15.1 or later.

This translator is for use with Teiid 6.0 or later.

This translator is for use with Teradata V2R5.1 or later.