In this tutorial a Virtual DataBase (VDB) is created from a text file using Teiid (more information on: http://www.jboss.org/teiid/). Any application could fetch the data from the VDB, executing SQL queries through a JDBC connection. The data is actually coming from the text file and even if it changes the VDB will fetch the new data in real time.
Before starting this tutorial you have to install Teiid (on a JBoss Server) and Teiid Designer (Eclipse + plugin). Below you will find more information:
Previously you have to download:
- Install Java 1.6
- Unzip the JBoss AS contents to your workspace. We will refer this location as your JBOSS_HOME path.
- Unzip the Teiid Runtime contents under JBoss AS install directory. The Teiid directory structure matches the JBoss AS structure. Therefore, Teiid is just an overlay and install it directly over the JBoss AS installed directory.
- Include the Teiid JDBC Driver under JBOSS_HOME/standalone/deployments
Install Teiid Designer
Download and install Teiid Designer from https://www.jboss.org/teiiddesigner/downloads. You will find the install instructions on the referenced site.
Tutorial: Create VDB from text file
Once Teiid and Teiid Designer have been installed, let’s see how to use Teiid Designer to create the VDB.
- Create a new Teiid Model project and set the name for the project (in our example: “DemoTextFile”), right click on the root and follow as shown below:
- Then include a text file in the folder of the project. The text file contains the data following a structure (CSV for example), in our case we have created the file “data.txt” containing:
letter,number a,1 b,2 c,3
- Let’s build the model which will access the text file. Create a new “Teiid Metadata Model” as shown below:
- After the creation of the previous model, switch to the “Database Development” view to create the connection profile. In this example it has been named as “TextFileDataConnection”:
- Different options are available for the “file style”, in this example CSV has been selected:
- Then, the connection profile has to be assigned to the model previously created. Right click on the model and then:
- Now let’s create the specific View for our model as shown:
- Right click on the view “TextFileView” to create the “virtual table”:
- In this example the table “MainTable” has been created with the following parameters:
- Specify the name and format of the columns as they will be fetched from the text file, in this case, “letter” and “number”:
- Set the SQL sentence to retrieve the data from the text file, in this example the following SQL has been set: SELECT A.letter, A.number FROM (EXEC TextFileModel.getTextFiles(‘data.txt’)) AS f, TEXTTABLE(f.file COLUMNS letter string, number string HEADER) AS A
- Now we can preview the data for “MainTable”:
- Next, let’s create the virtual database, right click on the project name and choose the model and view created previously:
- Last step, it’s to deploy the VDB on our Teiid Server, right click on the VDB file and click on “Modelling->Execute VDB”:
- Finally let’s fetch the data from a SQL client. Don’t forget to add the Teiid-JDBC-Driver. You can find below an example of the database URL:
- If you execute a select statement it will fetch the data from the text file through the VDB as shown:
The interesting thing about Teiid is that different data sources (flatfiles, web services, databases) could be integrated in the same VDB building one federated database.
Thanks for visiting semoru.com