NOTE: as of the 7.0 release, this article has been added to the Reference guide.
Teiid integration is available via a "Dynamic VDB" without the need for Teiid Designer tooling. While this mode of operation does not yet allow for the creation of view layers, the underlying sources can still be queried as if they are a single source. See the kit's "teiid-example/dynamicvdb-*" for working examples.
To build a dynamic vdb, you'll need to create a "<some-name>-vdb.xml" file. The xml file captures information about the VDB, the sources it integrate, and preferences for importing metadata.
NOTE:
VDB name pattern must adhere to "-vdb.xml" for the Teiid VDB deployer to recognize this file as a dynamic VDB.
my-vdb.xml: (The schema for this file is defined in the "teiid-{version}-client.jar" in "vdb-deployer.xsd" file)
<vdb name="${vdb-name}" version=${vdb-version}>
<property name="UseConnectorMetadata" value="..."/>
<!-- define a model fragment for each data source -->
<model name="${model-name}">
<property name="..." value="..." />
...
<source name="${source-name}" translator-name="${translator-name}"
connection-jndi-name="${deployed-jndi-name}">
</model>
<!-- create translator instances that override default properties -->
<translator name="${translator-name}" type="${translator-type}"/>
<property name="..." value="..."/>
...
</translator>
</vdb>
VDB
${vdb-name}: The name of the VDB. The VDB name referenced through the driver or datasource during the connection time.
${vdb-version}: The version of the VDB (should be an positive integer). This determines the deployed directory location (see Name), and provides an explicit versioning mechanism to the VDB name.
Properties
| Name | Description |
|---|---|
| UseConnectorMetadata | Setting to use connector supplied metadata. Can be "true" or "cached". "true" will obtain metadata once for every launch of Teiid. "cached" will save a file containing the metadata into the <jboss-install>/server/<profile>/data/teiid directory |
Model
${model-name}:The name of the model is used as a top level schema name for all of the metadata imported from the connector. The name should be unique among all Models in the VDB and should not contain the '.' character.
Properties
| Name | Description |
|---|---|
| importer.<propertyname> | Property to be used by the connector importer for the model for purposes importing metadata. See possible property name/values below in JDBC Importer Settings section. Note that using these properties you can narrow or widen the data elements available for integration. |
* See below for all available properties for JDBC and Text connectors
Source
${source-name}: The name of the connector binding to use for this model. This can be any name you like. Only used in multi-source scenarios.
${translator-name}:
${connection-jndi-name}: The JNDI name of this source's connection factory. There should be a corresponding "-ds.xml" file that defines the connection factory in the JBoss AS. Check out the deploying vdb dependencies section for info. You also need to deploy these connection factories before you can deploy the vdb.
The JDBC, File, WebServices (WS), and Saleforce translators are able to supply their own metadata. Only the JDBC connector has import settings (see below).
JDBC Importer Settings
| Name | Description | Default |
|---|---|---|
| importer.catalog | See DatabaseMetaData.getTables | null |
| importer.schemaPattern | See DatabaseMetaData.getTables | null |
| importer.tableNamePattern | See DatabaseMetaData.getTables | null |
| importer.procedureNamePattern | See DatabaseMetaData.getProcedures | null |
| importer.tableTypes | Comma separated list - without spaces - of imported table types. See DatabaseMetaData.getTables | null |
| importer.useFullSchemaName | When false, directs the importer to drop the source catalog/schema from the Teiid object name, so that the Teiid fully qualified name will be in the form of <model name>.<table name> - Note: that this may lead to objects with duplicate names when importing from multiple schemas, which results in an exception. | true |
| importer.importKeys | true to import primary and foriegn keys | true |
| importer.importIndexes | true to import index/unique key/cardinality information | true |
| importer.importApproximateIndexes | true to import approximate index information. See DatabaseMetaData.getIndexInfo | true |
| importer.importProcedures | true to import procedures and procedure columns; see Known Issues | true |
| importer.widenUnsignedTypes | (7.0+) true to convert unsigned types to the next widest type. For example SQL Server reports tinyint as an unsigned type. With this option enabled, tinyint would be imported as a short instead of a byte. | true |
| importer.quoteNameInSource | (7.0+) false will override the default and direct Teiid to create source queries using unquoted identifiers. | true |
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 import by schemaPattern and tableTypes, i.e.:
...
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.schemaPattern" value="my-schema"/>
...
Salesforce
The saleforce connector can also directly import metadata based upon the connection information. There are currently no import settings.
WS Connector
Exposes an invoke stored procedure for calling web services - see the weather example.
File Connector
Exposes stored procedures for accessing files as blobs or clobs - see the dynamic portfolio example that accesses text files for use with the TEXTTABLE function.
Migrating from 6.x
Dynamic VDBs from prior release are based upon a file format that is no longer supported. You can use the migration utility (bin/migrate.sh or bin/migrate.bat) supplied with the AdminShell download to update your dynamic vdb file for use with Teiid 7. Note - additional manual edits may be needed to the generated {vdb}-bindings-ds.xml file if you are using custom or xml connectors.
Known Issues
- Procedure result set columns do not import correctly from many source types, since they are not properly represented in DatabaseMetaData.
- Overloaded procedures are currently not supported. See also TEIID-796
Comments