SET SCHEMA ora;
IMPORT FOREIGN SCHEMA "my-schema" FROM SERVER ora INTO ora OPTIONS ("importer.tableTypes" 'TABLE,VIEW');
JDBC translators
The JDBC translators bridge the SQL semantics and data type differences between Teiid and a target RDBMS. Teiid has a range of specific translators that target the most popular open source and proprietary relational databases.
Usage of a JDBC source is straight-forward. Using Teiid SQL, the source can be queried as if the tables and procedures were local to the Teiid system.
If you are using a relational data source, or a data source that has a JDBC driver, and you do not find a specific translator available for that data source type, then start with the JDBC ANSI translator. The JDBC ANSI translator should enable you to perform the SQL basics. If there specific data source capabilities that are not available, you can define a custom translator that does what you need. For more information, see Translator Development.
UID types including UUID, GUID, or UNIQUEIDENTIFIER are typically mapped to the Teiid string type. JDBC data sources treat UID strings as non-case sensitive, but they are case-sensitive in Teiid. If the source does not support the implicit conversion to the string type, then usage in functions that expect a string value might fail at the source.
The following table lists the execution properties that are shared by all JDBC translators.
Name | Description | Default |
---|---|---|
DatabaseTimeZone |
The time zone of the database. Used when fetching date, time, or timestamp values. |
The system default time zone |
DatabaseVersion |
The specific database version. Used to further tune the use of pushdown operations. |
The base compatible version, or the version that is derived from the DatabaseMetadata.getDatabaseProductVersion string.
Automatic detection requires a connection.
If there are circumstances where you are getting an exception due to capabilities being unavailable (for example, because a connection is not available),
then set |
TrimStrings |
|
false |
RemovePushdownCharacters |
Set to a regular expression to remove characters that not allowed or undesirable for the source.
For example |
|
UseBindVariables |
|
true |
UseCommentsInSourceQuery |
This will embed a leading comment with session/request id in the source SQL for informational purposes. Can be customized with the CommentFormat property. |
false |
CommentFormat |
MessageFormat string to be used if
|
|
MaxPreparedInsertBatchSize |
The max size of a prepared insert batch. |
2048 |
StructRetrieval |
Specify one of the following Struct retrieval modes:
|
OBJECT |
EnableDependentJoins |
Allow dependent join pushdown for sources that use temporary tables (DB2, Derby, H2, HSQL 2.0+, MySQL 5.0+, Oracle, PostgreSQL, SQLServer, SQP IQ, Sybase). |
false |
When specifying the importer property, it must be prefixed with importer.
.
Example: importer.tableTypes
Name | Description | Default |
---|---|---|
catalog |
See DatabaseMetaData.getTables [1] |
null |
schemaName |
Recommended setting to import from a single schema. The schema name will be converted into an escaped pattern,overriding schemaPattern if it is also set. |
null |
schemaPattern |
See DatabaseMetaData.getTables [1] |
null |
tableNamePattern |
See DatabaseMetaData.getTables [1] |
null |
procedureNamePattern |
See DatabaseMetaData.getProcedures [1] |
null |
tableTypes |
Comma separated list — without spaces — of imported table types. See |
null |
excludeTables |
A case-insensitive regular expression that when matched against a fully qualified table name [2] will exclude it from import. Applied after table names are retrieved. Use a negative look-ahead (?!<inclusion pattern>).* to act as an inclusion filter. |
null |
excludeProcedures |
A case-insensitive regular expression that when matched against a fully qualified procedure name [2] will exclude it from import. Applied after procedure names are retrieved. Use a negative look-ahead (?!<inclusion pattern>).* to act as an inclusion filter. |
null |
importKeys |
NOTE: Foreign keys to tables that are not imported will be ignored. |
true |
autoCreateUniqueConstraints |
|
true |
importIndexes |
|
false |
importApproximateIndexes |
WARNING: Setting to |
true |
importProcedures |
|
false |
importSequences |
|
false |
sequenceNamePattern |
LIKE pattern string to use when importing sequences.
Null or |
null |
useFullSchemaName |
When |
false (only change when importing from multiple foreign schema). |
useTypeInfo |
If JDBC DatabaseMetaData getTypeInfo should be used. If false, array component types and unsigned types may not be able to be determined. |
true |
useQualifiedName |
WARNING: When you set this option to |
true (rarely needs changed) |
useCatalogName |
|
true (rarely needs changed) |
widenUnsignedTypes |
|
true |
useIntegralTypes |
|
false |
quoteNameInSource |
|
true |
useAnyIndexCardinality |
|
false |
importStatistics |
|
false |
importRowIdAsBinary |
|
false |
importLargeAsLob |
|
false |
[1] JavaDoc for DatabaseMetaData
[2] The fully qualified name for exclusion is based upon the settings of the translator and the particulars of the database.
All of the applicable name parts used by the translator settings (see useQualifiedName
and useCatalogName
) including catalog,
schema, table will be combined as catalogName.schemaName.tableName
with no quoting. For example, Oracle does not report a catalog,
so the name used with default settings for comparison would be just schemaName.tableName
.
Warning
|
The default import settings will crawl all available metadata.
This import process is time-consuming, and full metadata import is not needed in most situations.
Most commonly you’ll want to limit the import by at least schemaName or schemaPattern and tableTypes .
|
Or in an xml vdb:
<model ...
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.schemaName" value="my-schema"/>
...
</model>
For more information about importer settings, see Virtual databases.
Physical tables, functions, and procedures may optionally have native queries associated with them.
No validation of the native query is performed, it is simply used in a straight-forward manner to generate the source SQL.
For a physical table setting the teiid_rel:native-query
extension metadata will execute the native query as an inline view in the source query.
This feature should only be used against sources that provide inline views.
The native query is used as is and is not treated as a parameterized string.
For example, on a physical table y
with nameInSource="x"`
and teiid_rel:native-query="select c from g"
, the Teiid source
query "SELECT c FROM y"
would generate the SQL query "SELECT c FROM (select c from g) as x"
.
Note that the column names in the native query must match the nameInSource
of the physical table columns for the resulting SQL to be valid.
For physical procedures you may also set the teiid_rel:native-query
extension metadata to a desired query string with
the added ability to positionally reference IN parameters . For more information, see Parameterizable native queries in
Translators.
The teiid_rel:non-prepared
extension metadata property can be set to false
to turn off parameter binding.
Be careful in setting this option, because inbound allows for SQL injection attacks if not properly validated.
The native query does not need to call a stored procedure.
Any SQL that returns a result set that positionally matches the result set that is expected by the physical stored procedure metadata will work.
For example on a stored procedure x
with teiid_rel:native-query="select c from g where c1 = $1 and c2 = `$$1"'
, the Teiid source
query `"CALL x(?)"`
would generate the SQL query `"select c from g where c1 = ? and c2 = `$1"'`
.
Note that ?
in this example will be replaced with the actual value bound to parameter 1.
This feature is turned off by default, because of the inherent security risk in allowing any command to be run against the source.
To enable this feature, override the execution property called SupportsDirectQueryProcedure and set it to true
.
For more information, see Override execution properties in Translators.
By default, the name of the procedure that executes the queries directly is native. To change the name, override the execution property DirectQueryProcedureName.
The JDBC translator provides a procedure to execute any ad-hoc SQL query directly against the source without Teiid parsing or resolving. Since the metadata of this procedure’s results are not known to Teiid, they are returned as an object array. ARRAYTABLE can be used construct tabular output for consumption by client applications. For more information, see arraytable.
SELECT x.* FROM (call jdbc_source.native('select * from g1')) w,
ARRAYTABLE(w.tuple COLUMNS "e1" integer , "e2" string) AS x
SELECT x.* FROM (call jdbc_source.native('insert into g1 (e1,e2) values (?, ?)', 112, 'foo')) w,
ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
SELECT x.* FROM (call jdbc_source.native('update g1 set e2=? where e1 = ?','blah', 112)) w,
ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
SELECT x.* FROM (call jdbc_source.native('delete from g1 where e1 = ?', 112)) w,
ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
The resource adapter for this translator provided through data source in WildFly, See to Admin Guide section WildFly Data Sources for configuration.