Teiid Implements SQL-MED Specification

Teiid community is very excited to announce that with 9.2 release we have extended the support to define VDB completely in DDL. Previous to this version, a Virtual Database (VDB) is defined either using combination of XML/DDL format or one could have designed using Teiid Designer, which is in custom binary format (.vdb)

So, what the big deal? Well when you look at any traditional relational database, DDL is primary language to create any database and its Schema. Even though Teiid is a Data Virtualization engine, for consumers it always behaves like a relational database. Having separate concepts to define your schema really impedes the adoption and learning curve for a developer. We want to make working with Teiid, as simple as you working with any relational database. However, Teiid needs are different than a traditional databases, in that Teiid needs to interface with external systems, now how can that be represented? that is where SQL-MED specification comes to light.

What is SQL-MED?

"The SQL/MED, or Management of External Data, extension to the SQL standard is defined by ISO/IEC 9075-9:2008 (originally defined for SQL:2003). SQL/MED provides extensions to SQL that define foreign-data wrappers and datalink types to allow SQL to manage external data. External data is data that is accessible to, but not managed by, an SQL-based DBMS. This standard can be used in the development of federated database systems." -  https://en.wikipedia.org/wiki/SQL/MED

See why this is cool http://rhaas.blogspot.com/2011/01/why-sqlmed-is-cool.html

What are the Benefits?

In essence SQL-MED defines the DDL semantics to connect and communicate with external sources, where this part of the configuration in Teiid VDB was designed using proprietary methods. With the support of the SQL-MED, we have removed vendor lock-in as well as learning curve for a developer who is already familiar with SQL-MED.

Previous to this release we were mixing usage of XML and DDL text to define a VDB, now everything can be defined using DDL. Since this is all text based this could automatically generated, easily understood. Possible external tool support.

Compare to .vdb binary file, since this all text based, this can be easily used with team collaboration tools like git, cvs, svn, sharepoint etc, and can be versioned and easily see the differences between different versions your VDB.  Since, all changes to the existing schema can be defined using ALTER statements in case of changes, you keep easily keep track of new changes.

Even though a VDB is completely defined using the DDL, Teiid still requires the deployment model aka you need to deploy the VDB before you can use it. However, in a Database like MySQL, PostgreSQL, one can use a command line tool like mysql, psql or tool like TOAD, and issue DDL commands interactively to create databases, tables, views etc. Having full support of DDL paves way for this next step. 

Example VDB

Below is example VDB that talks to a PostgresSQL database, both using XML/DDL and as well as SQL-MED DDL

XML/DDL based VDB:

 <vdb name="my_example" version="1.0.0">  
   <model name="test" type="PHYSICAL">  
     <property name="importer.schemaPattern" value="public"/>  
     <property name="importer.useFullSchemaName" value="false"/>  
     <property name="importer.tableTypes" value="TABLE,VIEW"/>  
     <source name="pqsql" translator-name="postgresql" connection-jndi-name="java:/postgres-ds"/>  
   </model>  
 </vdb>  

The same VDB now defined in SQL-MED based DDL

 CREATE DATABASE "my_example" VERSION '1.0.0';  
 USE DATABASE "my_example" VERSION '1.0.0'  
 CREATE FOREIGN DATA WRAPPER postgresql;  
 CREATE SERVER pgsql TYPE 'postgresql-9.4-1201.jdbc41.jar'  
   VERSION 'one' FOREIGN DATA WRAPPER postgresql  
   OPTIONS (  
     "jndi-name" 'java:/postgres-ds'  
   );  
 CREATE SCHEMA test SERVER pgsql;  
 IMPORT FOREIGN SCHEMA public FROM SERVER pgsql INTO test  
   OPTIONS(  
     importer.useFullSchemaName false,  
     importer.tableTypes 'TABLE,VIEW'  
 );  

Both above VDBs defining a VDB that connect to a PostgreSQL database and importing metadata into a schema called "test" in a VDB called "my_example". As you can see DDL is more descriptive and easy to understand and follows the standards. As I mentioned before this also paves a way interactively design your VDB in future releases.

Two really new concepts from SQL-MED, that are "DATA WRAPPERS" which are translators, and "SERVER" which represents an external server. A SERVER provides a connection to external data source and a DATA WRAPPER is adapter between Teiid and data source. Apart from above DDL commands, Teiid also supports full range of other DDL commands to define/alter schema elements like Tables, Views, Procedures and Functions. For more explanation of all the allowed DDL statements, checkout Teiid documentation at https://teiid.gitbooks.io/documents/content/reference/vdb_guide.html

We also provide a command line tool called "teiid-convert-vdb.bat" which can convert your existing XML/DDL or .vdb based VDBs into DDL format automatically, so it easy to switch over.  For time being we are going to support both formats of VDB, but in future is more defined towards DDL based VDB.

Enjoy.

Ramesh..