Teiid - Scalable Information Integration

The investor's portfolio information is stored in a Derby database and "current" stock prices are stored in a delimited text file. When the VDB is completed, a single query will cause Teiid to access the relational and non-relational sources, calculate the portfolio values, and return the results.

Figure 2.1. Various Steps involved in creating the example

This example is written using "Derby" as the relational database. Download and install Derby on your machine. An existing local or remote instance can be used if it exists.

We need to start the Derby RDBMS and create the "accounts" database with the below schema. These commands are intended for a Linux environment. For starting the Derby instance on another platform, you will need to use commands appropriate to that platform.

Start a terminal session, and change directory to where Derby is installed and execute following commands

export DERBY_HOME=`pwd`
./bin/startNetworkServer        
    

This starts the Derby in network mode. Now, start another terminal and we will use Derby''s 'ij' tool (like SQL*PLus for Oracle) to create the schema, using the "customer-schema.sql" file in "examples/dynamicvdb-portfolio" directory

export DERBY_HOME=`pwd`
./bin/ij /path/to/customer-schema.sql        
    

This will create the accounts schema. It's abbreviated ddl is shown below.

--Contains the name and address of a Customer who owns portfolio account 
CREATE TABLE CUSTOMER
...

--Contains Customer's account number and its current status
CREATE TABLE ACCOUNT
...
          
--Contains information about stock symbol, company name etc.
CREATE TABLE PRODUCT
...

--Contains each Account's holdings of Stocks   
CREATE TABLE HOLDINGS
...

Make sure you did not have any issues when creating the schema as it is needed for going forward in this example. You can use 'ij' tool to verify the tables were created. As an alternative, you may use other tools like SQuirreL , or Eclipse's Data Tools plugin to connect to the Derby instance to check the schema and sample data.

In order to use a Text file as the source, we need a data file defines data inside a table

Just locate the sample data files provided or create your own data files. Now, both our sources are ready.

This XML file is defines a set of sources that need to treated as single source by the client application. Dynamic VDB does not yet allow for the creation of view layers. Below XML file defines "dynamicvdb-portfolio" example vdb.

portfolio-vdb.xml (copy available in "teiid-examples/dynamicvdb-portfolio" directory)

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="DynamicPortfolio" version="1">

    <description>A Dynamic VDB</description>
    
    <!-- 
      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 deploy/<vdb name>/<vdb version/META-INF directory
    -->
    <property name="UseConnectorMetadata" value="cached" />


    <!-- 
      Each model represents a access to one or more sources.
      The name of the model will be used as a top level schema name
      for all of the metadata imported from the connector.
    
      NOTE: Multiple model, with different import settings, can be bound to 
      the same connector binding and will be treated as the same source at
      runtime. 
    --> 
    <model name="MarketData">
        <!-- 
            Each source represents a translator and data source. There are 
            pre-defined translators, or you can create one. ConnectionFactories 
            or DataSources in JBoss AS they are typically defined using "xxx-ds.xml" files. 
        -->
        <source name="text-connector" translator-name="file" connection-jndi-name="java:marketdata-file"/>
    </model>

    <model name="Accounts">
        <!-- 
          JDBC Import settings 
          
          importer.useFullSchemaName directs the importer to drop the source 
          schema from the Teiid object name, so that the Teiid fully qualified name
          will be in the form of <model name>.<table name>
        -->
        <property name="importer.useFullSchemaName" value="false"/>
           
         <!--
            This connector is defined in the "derby-connector-ds.xml" 
          -->
        <source name="derby-connector" translator-name="derby" connection-jndi-name="java:PortfolioDS"/>
    </model>

</vdb>        
    

The XML file is explained below.

Before you can make a JDBC connection to the Teiid VDB, add the Teiid's driver jar file to your application's classpath

${jboss-install}/server/${profile}/lib/teiid-7.3-client.jar

For a Java application to connect to a JDBC source, it needs a URL, user-id, and password. To connect to your VDB all you need is a URL and any additional optional properties that you would like to set. Teiid defaults to allowing the "user" as user with password as "user". Additional user accounts can be added. A JDBC connection can be obtained through the Teiid driver "org.teiid.jdbc.TeiidDriver" with the URL syntax of

jdbc:teiid:<VDB-Name>@mm(s)://<host name>:<port>

You can add optional properties at the end of the URL using a semi-colon(;) name=value format. For example

jdbc:teiid:<VDB-Name>@mm(s)://<host name>:<port>;autoCommitTxn=DETECT

Check out Client Developer's guide for all the optional connection properties in your URL. Here is sample code showing how to make JDBC connection.

public void execute() throws SQLException {
    String url = "jdbc:teiid:Portfolio@mm://localhost:31000";
    String sql = "select firstname, lastname from customer";
    
    Class.forName("org.teiid.jdbc.TeiidDriver");
    
    Connection connection;
    try{
        connection = DriverManager.getConnection(url, "user", "user");
        Statement statement = connection.createStatement();
        ResultSet results = statement.executeQuery(sql);
        while(results.next()) {
          System.out.println(results.getString(1));
          System.out.println(results.getString(2));
          ...
        }
        results.close();
        statement.close();
    } catch (SQLException e){
        e.printStackTrace();
        throw e;
    } finally {
        try{
          connection.close();
        }catch(SQLException e1){
          // ignore
        }              
    }
}

You can also use org.teiid.jdbc.TeiidDataSource to make connection in your Java application. For example, you can use following code fragment to make a connection to the VDB and issuing the query exactly same as in the above example

TeiidDataSource ds = new TeiidDataSource(); 
ds.setDatabaseName("Portfolio");
ds.setUser("user");
ds.setPassword("user");

Connection connection = ds.getConnection();
...

TeiidDataSource source also provides an option to set optional parameters using the "set" methods on the data source look. For all the allowable data source properties check out Client Developer's Guide.

The Teiid installation includes a simple Java class which demonstrates JDBC access of the deployed VDB. To execute this demonstration, follow these steps:

Depending on the VDB you deployed, see the relevant README file for example queries. If you are using a graphical client, such as SQuirreL, have a look at the metadata tree to see not only what is exposed by your VDB, but also the SYS schema tables.

If your application is Web based, you can create data source for your VDB using the above and treat it as any other JDBC source using org.teiid.jdbc.TeiidDataSource and assigning it a JNDI name. Refer to Client Developer's Guide deployment for more information on creating a DataSource.