BookFederationTutorial_Part2

Data Federation (Part 2)

Table of Contents Data Federation (Part 2) Create Relational Metadata Model Create Data Source Extend the VDB Redeploy the VDB Query the VDB Create a view (Optional) Working with OBDA Plugin Resources References

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 in a dedicated folder of your file system. It is important to create a new folder for this purpose. And indeed, you may have more than one files there. However, all the text files in the same folder must have the same columns structure.

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. 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. Click on the "Add model" icon (bottom left of the main window).
3. Locate "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" if needed (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 > Show View > Other... in the main menu and then select "Database Management > Data Source Explorer".
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 model: File > New > Teiid Metadata Model.
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" in the Model Explorer, right-click and select the New Child > Table action. Name the table "tb_prices".
5. Select the "Column" tab and click "Add" (or alternatively, select the "tb_prices" table in the Model Explorer, right-click and select the New Child > Column action).

"Create three new columns:

1. • 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

1. Save the view.
2. Add the view to "bookselling.vdb" and redeploy it.
3. Now, do the query again but this time type:

select * from tb_prices

You should get the same result as previously.

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.zip: contains an example work.

References

1. Community Wiki: Text to Table with Teiid