WARNING this article is out of date - please refer to more recent documentation.
Starting with Teiid 6.2 the JDBC and Text connectors can provide their metadata to the query engine. This allows for simple usage of Teiid integration by building "Dynamic VDB" without the Designer tool. 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 example/vdbless-portfolio for a working example.
To build a "dynamic vdb" to utilize this feature you'll need to create a "<some name>.def" file under the "<teiid-install>/deploy" directory. The .def file format is simple xml that captures information about how the VDB should be accessed, the source models/import settings, and what VDB specific connector bindings (if any) the models are bound to. For example purposes The Quick Start Example is written using this feature here.
.def file format: (Currently there is no xml schema defined)
<VDB> <VDBInfo> <Property Name="Name" Value="..." /> <Property Name="Version" Value="..."/> <Property Name="UseConnectorMetaData" Value="..."/> </VDBInfo> <!-- define a model for each source --> <Model> <Property Name="Name" Value="..." /> ... <ConnectorBindings> <Connector Name="..." /> </ConnectorBindings> </Model> ... <ConnectorBindings> <Connector Name="..." ComponentType="..."> <Properties> <Property Name="...">...</Property> ... </Properties> </Connector> ... </ConnectorBindings> </VDB>
Property Definitions
| Name | Description |
|---|---|
| VDBInfo | |
| Name | The name of the VDB. This determines the deployed directory location <Teiid Home>/deploy/<VDB name>/<VDB version>, and how the VDB should be reference through the driver or datasource. |
| 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. If changes are directly made to the .def file in the deploy directory, the version number should be incremented (or the previous VDB revision removed) to trigger a new deployment. |
| 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 <teiid-install>/deploy/<vdb name>/<vdb version>/META-INF directory |
| Model - Each model represents a schema with one or more connector bindings. | |
| 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. |
| importer.<property name> | 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. |
| Model/ConnectorBindings/ConnectorBinding - represents a reference to a Connector | |
| Name | The name of the connector binding to use for this model. The connector binding definition may be in the ConnectorBindings section of the this .def file (recommended) or it may be in the <teiid-install>/deploy/configuration.xml file, which can also be updated through the AdminAPI. |
| ConnectorBindings/Connector - Represents a physical data source | |
| Name | The unique name of the connector binding. |
| ComponentType | The name of the component type. Available component types are defined in the <teiid-install>/deploy/configuration.xml file. The component type defines what properties can be specified for a given connector binding under the Properties element. The sample XML fragments with required properties and all the supported ComponentTypes are also specified here. |
Note: Once the <teiid-install>/deploy/<some name>.def file is in a deployed state, with a <teiid-install>/deploy/<vdb name>/<vdb version> diretory, further changes to <some name>.def will not modify the deployed instance. Changes to the deployed instance must be made through the AdminAPI or to the <teiid-install>/deploy/<vdb name>/<vdbConfigurationInfo.def version>/ file.
Currently only the JDBC and Text connector are able to supply their own metadata. Also only the JDBC connector has import settings (see below).
JDBC Connector
All the data sources that can be accessed through using JDBC driver can be categorized as the JDBC Connector. A list of currently supported JDBC Connectors can found here.
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"/>
...
Text Connector
Text Connector Metadata
For the Text connector to supply metadata, additional information must be added to the text descriptor file. For each Table the column names and optionally the column types should be specified. For example, in the "portfolio" example, the Price table in text descriptor file "marketdata-def.txt" file defined as
MarketData.Price.location = marketdata-price.txt MarketData.Price.delimiter = , MarketData.Price.headerLine = 1 MarketData.Price.skipHeaderLines = 1 MarketData.Price.columns=SYMBOL,PRICE MarketData.Price.types=string,bigdecimal
Note that the column names should be specified in the same order the columns appear in the text file. If types are not specified, the "string" type will be assumed. The below are all the data types supported:
Text Connector Data Types:
| string | biginteger | time | clob |
| integer | bigdecimal | date | blob |
| float | byte | timestamp | xml |
| double | boolean | char | object |
| long | short |
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