Home > Databases > How to create a virtual database from a text file using Teiid

How to create a virtual database from a text file using Teiid

Share on TwitterSubmit to reddit

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:

Install Teiid

Previously you have to download:

To install Teiid follow these steps:
  1. Install Java 1.6
  2. Unzip the JBoss AS contents to your workspace. We will refer this location as your JBOSS_HOME path.
  3. 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.
  4. 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

 

Share on TwitterSubmit to reddit

8 thoughts on “How to create a virtual database from a text file using Teiid

  1. Thank you for the tutorial. However I have a problem with the step “Preview Data”. I receive an error: “A Teiid instance connection mm://localhost:9999 cannot be made. Check to insure this server instance is running.”

    Any ideas?

      • Good tutorial.
        I am able to query the vdb getting the right results but I am not able to “Preview Data”. I get this error:

        null TEIID40011 Processing exception ‘TEIID50072 The username “user” and/or password and/or payload token could not be authenticated by any membership domain.’ for session null. Exception type org.teiid.client.security.LogonException ….

  2. Nice tutorial.
    I’m not able to connect to the Teiid Server Instance. I am starting the server at local host, but not able to preview data and connect to JDBC.

      • I’m able to connect to the Teiid Server instance. But now i’m getting the following error when tried to Preview Data.
        org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidProcessingException: TEIID30504 TextFileView: TEIID30481 Failed to find the Connection Factory with JNDI name PREVIEW_e2f7a349-b973-4027-a016-425e2e14b403_DemoTextFile_TextFileView. Please check the name or deploy the Connection Factory with specified name.

      • When i try to preview data i get the following error.

        org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.api.exception.query.QueryPlannerException: TEIID30352 SOURCE.GETTEXTFILES does not exist.

        May i know what mistake i am doing. Please help

Leave a Reply

Your email address will not be published. Required fields are marked *

*


7 × eight =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>