Skip to main content

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..

Comments

Popular posts from this blog

Teiid Platform Sizing Guidelines and Limitations

Users/customers always ask us about the sizing of their Data Virtaulization infrastructure based on Teiid or the JDV product from Redhat. Typically this is very involved question and not a very easy one answer in plain terms. This is due to fact that it involves taking into consideration questions like:
What kind of sources that user is working with? Relational, file, CRM, NoSQL etc.How many sources they are trying to integrate? 10, 20, 100?What are the volumes of data they are working with? 10K, 100K, 1M+?What are the query latency times from the sources? How you are using Teiid to implement the data integration/virtualization solution. What kind of queries that user is executing? Even small federated results may take a lot of server side processing - especially if the plan needs tweaking.Is materializing being used?Is query written in optimal way?and so on..Each and every one of the question affects the performance profoundly, and if you got mixture of those then it become that much…

Teiid Runtimes Explained

If you have been following Teiid lately we have been going through a whole lot of renovations. Yes, renovations or reorganization or refactoring or whatever you want to call it. Basically, we are making Teiid more modular with fewer dependencies that can be used by however your use case dictates rather than use it as one monolith application deployed into WildFly JEE Application Server. There is nothing wrong in using Teiid as server model, but with the proliferation of container-based workloads and cloud-based architectures, the previous server-based model does not work or simply won't scale. So, we needed to think of alternatives, thus Teiid team introduced a couple different versions modular Teiid what we are calling as "Teiid Runtimes".

Note that in these modular Teiid runtimes, not all the features you were used to using in Teiid Server model may not be there but you will have extensions to add in those that are most appropriate for your domain. If you are looking …