JBoss Teiid quickstart tutorial

Teiid is a data virtualization platform, which can be used to integrate data from multiple datasource like RDBMS, Web services, Plain text files, EXCEL, No SQL databases and more, providing a single view of the data. Teiid is typically executed inside JBoss EAP platform, but can also be run in embedded mode. The enterprise version of Teiid is called JBoss Data Virtualization platform. The notions we will learn from this tutorial apply both to Teiid and the Red Hat’s JBoss Data Virtualization platform.

Start by downloading Teiid (http://teiid.jboss.org/downloads/). Unzip the downloaded file which contains a JBoss EAP 6 installation within it. The Teiid 8.11 directory structure matches the one of the application server directly, including a specific Teiid configuration named standalone-teiid.xml with the teiid subsystemm within it.

In standalone mode, you can execute the following command to start Teiid:

JBOSS_HOME/bin/standalone.sh -c standalone-teiid.xml

Creating our first Virtual Database definition

A Virtual Database definition (VDB) is the primary means to define a connection towards a set of data. You have mainly two options to create a VDB:

  • Use a Dynamic VDB. This is defined using a simple XML file which is then deploy it into the application server. 
  • You can create it with the Teiid Designer – http://www.jboss.org/teiiddesigner/ . This Eclipse-based tool lets define source models but also allows you to define relational and XML views on top of those sources.

We will follow the former approach in this tutorial, in the next one we will cover the graphical approach. Our VDB will abstract a connection towards a MySQL database. Start by defining a datasource, here is a sample CLI script:

module add --name=com.mysql --resources=/home/jboss/Downloads/mysql-connector-java-5.1.24-bin.jar --dependencies=javax.api,javax.transaction.api

/subsystem=datasources/jdbc-driver=mysql:add(driver-name="mysql",driver-module-name="com.mysql",driver-class-name=com.mysql.jdbc.Driver)

data-source add --jndi-name=java:/MySqlDS --name=MySqlPool --connection-url=jdbc:mysql://localhost:3306/as7development --driver-name=mysql --user-name=jboss --password=jboss

 Now let’s create the sample-vdb.xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="Quickstart" version="1">

    <description>A Dynamic VDB</description>
 
    <property name="UseConnectorMetadata" value="true" />

    <model name="Accounts">
 
        <property name="importer.useFullSchemaName" value="false"/>
 
        <source name="jdbc-connector" translator-name="mysql" connection-jndi-name="java:/MySqlDS"/>
    </model>
  
</vdb>

As you can see, this file contains a model which is an abstraction toward a connection. The source of data is the mysql datasource. Check that the connection-jndi-name matches with the one you have defined.

Within the source we are using a Translator.

A Translator provides a abstraction layer between Teiid Query Engine and physical data source; It is smart enough  to convert the result data that came from the physical source into a form that Teiid Query engine is expecting.

Now copy the files in the deployments folder of the application server and check from the logs that the VDB is active:

11:05:45,143 INFO  [org.teiid.CONNECTOR] (teiid-async-threads - 1)  MySQLExecutionFactory Commit=true;DatabaseProductName=MySQL;DatabaseProductVersion=5.1.58-1ubuntu1;DriverMajorVersion=5;DriverMajorVersion=1;DriverName=MySQL-AB JDBC Driver;DriverVersion=mysql-connector-java-5.1.24 ( Revision: ${bzr.revision-id} );IsolationLevel=2
11:05:45,178 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 1)  TEIID50030 VDB Quickstart.1 model "Accounts" metadata loaded. End Time: 05/08/15 11:05
11:05:45,185 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (teiid-async-threads - 1)  TEIID40003 VDB Quickstart.1 is set to ACTIVE

In order to test our model, we can use a sample client which is available in the teiid examples quickstart. The sampleclient contains a JDBCClient.java example code that shows a developer the basic connection logic that can be used to connect to a Teiid instance running in a JBoss AS server.

To execute a sql query using the simpleclient, use the following format:

mvn install -Dvdb="<vdb>" -Dsql="<sql>"

 So, for example, assumed that a Customer table is available on the MySQL Database:

mysql> select * from customer;
+----+-------+---------+-------------------+
| id | name  | surname | phone             |
+----+-------+---------+-------------------+
|  1 | john  | smith   | 320- 12345678912  |
|  2 | willy | wonka   | 339- 78946576764  |
|  3 | steve | brooks  | 349- 43987493444  |
+----+-------+---------+-------------------+
3 rows in set (0.02 sec)

 Then you can query it this way using the Sample Client:

mvn install -Dvdb="Quickstart" -Dsql="select * from customer"

The expected output is:

Executing using the TeiidDataSource
Results
1: 1,john,smith,320- 12345678912
2: 2,willy,wonka,339- 78946576764
3: 3,steve,brooks,349- 43987493444

 Creating an excel Virtual Database definition

The beauty of this framework is that we are not limited to relational databases when defining a VDB but we can use all available datasources which have a translator available in the configuration. So our next attempt will be with an Excel Stylesheet. Here is the excel file we are going to include as datasource:

jboss teiid data virtualization tutorial

When using an Excel file as source of data we will not rely on a standard datasource as we did with Mysql; rather we will rely on a resource adatper which contains the required connection definitions, including the JNDI binding and the folder where Excel files are scanned:

<resource-adapter id="fileQSExcel">
	<module slot="main" id="org.jboss.teiid.resource-adapter.file"/>
	<connection-definitions>
		<connection-definition class-name="org.teiid.resource.adapter.file.FileManagedConnectionFactory" jndi-name="java:/excel-file" enabled="true" use-java-context="true" pool-name="fileDS">
			<config-property name="ParentDirectory">
				${jboss.home.dir}/teiidfiles/excelFiles/
			</config-property>
			<config-property name="AllowParentPaths">
				true
			</config-property>
		</connection-definition>
	</connection-definitions>
</resource-adapter>

 And here is the updated VDB which now includes a model named “excel”:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="Quickstart" version="1">

    <description>A Dynamic VDB</description>
 
    <property name="UseConnectorMetadata" value="true" />

	<model name="excel">  
        <property name="importer.headerRowNumber" value="1"/>  
        <property name="importer.ExcelFileName" value="orders.xlsx"/>  
        <source name="connector" translator-name="excel"  connection-jndi-name="java:/excel-file"/>  
    </model> 
	
    <model name="Accounts">
 
        <property name="importer.useFullSchemaName" value="false"/>
 
        <source name="jdbc-connector" translator-name="mysql" connection-jndi-name="java:/MySqlDS"/>
    </model>
  
</vdb>

Check that the file named orders.xlsx has been copied into ${jboss.home.dir}/teiidfiles/excelFiles/ and save the updated VDB file.

Testing the excel model

Much the same way as for the Mysql model, we can check it with the sampleclient application. In this case, we will use the “Sheet1” definition in order to query for data contained in the Excel sheet named (indeed) Sheet1:

mvn install -Dvdb="Customer" -Dsql="SELECT * FROM excel.Sheet1"
-----------------------------------
Executing using the TeiidDataSource
Results
1: 2,1.0,Bike,700.0
2: 3,2.0,Notebook,600.0
3: 4,1.0,Mobile,250.0
4: 5,3.0,Chair,50.0
5: 6,2.0,Refrigerator,550.0
6: 7,3.0,Microwave,100.0

Joining models

Perhaps the most interesting scenario, where you can see the raw power of Teiid, is to create a virtual data definition using the two models composed so far. The purpose of it, will be joining the customer’s data (held in Mysql) with their orders (held in the Excel file).

jboss teiid data virtualization tutorial

Let’s go directly to the point: here is the updated VDB which features the model named “join”:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="Quickstart" version="1">

    <description>A Dynamic VDB</description>
 
    <property name="UseConnectorMetadata" value="true" />

	    <model name="join" type="VIRTUAL">
    	<metadata type="DDL"><![CDATA[
    	
    	    CREATE VIEW customerorders (
            name string,
            surname string,
            phone string,
			item string,
            price double
            )
            AS  
			  SELECT B.name, B.surname, B.phone, A.item, A.price 
			  FROM excel.Sheet1 as A,
			  Accounts.customer AS B
			    where A.id = B.id
              
     	]]>
   		</metadata>
	</model>  
	
	<model name="excel">  
        <property name="importer.headerRowNumber" value="1"/>  
        <property name="importer.ExcelFileName" value="orders.xlsx"/>  
        <source name="connector" translator-name="excel"  connection-jndi-name="java:/excel-file"/>  
    </model> 
	
    <model name="Accounts">
 
        <property name="importer.useFullSchemaName" value="false"/>
 
        <source name="jdbc-connector" translator-name="mysql" connection-jndi-name="java:/MySqlDS"/>
    </model>
  
</vdb>

As you can see, we have added a View model to our VDB: View models represent the structure and characteristics of abstract structures you want to expose to your applications. The content of our query is defined in a Metadata section. We are making a join between the excel.Sheet1 and the customer table.

Run the sample against the virtual customerorders:

mvn install -Dvdb="Customer" -Dsql="select * from customerorders"
Executing using the TeiidDataSource
Results
1: john,smith,320- 12345678912,Bike,700.0
2: willy,wonka,339- 78946576764,Notebook,600.0
3: john,smith,320- 12345678912,Mobile,250.0
4: steve,brooks,349- 43987493444,Chair,50.0
5: willy,wonka,339- 78946576764,Refrigerator,550.0
6: steve,brooks,349- 43987493444,Microwave,100.0

Cool, isn’t it? In the next tutorial we will use the graphical designer to define our VDB. Keep in touch and have fun with JBoss Data Virtualization

Found the article helpful? if so please follow us on Socials