Teiid Connector Supplied Metadata

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

NameDescription
VDBInfo
NameThe 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.
VersionThe 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.
UseConnectorMetadataSetting 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.
NameThe 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
NameThe 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.

ComponentTypeThe 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
NameDescriptionDefault
importer.catalogSee DatabaseMetaData.getTablesnull
importer.schemaPatternSee DatabaseMetaData.getTablesnull
importer.tableNamePatternSee DatabaseMetaData.getTablesnull
importer.procedureNamePatternSee DatabaseMetaData.getProceduresnull
importer.tableTypesComma separated list - without spaces - of imported table types.  See DatabaseMetaData.getTablesnull
importer.useFullSchemaNameWhen 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.importKeystrue to import primary and foriegn keystrue
importer.importIndexestrue to import index/unique key/cardinality informationtrue
importer.importApproximateIndexestrue to import approximate index information.  See DatabaseMetaData.getIndexInfotrue
importer.importProcedurestrue to import procedures and procedure columns; see Known Issuestrue
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:
stringbigintegertimeclob
integerbigdecimaldateblob
floatbytetimestampxml
doublebooleancharobject
longshort

 

Known Issues

  1. Procedure result set columns do not import correctly from many source types, since they are not properly represented in DatabaseMetaData.
  2. Overloaded procedures are currently not supported.  See also TEIID-796.