Introduction to JBoss Teiid
This is the second year that we have used JBoss Teiid in our product. Experience has been gained, many mistakes made and taken into account. There are no regrets in the choice made. There was a desire to share the collected information, I hope someone comes in handy. In a nutshell, JBoss Teiid is a federated database [2]. Well or so - this is a unifying relational add-on over multiple data sources. From the user's point of view, such a database looks like a single scheme in which tables, views, and procedures are external data objects. We can also say that external data is transmitted to a single virtual database.
The basics
This is what JBoss Teiid [1] looks like from the inside (according to the official documentation [3]):
A simple example: we take an honest relational database such as Oracle, mySQL or H2 (one or all three at once). Combine with your favorite web service, add some data in CSV text files,
It is clear that some data sources may have certain properties, for example, do not support transactions or changes (data is read-only). The built-in Teiid optimizer takes care of the disassembly with the supported properties of each such source, using an abstraction with the mysterious name "translator". Currently, out of the box translators are supported for:
- JDBC (Oracle, MS SQL Server, IBM DB2, MySQL, Postgres, Derby, Sybase, H2, HSQL, Netezza, Teradata, Modeshape. Almost everything that can be accessed through JDBC, for example Excel via jdbc-odbc or Hive Hadoop. There is also Support for ODBC: Understands Hibernate as much as possible (dynamic creation of DDL for table generation is not supported);
- Files on the file system (CSV, XML);
- Web Services
- Access to directories through LDAP;
- SalesForce, OLAP;
Client connection with Teiid is via JDBC (specification 4.0 is almost fully implemented). Keep in mind that the client application must use Java 1.6 JDK and higher, previous versions of Java are not supported.
A little bit about actually creating a virtual database (VDB file). There are two ways: you can create a database using a special designer application (built on the basis of Eclipse), or completely dynamically. The second option was more suited to our tasks, so we used it.
The following are some details about working with JBoss Teiid.
Details
1. Installing Teiid
At the moment, Teiid has two active versions: 8.0 (in development, designed to work with JBoss 7) and 7.7 (we will use it together with JBoss AS 5.1.0, we plan to switch to eight this summer).
Installation consists in downloading the Teiid archive with the latest version and deploying it on top of the installed JBoss container. Teiid 7.7 requires a “default” JBoss 5.1.0 profile. We optimized this profile slightly (we basically excluded unused services), but this step is not necessary.
2. Client application and Virtual Database
Teiid comes with several examples that illustrate the features of the work well:
- “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 client java application, connects to running Teiid and executes the specified SQL. It seems more practical for these purposes 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 databases (IBM DB2 and H2), many small files (CSV), as well as legacy data sources developed long before the advent of JDBC and the language Java (z / OS if anyone is interested). We managed to achieve the required by writing our own translators to such sources. The task turned out to be trivial, a home-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 Database without rebooting the server. I had to abandon the Designer and build a 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 utilization. Teiid has a customizable cache that has 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 introduction. If there is interest, certain moments can be developed deeper, well, or add more details from work experience.
For example, Teiid 8 under JBoss 7 promises to be quite nimble and minimally gluttonous. Unfortunately, it is still damp in places, but is being finalized at an accelerated pace. By the way - I can say that the professionalism of the Teiid support team evokes only pleasant emotions. In many cases, the problems found were fixed the day after the ticket was opened in Jira. Questions on the forum are answered within a few hours. The open source project, supported by JBoss, the guys are clearly 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
