Chapter 2. Example Explained

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

Note

Derby is used here since it is Open Source, easily obtained, and light-weight. You can substitute any other relational database, as long as you have a suitable JDBC driver. The schema file provided, and described below, is specific to Derby, but can be easily converted for use with other databases.

2.2. Create the Relational Database's schema and load the sample data

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/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.

2.3. Describe the CSV file and its contents

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

Data File: Each data file contains column information for the table. The column information is typically defined on line 1 as header line in the file, and all the following lines contain the actual rows of data. Each single line corresponds to single row. A portion of the sample file is shown below. The complete sample file is "examples/portfolio/marketdata-price.txt".
```
           SYMBOL,PRICE
           IBM,83.46
           RHT,11.84
           BA, 44.58
           ORCL,17.37           
                 
        
```

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