Introduction to JBoss Teiid

This is the second year we have been using JBoss Teiid in our product. Accumulated experience, made and taken into account many errors. There are no regrets in the choice made. There was a desire to share the collected information, I hope someone will come in handy.

In a nutshell, JBoss Teiid is a federated database [2]. Well or so - this is a unifying relational superstructure over multiple data sources. From the user's point of view, such a database looks like a unified scheme in which tables, views, and procedures are external data objects. We can also say that external data is translated into a single virtual database.

The basics

This is what JBoss Teiid [1] looks like from the inside (according to official documentation [3]):
')


A simple example: take an honest relational database such as Oracle, mySQL or H2 (one or all three at once). We combine with our favorite web service, add some data in CSV text files, add salt and bring to a boil, expand the resulting configuration in Teiid under JBoss. Join SQL client. The most interesting thing is that now we can build SQL queries that will be executed with combined resources.
It is quite clear that some data sources may have certain properties, for example, they do not support transactions or changes (data is read-only). The built-in optimizer Teiid takes on disassembly with the supported properties of each such source, using an abstraction with the mysterious name "translator". Currently, translators are supported out of the box for:

• JDBC (Oracle, MS SQL Server, IBM DB2, MySQL, Postgres, Derby, Sybase, H2, HSQL, Netezza, Teradata, Modeshape. Almost everything that can be accessed via JDBC, such as Excel via jdbc-odbc or Hive Hadoop. There are also ODBC support. Understands Hibernate as much as possible (dynamic DDL generation for table generation is not supported);
• Files on the file system (CSV, XML);
• Web services;
• Access to directories via LDAP;
• SalesForce, OLAP;

The client connection to Teiid is via JDBC (specification 4.0 is implemented almost completely). It must be remembered that the client application must use Java 1.6 JDK and higher, previous versions of Java are not supported.
A little about actually creating a virtual database (VDB file). There are two ways: you can create a database using a special application designer (built on the basis of Eclipse), or completely dynamically. The second option answered our tasks more, therefore we used it.
Below are some details about the practical work with JBoss Teiid.

Details

1. Install Teiid
Currently, Teiid has two active versions: 8.0 (in development, designed to work with JBoss 7) and 7.7 (we use it together with JBoss AS 5.1.0, the plans for switching to the eight this summer).
The installation consists in downloading the Teiid archive with the latest version and expanding over the container installed by JBoss. Teiid 7.7 needs a “default” JBoss 5.1.0 profile. We have slightly optimized this profile (basically eliminated unused services), but this step is not necessary.


2. Client Application and Virtual Database
The Teiid kit includes several examples that illustrate well the features of the work:

• “Dynamicvdb-portfolio” - an example combines text files with data and an HSQL database;
• “Dynamicvdb-ws-weather” - makes it clear how to wrap a web service call in SQL;
• “Simpleclient” is a simple java client application that connects to a running Teiid and executes the specified SQL. For these purposes, it seems more practical to use specialized clients (SQuirreL or Eclipse Database Developer);

3. Features of using Teiid in our project
It turned out that under the conditions it was necessary to combine completely diverse things - relational bases (IBM DB2 and H2), many small files (CSV), as well as legacy data sources developed long before the advent of JDBC and the Java language (z / OS if anyone) that's interesting). The required was achieved by writing their own translators to such sources. The task turned out to be a trivial, self-made translator - no more than a couple of new java classes that extend the existing classes provided by Teiid.
The task was complicated by the requirement to dynamically determine the presence and absence of various data sources and rebuild the Virtual Base without rebooting the server. I had to abandon the Designer and build the vdb file using the Teiid Admin API at the initial start of the application. If the “picture of the world” of a running application becomes different, some data sources change - a new version of VDB is programmatically created and deployed instead of the old one on the fly.
Many steps have been taken to optimize memory usage and CPU usage. Teiid has a flexible cache that helped us a lot. Query Optimizer is also a very useful thing - a detailed execution plan is printed in the logs and makes it clear which parts of the query are executed inefficiently.

Afterword

This article was intended as an introductory one. If you are interested in individual moments, you can drill down, well, or add more details from the experience.
For example, Teiid 8 under JBoss 7 promises to be quite nimble and minimally voracious. Unfortunately, it is still damp in places, but it is being finalized at an accelerated pace. By the way, I can say that the professionalism of the team supporting Teiid causes only pleasant emotions. In many cases, the problems found were corrected the day after the opening of the ticket in Jira. Questions on the forum are answered within a few hours. Open source project, supported by JBoss, the guys are obviously trying, we are pleased with the cooperation.


[1] JBoss: The Teiid Project
[2] Wikipedia: Federated Database System
[3] Tao of Teiid: Technical Introduction to Teiid