Version 14

    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

    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.