Tuesday, February 28, 2017

Teiid 9.1.4 Released

9.1.4 is available for download or from the Maven repository.  It addresses 8 important issues:
  • [TEIID-4737] - Incorrect work of left join statement
  • [TEIID-4754] - Wrong result with bidirectional relationship
  • [TEIID-4776] - Issues with array type metadata
  • [TEIID-4791] - Bypass lookup function's keycolumn reserved word
  • [TEIID-4775] - External materialization, after transaction timeout, LoadState continues to be LOADING
  • [TEIID-4778] - External Materialization, When TTL is less than loading time, the scheduling is off
  • [TEIID-4780] - Clone TEIID-4734 (Backwards compatible solution)
  • [TEIID-4782] - Change framework to catch RutineException from translator/connector
From here the release pace on 9.1.x will slow as primary support will be on the 9.2.x branch. There will also be a 9.3 Alpha1 release next week with significant work toward JDG, materialization, and allowing domain types.


Thursday, February 23, 2017

Teiid 9.0.6 Released

Teiid 9.0.6 is now available.  This release contains 12 important fixes:
  • [TEIID-4663] - Support a more secure block mode for client/server encryption
  • [TEIID-4546] - On setting jboss-teiid cluster : throwing java.lang.IllegalArgumentException.
  • [TEIID-4547] - getQueryPlan fails in adminapi
  • [TEIID-4553] - COUNT returns inconsistent results when column statistics are not gathered
  • [TEIID-4608] - Insert statement - multi-value insert into view - incorrect type resolution if first tuple contains NULL
  • [TEIID-4661] - Subsequent queries hang after materialized view TTL expires.
  • [TEIID-4668] - Google translator skips NULL value in INSERT
  • [TEIID-4710] - Request id conflict with CommandContext connection
  • [TEIID-4727] - Aggregation is performed incorrectly for JOIN queries
  • [TEIID-4732] - Teiid Server does not honor columns name in external materialization
  • [TEIID-4737] - Incorrect work of left join statement
  • [TEIID-4776] - Issues with array type metadata
It is the final release of the 9.0.x line.  You should upgrade to 9.1 or later when feasible.  Note there will be a 9.1.4 release early next week.


Tuesday, February 21, 2017

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"/>  

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 SERVER pgsql TYPE 'postgresql-9.4-1201.jdbc41.jar'  
   VERSION 'one' FOREIGN DATA WRAPPER postgresql  
   OPTIONS (  
     "jndi-name" 'java:/postgres-ds'  
     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.



Thursday, February 16, 2017

Teiid 9.2 Final Released

It's been 4 months of hard work and we're ready to sign off on the 9.2 Final release.  Feature highlights include:

  • TEIID-4284 Salesforce Bulk API for SELECT queries.
  • TEIID-4556 TEIID-4545 TEIID-4515 Phoenix/Hbase Translator now supports joins, more system functions/pushdown functions, and offset
  • TEIID-4532 Hashing Functions were added for md5, sha1, sha2-256, and sha2-512.
  • TEIID-4627 Source Triggers to handle source change events.
  • TEIID-4643 Encrypt/decrypt functions were added for 128bit AES.
  • TEIID-4622 Data Quality Functions contributed by OSDQ.
  • TEIID-4441 FTP/SFTP Connectivity via a new ftp resource adapter.
  • TEIID-4629 DDL Replacement for vdb.xml to eliminate the need to mix XML and DDL.
  • TEIID-4398 VDB Migration tool - Migration tool to convert .vdb & -vdb.xml files into DDL based VDB
In addition to reading up on the individual JIRA issues, here's a summary of where several important efforts stand.

DDL Support

The DDL option (TEIID-4629/TEIID-4398) for defining VDBs is an exciting feature set. You will no longer need to mix XML and DDL to define a VDB, it can all be done in DDL. These features are evolving though, so please bear with us if you discover issues such as syntax that needs tweaked or inconsistencies with XML metadata. Just log an issue and we'll get things addressed - a special thanks to Bram Gadeyne who has already helping catch issues.

Data Quality

Vivek Singh contributed more OSDQ Data Quality Functions. Please see the OSDQ tooling for data quality / preparation functionality that is now compatible with Teiid. Some hashing and encryption functions (TEIID-4532/TEIID-4643) were also added as regular Teiid system functions. Expect more functions to become available in subsequent releases. We'll continue to work across both communities on data quality and related efforts.

UI Work

If you haven't seen it, please also check out Data Service Builder - which is a streamlined web GUI for JDV/Teiid on OpenShift.

There will also be a Teiid Designer compatible with 9.2 out shortly - unless full UI support for some of the language features, such as source triggers, is initially targeted.


TEIID-4627 added DDL support for source table AFTER triggers - that is just part of a major thrust. Additional issues in 9.3 will allow Debezium integration to help keep materialized views synchronized with live data via change events.

We will also be adding domain support as a first step toward making the type system more flexible.

We expect that we'll hit the 3 month target for the release, so if there is anything that seems of high value to identify for the release please let us know early.

Thanks again to everyone who contributed in some way to this release,

Wednesday, February 8, 2017

Teiid 9.2 CR1 Released

We are nearing the final release of 9.2.  CR1 can be downloaded or is available from Maven.

A feature was added since Beta2:
  • TEIID-4398 VDB Migration tool - Migration tool to convert .vdb & -vdb.xml files into DDL based VDB
At over 160 issues addressed this is a pretty significant release.  We'll now work hard to close out the remaining issues and make sure the documentation is ready.  Thanks to all who have brought issues to our attention.  In particular Pedro Inácio highlighted several issues with external materialization management.

Please try out all of issues on the CR to provide feedback before the final release.  If nothing significant is found we'll be ready for a final in about a week to 10 days.

Thank you,

Wednesday, February 1, 2017

Teiid 9.1.3 Released

9.1.3 is available for download or from the Maven repository.  It address 17 important issues:
  • [TEIID-4298] - Support in Odata4 for the Partner NavigationProperty attribute
  • [TEIID-4633] - Odata $expand over Oracle tables generates bad query
  • [TEIID-4644] - anonymous block dynamic issues
  • [TEIID-4679] - PrestoDB translator - locate function - source specific function has different order of arguments
  • [TEIID-4680] - PrestoDB translator - error executing locate(string, string, int)
  • [TEIID-4681] - PrestoDB tranlator - NPE while executing CHR function with illegal argument (char)
  • [TEIID-4685] - PrestoDB translator - IFNULL is translated as NULLIF
  • [TEIID-4692] - upsert delegating override not correct
  • [TEIID-4693] - mysql "booleans" translation issues
  • [TEIID-4698] - Adding an INFO validation to a model can invalidate the vdb
  • [TEIID-4700] - Google translator inconsistent behavior for DELETE and SELECT for null values
  • [TEIID-4710] - Request id conflict with CommandContext connection
  • [TEIID-4722] - TEIID30019: java.lang.AssertionError: Assertion failed assertion due to guard against a corner condition.
  • [TEIID-4725] - ObjectTranslator SearchByKey limit handling issue
  • [TEIID-4727] - Aggregation is performed incorrectly for JOIN queries
  • [TEIID-4729] - Multisource SOURCE_NAME field pushes to source
  • [TEIID-4732] - Teiid Server does not honor columns name in external materialization
There will also be a 9.2 CR1 release next week, so please give that an early evaluation if possible.