wiki:BookFederationTutorial_Part2

Data Federation (Part 2)

This tutorial continues the first part of the two series of BookFederation tutorial. Previously, we have shown you how to integrate two databases and create a virtual database (VDB) and make it cooperate with the OBDA plugin. In this tutorial, we are going to extend the VDB by adding another data source coming from a text file. We are going to add prices to the books that are sold by the store. And the price list is saved in a CSV file as an external source.

Before we start, download the text file for this tutorial and save it to your file system (e.g., JBOSS_PATH/server/default/data/teiid/bookprices/). It is important to create a new folder to keep the text file. And indeed, you may have more than one files there. However, all the text files must have the same columns structure in one directory.

Create Relational Metadata Model

These steps will show you how to create a metadata model for the text file using Teiid Designer plugin in Eclipse. Notice that we are not going to use the "Import" action as in the first tutorial but instead we are going to use the "New" action.

  1. Select File > New > Teiid Metadata Model action from the main menu.
  2. Fill the parameters:
    • Model Name = "bookprices"
    • Model Class = "Relational"
    • Model Type = "Source Model"
    • Model Builder = "Generate File Translator Procedures"
  3. Select Next.
  4. Accept all the options and select Finish. You should see a new metadata model "bookprices.xmi" in the Model Explorer.
  5. Save it.

Create Data Source

  1. Select "bookprices.xmi" in the Model Explorer.
  2. Do a right click and then select Modelling > Set Connection Profile.
  3. Select New....

  1. Select "Flat File Data Source" and type "bookprices" in the Name field.
  2. Locate the directory of the text file (e.g., JBOSS_PATH/server/default/data/teiid/bookprices/). Select Finish to go back to the parent window.
  3. Select OK to close the window.
  4. Again, do a right-click and select Modelling > Create Data Source.
  5. Type "bookprices-file" in the Data Source Name field.
  6. Select Use Model Connection Info option and select "bookprices" as the Source Model (we created this in step 1-5).
  7. Go to the JBOSS_PATH/server/default/deploy directory and you should see a new file "bookprices-file-ds.xml" has been created. Or, go to Teiid View panel and select Reconnect. A new entry "bookprices-file" will appear in the Data Sources folder.

Extend the VDB

After we have the metadata and deploy the data source, it's time to extend our VDB.

  1. Open "bookselling.vdb" in the Model Explorer.
  2. Select the Add icon.
  3. Locate the "bookprices.xmi" in the Project Explorer. Select OK and a new row entry is added to the VDB.
  4. Make sure the Translator is "file" and rename the JNDI Name to "bookprices-file" (this should follow the data source name).
  5. Save the VDB.

Redeploy the VDB

Before we start redeploying the new VDB, we need to activate the file connection (it doesn't go automatically as in JDBC case).

  1. Go to "Database Designer" perspective. If you don't see this perspective at the corner top-right, select Window > Open Perspective > Other... in the main menu and then select "Database Development". Select OK.
  2. Select ODA Data Sources > Flat File Data Source > bookprices in the Data Source Explorer. Do a right-click and select Connect.

Now we are ready to redeploy the VDB.

  1. Select "bookselling.vdb" in the Model Explorer.
  2. Do a right-click and select Modelling > Execute VDB. Note: If any error message appears, try to undeploy the old VDB and repeat these steps.

Query the VDB

Once the deployment succeed, Eclipse will automatically switch to "Database Development" perspective. And similar to the first tutorial, select Open SQL Scrapbook and enter this example query:

select price.* from (call bookprices.getTextFiles('*.csv')) f, 
TEXTTABLE(f.file COLUMNS book integer, price double HEADER) price

Execute the query and you should get the result as shown in the image below.

Create a view (Optional)

If you observe the query string, it is cluttered with the data organization inside the file. We can hide this information and simplify the query by creating a view in the VDB.

  1. Create a new metadata: File > New > Teiid Metadata Model.
  2. 2. Fill the parameters:
    • Model Name = "bookprices_view"
    • Model Class = "Relational"
    • Model Type = "View Model"
  3. Select Finish and save it.
  4. Select the new metadata "bookprices_view.xmi" and select New Child > Base Table action. Name the table "tb_prices".
  5. Create three new columns: New Child > Column
    • bk_code:int
    • bk_price:double

  1. Double-click the table to open the "Transformation Editor" and type:
    SELECT
      price.book AS bk_code, price.price AS bk_price
    FROM
      (EXEC bookprices.getTextFiles('*.csv')) AS f, TEXTTABLE(f.file COLUMNS book integer, price double HEADER) AS price
    
  2. Save the view.
  3. Add the view to "bookselling.vdb" and redeploy it.
  4. Now, do the query again but this time type:
    select * from tb_prices
    
    You should get the same result as in the previous.

Working with OBDA Plugin

We are going to leave this part as your exercise :) Here's a todo list to remind you:

  1. Extend the ontology. Hint: You just need to add one data properties called "price".
  2. Develop some new mappings.
  3. Create the queries.

Check the Resources section below to compare your work.

Resources

BookFederation-part2_Final.zip: contains an example work.

References

  1.  Community Wiki: Text to Table with Teiid

Attachments