Monday, December 14, 2015

Teiid 8.13 Beta1 with WildFly 9.0.2 Available

The next revision Beta1 of Teiid 8.13 with WildFly is now released. The target version of WildFly we choose is 9.0.2.Final, which is latest stable release by the WildFly team. You can download it from http://teiid.jboss.org/downloads/

The issues described about sockets, clustering in previous release are now fixed. Support for Web-Console is added back in this release. The navigation of Web-Console has changed a lot, so please take a look and give us feedback.

In the next revision we will be removing "embedded" kit, and will be updating the corresponding examples soon.  We expect to have release a candidate release before the holiday break and hopefully final following soon after that.

This Beta1 version is be good version for start testing you usecase and letting us know about any issues you encounter, we can try fix them before final release.

Thank you.

Ramesh..

Thursday, December 10, 2015

Teiid 8.12.3 Released

Teiid 8.12.3 has been released.  Issues addressed include:
  • [TEIID-3814] - Rest Web Service URL for VDB
  • [TEIID-3823] - Issues with fetchsize and local connections
  • [TEIID-3827] - multi-source view insert throwing TEIID30492 error
  • [TEIID-3840] - teiid-outh-util.bat - typo in name
  • [TEIID-3841] - sap-nw-gateway translator wrongly encodes when a parameter includes a space
  • [TEIID-3845] - select count(*) query not working on SimpleDB connector in Teiid
  • [TEIID-3846] - Wrong alias rewriting in subqueries
  • [TEIID-3847] - HiveTranslator should not use a Calendar for the Hive getDate
  • [TEIID-3848] - Issue with hive literal timestamps
  • [TEIID-3850] - Source caching with the cache directive can be at the wrong scope
  • [TEIID-3851] - foreign temp additional extension properties not accessible in QMI
  • [TEIID-3854] - Impala translator - ORDER BY clause using incorrect columns when not in SELECT list
  • [TEIID-3857] - ProjectIntoNode should report a transaction required even with iterator processing
  • [TEIID-3858] - Planning error with unrelated sort columns and view removal
  • [TEIID-3682] - Add logon properties to audit messages
  • [TEIID-3831] - For results set caching there should scope metadata for source tables/procedures
  • [TEIID-3829] - olingo module junit test failed
  • [TEIID-3839] - minimize implicit dependencies on sun classes
Depending upon the progression of 8.13 and the criticality of any issues discovered, this may be the last of the fix releases on 8.12.x.  Teiid 8.13 is introducing Wildfly 9 support, but no new feature work so that it provides a transitional release into Teiid 9.  Teiid 8.13 should provide Beta1 next week and the first 9.0 Alpha should be available by the end of January.

Steve

Wednesday, December 2, 2015

Teiid 8.13 Alpha1 with WildFly 9.0.1 Available

Teiid team is proud to announce the first ever version of Teiid with WildFly. The target version of WildFly we choose is 9.0.1.Final. You can download the its from http://teiid.jboss.org/downloads/

Most of the functionality  is available,  but following are the areas we are still working on
- Domain mode
- Web-console
- Socket performance issue

We hope to fix these by Beta 1 version, hopefully in next 3 weeks. I encourage you to download and play with it and let us know if you see any issues.

Thank you.

Ramesh..

Monday, November 16, 2015

Thursday, November 12, 2015

Teiid 8.12.2 Available

Teiid 8.12.2 is now available.  Issues addressed since 8.12.1 include:
  • [TEIID-3795] - Order of VARIADIC parameters is not preserved
  • [TEIID-3799] - Insert default expressions not rewritten
  • [TEIID-3802] - HANA translator modifies boolean to tinyint in type conversion
  • [TEIID-3803] - Kerberos with ODBC with MutualAuth fails
  • [TEIID-3806] - Improve null resolving in array literals
  • [TEIID-3807] - quantified comparison results dependent on null ordering
  • [TEIID-3812] - HANA MIN and MAX functions not applicable on boolean
  • [TEIID-3813] - Informix translator - convert function in definition of view is not pushed down
  • [TEIID-3815] - Oracle translator - OFFSET function does not work
  • [TEIID-3817] - HANA FLOOR and CELING functions not applicable to boolean
  • [TEIID-3818] - Issues with ODBC metadata foreign key queries
  • [TEIID-3819] - SimpleDB NullPointerException when inserting null value
  • [TEIID-3581] - Add a system function to return Teiid Server Name
  • [TEIID-3582] - Add the query requestID to all lines for that query in the logs
  • [TEIID-3793] - Push Teiid function LOCATE() to SQL Server
  • [TEIID-3821] - Expand xml output types
  • [TEIID-3717] - Enable SNI (Server Name Indication) support
  • [TEIID-3787] - ignored properties in dynamic vdb should be flagged with a WARN log entry
  • [TEIID-3811] - Teiid Embedded with remote JDBC susceptible to exploit with common-collections in classpath
  • [TEIID-3820] - SimpleDB column import order may not be consistent

If you are using Teiid Embedded with socket based JDBC access enabled and have spring, common-collections, or groovy in the classpath, then you should upgrade to 8.12.2 due to TEIID-3811 to prevent deserialization attacks.

There have been a few delays in the 8.13 pre-releases as there has been more changes in the AS/Wildfly integration apis and it has been a larger effort than expected to normalize our dependencies.  Ideally we're still on track for an alpha release in the next week or two.

Thanks again for all the community support,
Steve

Wednesday, November 11, 2015

VDB Builder - 0.3 Update Available

VDB Builder Update

The Teiid Tooling team has released the next milestone kit for VDB Builder.  The VDB Builder is a command line tool that allows creating, editing and managing dynamic VDBs and their contents.  It also allows interaction with a teiid instance.

 

What's New?

Please see the announcement on our Teiid Tooling page for a full list of changes since the last milestone.  In this milestone, we have added the ability to connect to a teiid instance.  You can view server artifacts and also deploy vdbs from your VDB Builder workspace.  We have also made numerous enhancements in other areas.

 

Try It Out

You can start building your VDBs now by downloading the milestone kit from SourceForge!
Contributions and feedback are welcome!

Teiid Tooling Team

Monday, November 2, 2015

Teiid Designer 9.2 Released


Primary drivers for 9.2 include:
  • Compatibility with Teiid 8.11 Runtime
  • New Features
    • Generate VDB Archive and Models from dynamic -vdb.xml file
    • Generate Dynamic -vdb.xml file from VDB archive *.vdb 
These 2 new features allow you to manage your vdb's in a simpler format and utilize Teiid Designer's detailed VDB Editor to manage data roles as well as setting various VDB runtime properties (translator overrides, allowed languages).  With 9.2, you can also deploy and test your dynamic VDBs.
  • Converts dynamic -vdb.xml files in your workspace into VDB archives (*.vdb) and models
 
  • Converts VDB archives (*.vdb) into dynamic -vdb.xml files
 

See Working with Dynamic VDBs section in documention

For details see:
Teiid Designer Project Team

Thursday, October 29, 2015

Teiid 8.12.1 Released

Teiid 8.12.1 is now available.  It contains all of the 8.11.5 fixes plus a number of odata and hbase fixes.  All fixes since 8.12 include:
  • [TEIID-3757] - OData V2: $top doesn't work with virtual procedures
  • [TEIID-3760] - endswith defined with wrong return type
  • [TEIID-3761] - OData V2: The property based response and Function based responses are incorrect per specification
  • [TEIID-3762] - OData V2: $value on property is not supported
  • [TEIID-3764] - HBase - Phoenix driver does not support UNION
  • [TEIID-3765] - HBase - Phoenix does not support scalar subquery
  • [TEIID-3767] - HBase - Phoenix does not support LIKE ESCAPE
  • [TEIID-3770] - HBase - table's alias (... AS ...) is not "visible" in SELECT caluse in IN predicate
  • [TEIID-3771] - Teiid throws an NPE while executing query with nested subquery IN predicate
  • [TEIID-3776] - Teiid throws NPE if query contains AVG function on varchar type
  • [TEIID-3777] - ORDER BY DESC is ignored in STRING_AGG function when DISTINCT is also specified
  • [TEIID-3783] - Error when limit/sort is pushed above a window function
  • [TEIID-3785] - PostgreSQL - Large Objects may not be used in auto-commit mode.
  • [TEIID-3759] - Missing Apache Olingo dependency in Teiid 8.12.0.Final
  • [TEIID-3780] - Hive: Convert "UseDatabaseMetaData: to be an translator import property
  • [TEIID-3781] - Queries are not killed even if the client sends a cancel request
Additionally we are working targeted enhancements in 8.12.x.  For 8.12.1 this includes:
  • [TEIID-3039] - OData V2: Add support for odata expand
  • [TEIID-3361] - Initial addition of source query details to the command log
  • [TEIID-3436] - OData V2: Add array type support to the odata translator
  • [TEIID-3778] - Mark hbase/phoenix as supporting limit
  • [TEIID-3791] - Upgrade groovy-all to be version 2.4.
We'll look to put out an 8.12.2 in a couple of weeks as more issues are worked/found.

Work has started on the Wildfly 9 support in Teiid 8.13.  If all goes well, then an 8.13 Alpha1 will be available in the next two weeks.

Thanks,
The Teiid Team


Thursday, October 22, 2015

Teiid 8.11.5 Released

Teiid 8.11 development has concluded with the release of 8.11.5, which is available from maven and the downloads page.  Important issues addressed in this release include:
  • [TEIID-3564] - float field gets converted to scientific notation when query is submitted to source
  • [TEIID-3707] - Wrong Data returned when a procedure is executed in the SELECT clause
  • [TEIID-3720] - DDLString Visitor not writing the OPTIONS value for Procedure parameters
  • [TEIID-3726] - CTEs combined with constant values yield an error message
  • [TEIID-3739] - Dynamic VDB that imports another VDB does not get redeployed if the imported VDB is redeployed
  • [TEIID-3756] - Order By clause specified in STRING_AGG function is skipped when pushed down to PostgreSQL
  • [TEIID-3777] - ORDER BY DESC is ignored in STRING_AGG function when DISTINCT is also specified
  • [TEIID-3705] - Build issue in security

From here we will start providing 8.12.x releases - starting with 8.12.1 in about a week.  If all goes well in approximately 2 weeks we'll provide an 8.13 Alpha with the initial Wildfly integration.

Thanks,
Steve

Thursday, October 15, 2015

Teiid 8.12 Final Released, Swagger support added

Keeping up the tradition of time boxed releases, the Teiid team is proud to announce 8.12.0 Final. We have resolved 147 issues (features, enhancements, bugs) in this release. You can downloads at http://teiid.jboss.org/downloads/

JBoss EAP & Teiid:
8.12/8.11 are based on the JBoss EAP 6.4.Alpha platform. The downloads page contains a consolidated single download file that contains  EAP 6.4 Alpha + Teiid 8.12.0 + Teiid Web-Console.  You also have choice of using Docker image if you choose to at Docker Hub.

Please note this is the LAST VERSION of Teiid that will be based on JBoss EAP.  8.13 and 9.0, we will be moving to WildFly. Any micro releases on 8.12 line like 8.12.1, 8.12.2 etc still be using JBoss EAP.

Highlights of 8.12
  • TEIID-3411 LDAP modeling can now better account for associations defined by multivalued attributes.
  • TEIID-3323 Vertica translator added.
  • TEIID-3515 TEIID-3530 Dependent join enhancements in hints and pushdown planning.
  • TEIID-2883 Actian Vector in Hadoop Translator added
  • TEIID-3595 TEIID-3594 Logging Enhancements for command and audit logging
  • TEIID-3546 IS DISTINCT FROM syntax support added for use in procedures.
  • TEIID-3521 Swagger Support for auto-generated REST web services
  • TEIID-3652 Additional OUTER JOIN planning to allow more flexibility in join ordering
  • TEIID-3668/TEIID-3618 OAuth Support with JWT Bearer Access Token and Salesforce SSO using OAuth2 (RefreshToken, JWT)
  • TEIID-3712 Date/Time Keyword Literal support was added in the parser.
  • TEIID-3722 Option to not widen comparisons to string was added via the system property org.teiid.widenComparisonToString
  • TEIID-3327 OSIsoft PI translator added for use with the PI JDBC driver.
  • TEIID-3727 Salesforce Translator added a translator and resource-adapter based on Salesforce Partner API 34
  • TEIID-3370 OData4 Translator added new translator to read/write from any compliant OData V4 source
Community Folks:
Once again we had great interactions with community in developing this release. We sincerely extend our appreciation to Albert Wong, Avinash Misra, Bram Gadeyne, Burak Serdar, Dalex, Don Krapohl, Gary Gregory, Guru Prasad, Jorge Herrera, John Muller, Mark Addleman, Mark Tawk, Michael Davies, Michal Sanitrak, Mike Higgins, Pranav K, Prashanthi Kairuppala, Salvatore R, Sathish Kumaran Vairavelu, Scott Wallace, and Tom Arnold.

What's Coming Next:

Lot's of Stuff :)

We are working on 8.13 version that is just going to be similar to 8.12 version in terms of features, but we will replace JBoss EAP 6.4 Alpha with WildFly 9 or 10. If community feels WildFly 10 is better please speak up.  We'll probably move to WildFly 10 during the Teiid 9.0 development regardless.

Also expect an 8.11.5 shortly as the last of the 8.11.x series.

We are always looking for volunteers, so if you got idea or just want contribute to Teiid, please come talk to us in the forums.

Thank you for your continued support.

Ramesh & Steve.

Friday, October 2, 2015

Teiid 8.12 CR1

After a small delay, we are pleased to release Teiid 8.12 CR1.  We were able to get quite a few issues resolved - to date there are 134 issues resolved.  As usual CR1 can be found in maven and on the downloads page.

Feature highlights since Beta3 include:
  • TEIID-3712 Date/Time Keyword Literal support was added in the parser.
  • TEIID-3722 Option to not widen comparisons to string was added via the system property org.teiid.widenComparisonToString
  • TEIID-3327 OSIsoft PI translator added for use with the PI JDBC driver.
  • TEIID-3727 Salesforce Translator added a translator and resource-adapter based on Salesforce Partner API 34

Expect a final release in approximately 2 weeks, followed shortly by an 8.11.5 release.  Please let us know if you find any issues as we will address anything critical prior to the final release.

Thanks again for all the community support,
Steve

Thursday, September 17, 2015

Teiid 8.12 Beta3

There is still more Olingo work and quite a few issues to go through for 8.12, so we decided it was best to release a Beta3 rather than CR1 at this point.  Beta3 can be found in maven and on the downloads page.

The feature highlight since Beta2 include:
  • TEIID-3546 IS DISTINCT FROM syntax support added for use in procedures.
  • TEIID-3521 Swagger Support for auto-generated REST web services
  • TEIID-3652 Additional OUTER JOIN planning to allow more flexibility in join ordering
  • TEIID-3668/TEIID-3618 OAuth Support with JWT Bearer Access Token and Salesforce SSO using OAuth2 (RefreshToken, JWT)
TEIID-3652 was only in a preliminary state in Beta2, but should be complete now.  And once again a CR1 will follow shortly.

Thanks again for all the community support,
Steve

Teiid Earns 2015 InfoWorld Bossie

We are very proud to announce that Teiid was named one of the winners of the 2015 InfoWorld Bossie (Best of Open Source Software) awards in the business applications, enterprise integration and middleware category.

Please see the announcement at
http://www.infoworld.com/article/2982622/open-source-tools/bossie-awards-2015-the-best-open-source-applications.html#slide15 

Congrats to whole Teiid community for this validation of the dedicated work we put in to consistently expand our integration and virtualization capabilities.

The Teiid Team

Wednesday, September 16, 2015

Teiid 8.11.4

In what should be the last of the formal 8.11 fix releases, we have made 8.11.4 available.  This address 8 issues since 8.11.3:
  • [TEIID-3671] - filter on child table not working
  • [TEIID-3674] - NOT IN construct behaves inconsistently
  • [TEIID-3685] - org.teiid.padSpace does not affect to the "IN" operator
  • [TEIID-3692] - Impala/Hive string literals
  • [TEIID-3696] - Duplicates are not always removed when UNION and GROUP BY clauses are used in a subquery
  • [TEIID-3699] - execute immediate only checks immediate parent scope
  • [TEIID-3702] - dependency and validation logic incorrect
  • [TEIID-3664] - Timing issue with ping
Thanks to those who helped uncover the issues.  8.12 CR1 will follow shortly.

Steve

Tuesday, September 1, 2015

Teiid 8.12 Beta2 and 8.11.3

We are further along toward the fall debut of Teiid 8.12 with the release of 8.12 Beta2 today.  The highlights of Beta1 are mostly fixes including corrections to the auto sizing of memory resources, fixing geometry methods in the EAP kit, improving the admin api, and a variety of translator issues.  We expect a CR1 in about 2 weeks.

Teiid 8.11.3 has also been released.  It contains 9 important fixes - all of which are in 8.12 Beta2.  Moving forward we anticipate supporting fix releases for longer into the cycle than we were previously.  So it should be routine for 3-4 fix releases to be issued rather than just a single one.

Both releases are available from maven and have been added to the download page: http://teiid.jboss.org/downloads/

Thanks again for all the community support,
Steve

Friday, August 14, 2015

Teiid 8.12 Beta1

Teiid 8.12 Beta1 has been released.  The feature highlight since Alpha2 is TEIID-3595/TEIID-3594 which were logging enhancements for command and audit logging - more granularity of the levels and more security events.

There have also been several HBase/Phoenix fixes and a variety of other fixes.  Expect another Beta release in about 2 weeks.

Thanks again for the community support and let us know if you find anything with the pre-release.

Steve

Friday, August 7, 2015

VDB Builder - Update Available

VDB Builder Update

The Teiid Designer team has released the next milestone kit for VDB Builder.  The VDB Builder is a command line tool that allows creating, editing and managing dynamic VDBs and their contents. The command line interface (CLI) is a result of our on-going Komodo initiative to develop an extensible VDB editing framework.

What's New?

In this milestone, we have added the ability to import and export Teiid DDL.  This means that you can create model content using a saved DDL file, and you can also export your model content into a DDL file.  In addition, we have updated our launch scripts and made numerous fixes and enhancements.

Try It Out

You can start building your VDBs now by downloading and unzipping the milestone kit from SourceForge!
You can view and checkout the source code here:  https://github.com/Teiid-Designer/komodo

Contributions and feedback are welcome!

Teiid Designer Project Team

Tuesday, July 28, 2015

Teiid 8.12 Alpha2 Released

Teiid 8.12 Alpha2 is now available from the downloads and maven.  Feature highlights since Alpha1 include:
  • TEIID-3515 TEIID-3530 Dependent join enhancements in hints and pushdown planning.
  • TEIID-2883 Actian Vector in Hadoop Translator added
Expect more translators to come for this release.  There should be a Beta1 in about 2 weeks, and an 8.11.2 release will be available by the end of the week to correct a couple of new issues and addresses two of the issues originally intended for 8.11.1 but that didn't make it into the release.

Thanks again for all of the community support,
Steve

Thursday, July 16, 2015

Teiid 8.11.1 Released

Just a quick post to say that Teiid 8.11.1 is now available from maven.  It address 5 important issues:

  • [TEIID-3502] - Teiid returns integer/long overflow values
  • [TEIID-3556] - BufferManager reserve memory variable is decremented twice
  • [TEIID-3557] - Can't reload VDB in domain mode
  • [TEIID-3558] - Duplicate records being for formed for Internal Materialized View cluster
  • [TEIID-3563] - Problems with capabilities initialization

Also 8.12 Alpha2 should be available next week.

Thanks,
Steve

Friday, July 10, 2015

VDB Builder - Next Generation Teiid Tooling Available

Next Generation of Teiid Designer

The Teiid Designer team has been developing a more versatile VDB editing tool called the VDB Builder.  The VDB Builder is a command line tool that allows creating, editing and managing dynamic VDBs and their contents.

This tool is a result of our on-going Komodo initiative to develop a non-Eclipse extensible VDB editing framework. We plan to continue building out this CLI to allow full editing of VDBs and data sources and eventually adding deployment capabilities. But it is currently limited to VDB creation and editing.

This framework is rooted in adapting a local embedded ModeShape repository which utilizes sequenced node structures modeled from the Teiid vdb-deployer.xsd schema definition and Teiid BNF.

Try It Out

You can start building your VDBs now by downloading and unzipping the first milestone kit from SourceForge!
You can view and checkout the source code here:  https://github.com/Teiid-Designer/komodo
Here are a couple of screen caps of initial welcome screen and showing the summary information for a VDB that was imported.
























Future Direction

Moving forward, we plan to refine and add capabilities to the framework that will allow for other UIs to be added such as Web and Eclipse. Contributions and thoughts are welcome!

Teiid Designer Project Team

Teiid Designer 9.1 Released

Teiid Designer 9.1 Final is available for install.

Primary drivers for 9.1 include:
  • Compatibility with Teiid 8.10 Runtime
  • Bug fixing with special attention to Web Services Importers and reducing size of dialogs and wizard pages

For details see:
Teiid Designer Project

Thursday, July 9, 2015

Teiid 8.12 Alpha1

Teiid 8.12 Alpha1 is now available from the downloads and maven.  Feature highlights in Alpha1 include:
  • TEIID-3411 LDAP modeling can now better account for associations defined by multivalued attributes.
  • TEIID-3323 Vertica translator added.
There have also been several important fixes, new options for default handling, and refinements to dependent join pushdown planning.  We except more sources to be added in the subsequent pre-releases and we're working toward removing the embedded kit in favor of more examples and just using maven, so stay tuned.

Also expect an 8.11.1 release in about a week to provide fixes for around 5 - 10 key issues.

Thanks again for all of the community support,
Steve

Wednesday, July 1, 2015

Teiid on top of HP Vertica Analytic Database Server

HP Vertica Analytic Database Server is designed to manage large, fast-growing volumes of data and provide very fast query performance when used for data warehouses and other query-intensive applications.

Starting from Teiid 8.12, with Vertica Data Source(Vertica supply JDBC Driver) Teiid support connect/transform/federate data exist in Vertica table.

vertica-as-a-datasource is example demonstrates using the Vertica Translator to access data in Vertica Server, this can be a quick start for implementing your usecase. 

Saturday, June 20, 2015

8.11 Final Released, Support for OData V4 and SAP HANA

Keeping up with tradition of releasing a minor release every 3 months, the Teiid community is proud to announce yet another feature rich release - 8.11.0.Final. We have resolved 117 JIRA issues (features, enhancements, bugs) in this release. You can download the latest version from http://teiid.jboss.org/downloads

JBoss EAP & Teiid:
Note that in this release the base environment to deploy Teiid is moved JBoss EAP 6.4 platform. For your convenience, we are also providing a download link where EAP 6.4 Alpha + Teiid 8.11 + Teiid Web-Console bundled all in single downloadable file. You also have choice of using Docker image if you choose to at Docker Hub.

Embedded Teiid:
If you are working with Embedded Teiid checkout our new examples repository at https://github.com/teiid/teiid-embedded-examples For embedded we are changing from providing a kit, to just relying on maven and the examples for 8.12.  We want provide as many example scenarios as possible, so that you can pick a example and start implementing your usecase.

Top highlights:


  • TEIID-2973 OData V4 Access your VDB using OData V4 protocol
  • TEIID-3151 SAP Hana A new translator integrate data from SAP Hana.
  • TEIID-3372 Multiple metadata elements can now be used to configure a vdb.
  • TEIID-3369 Custom query rewrite - via a configurable PreParser.
  • TEIID-3389 Kerberos Delegation JDBC driver can now participate in kerberos delegation based authentication
  • TEIID-3390 SQL/XML improvements in performance and disk utilization.
  • TEIID-3362 ARRAYITERATE system procedure for iterating over an array.
  • TEIID-3393 Embedded AdminApi is now available from EmbeddedServer.getAdmin.
  • TEIID-3270 Virtual Functions can not be defined with procedure syntax.
  • TEIID-3434 More caching control over ttls in the result set cache down to the schema/table level.
  • TEIID-3412 MS Access support via the UCanAccess driver.
  • TEIID-3371 EAP 6.4 as the target platform.
  • TEIID-3491 REGEXP_REPLACE function added


  • Community:
    We had great community support from Ben Kibler, Bram Gadeyne, Bruuno Macho, Burek Serder, Gautam Benerjee, Harrison Gentry, Jaime Merritt, Mark Addleman, Monica Ahuja, Pranav K, Salvatore R, Sanjeev Gour, Shiveeta Matto and Tom Arnold for participating in the community in terms of submitting JIRAs and code contributions. Special thanks to Tom Arnold for submitting regexp_replace and to Ted Jones for developing the Hana support.

    Teiid is also seeing a large number of community members that are asking questions in out forums, we sincerely thank you all. Keep those questions and comments rolling in. We need to keep the momentum going. Write a blog, share your story.

    What's Coming:
    Lot of exciting stuff happening, we started to work on 8.12 and also planning on 9.0 simultaneously. We hope to bring in WildFly support in Teiid 8.12 along with EAP 6.4 and also move permanently to WildFly in Teiid 9.0 releases. We are gathering requirements. So, challenge for you is "How is your enterprise IT changing? What kind of changes you want to see from Teiid Data Virtualization platform?" come engage us in conversation that is important for you. We highly encourage it.

    Thank you again for your continued support.

    Ramesh & Steve

    Wednesday, June 10, 2015

    SQL on MongoDB using Teiid - Part 2

    In the previous article SQL on MongoDB using Teiid - Part 1 I showed a simple example how one can start using SQL based queries on MongoDB using Teiid. I have showed how to install and configure the Teiid and test.

    The previous example was very simple that generated a MongoDB collection for defined relational table in Teiid. One of many advantages of NoSQL based stores like MongoDB is, user's ability to de-normalize data such that data for related entities is co-located. When the data is co-located there is no requirement for defining the relationships across entities, and queries will be naturally faster to execute. However, in relational databases data is more often very normalized, the naive implementation of relational to MongoDB where one table is mapped to a collection will result in very poorly performing queries. The solution is, utilize the MongoDB's nested document feature to represent a relationships in Relational database.

    In this article, I will show how to define relationships across different tables and it's representation in MongoDB document, where the performance is not compromised.

    ONE-2-ONE

     CREATE FOREIGN TABLE Customer (  
       CustomerId integer PRIMARY KEY,  
       FirstName varchar(25),  
       LastName varchar(25)  
     ) OPTIONS(UPDATABLE 'TRUE');  
    
     CREATE FOREIGN TABLE Address (  
       CustomerId integer,  
       Street varchar(50),  
       City varchar(25),  
       State varchar(25),  
       Zipcode varchar(6),  
       FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId)  
     ) OPTIONS(UPDATABLE 'TRUE');  
    

    When you issue INSERT statements against above tables, by default it will produce the documents like

     Customer  
     {  
      _id: 1,  
      FirstName: "John",  
      LastName: "Doe"  
     }  
     Address  
     {   
      _id: ObjectID("..."),   
       CustomerId: 1,  
       Street: "123 Lane"  
       City: "New York",  
       State: "NY"  
       Zipcode: "12345"  
     }  
    

    You can enhance the storage in MongoDB to a single collection by using "teiid_mongo:MERGE' extension property on the table's OPTIONS clause


     CREATE FOREIGN TABLE Customer (  
       CustomerId integer PRIMARY KEY,  
       FirstName varchar(25),  
       LastName varchar(25)  
     ) OPTIONS(UPDATABLE 'TRUE');  
    
     CREATE FOREIGN TABLE Address (  
       CustomerId integer PRIMARY KEY,  
       Street varchar(50),  
       City varchar(25),  
       State varchar(25),  
       Zipcode varchar(6),  
       FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId)  
      ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:MERGE" 'Customer');  
    

    this will produce single collection in MongoDB
     Customer  
     {  
      _id: 1,  
      FirstName: "John",  
      LastName: "Doe",  
      Address:   
        {   
         Street: "123 Lane",  
         City: "New York",  
         State: "NY",  
         Zipcode: "12345"  
        }  
     }  
    

    You can issue queries like

     SELECT * FROM Customer JOIN Address ON Customer.CustomerId = Address.CustomerId  
    

    ONE-2-MANY

    Following the similar strategy using "teiid_mongo:MERGE" property, the below shows the one to many relationship  .


     CREATE FOREIGN TABLE Customer (  
       CustomerId integer PRIMARY KEY,  
       FirstName varchar(25),  
       LastName varchar(25)  
     ) OPTIONS(UPDATABLE 'TRUE');  
    
     CREATE FOREIGN TABLE Order (      
       OrderID integer PRIMARY KEY,  
       CustomerId integer,  
       OrderDate date,  
       Status integer,  
       FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId)  
     ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:MERGE" 'Customer');  
    

    Will generate MongoDB collection like below

     {  
      _id: 1,  
      FirstName: "John",  
      LastName: "Doe",  
      Order:   
      [  
        {   
         _id: 100,   
         OrderDate: ISODate("2000-01-01T06:00:00Z")  
         Status: 2  
        },  
        {   
         _id: 101,   
         OrderDate: ISODate("2001-03-06T06:00:00Z")  
         Status: 5  
        }  
        ...  
       ]  
     }  
    

    You can issue SQL queries like

    SELECT * FROM Customer JOIN Orders ON Customer.CustomerId = Orders.CustomerId 
      WHERE OrderDate = <date>  
    

    Note that above I showed the nesting of the documents at single level, however you can define even define deeper nesting. For example, Order table has OrderItems table

      CREATE FOREIGN TABLE Customer (   
       CustomerId integer PRIMARY KEY,   
       FirstName varchar(25),   
       LastName varchar(25)   
      ) OPTIONS(UPDATABLE 'TRUE');  
     
      CREATE FOREIGN TABLE Order (     
       OrderID integer PRIMARY KEY,   
       CustomerId integer,   
       OrderDate date,   
       Status integer,   
       FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId)   
      ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:MERGE" 'Customer'); 
      
     CREATE FOREIGN TABLE OrderDetails (  
      OrderID integer NOT NULL,  
      UnitPrice double default '0',  
      Quantity integer default '1',  
      Discount float default '0',  
      FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),  
     ) OPTIONS ("teiid_mongo:MERGE" 'Orders', UPDATABLE 'TRUE');  
    

    after inserting some data, the result MongoDB document will look like

     {  
      _id: 1,  
      FirstName: "John",  
      LastName: "Doe",  
      Order:   
      [  
        {   
         _id: 100,   
         OrderDate: ISODate("2000-01-01T06:00:00Z")  
         Status: 2,  
         OrderDetails:  
         [  
           {  
             UnitPrice: 23.12  
             Quantity: 5  
             Discount: 34.2  
           },  
           {  
             UnitPrice: 51.17  
             Quantity: 10  
             Discount: 15.0  
           }  
           ...  
         ]  
        },  
        {   
         _id: 101,   
         OrderDate: ISODate("2001-03-06T06:00:00Z")  
         Status: 5,  
         OrderDetails:  
         [  
           {  
             UnitPrice: 78.00  
             Quantity: 4  
             Discount: 20  
           },  
           {  
             UnitPrice: 43.12  
             Quantity: 100  
             Discount: 30  
           }  
           ...  
         ]      
        }  
        ...  
       ]  
     }  
    

    So, as you can see you can create pretty complex nested documents in MongoDB using Teiid, to save query performance, at the same time you can use your expertise in SQL to design the queries using the JOINS to  read the data that you are interested in or run BI tool on top it to generate reports etc.

    For more information please read https://docs.jboss.org/author/display/TEIID/MongoDB+Translator 

    If you are working with existing MongoDB database, Teiid can import the existing collections and their document structure and automatically create the relational tables in Teiid. For that please refer to article https://developer.jboss.org/wiki/ConnectToAMongoDBSource

    I have showed in these articles how to create and use MongoDB database and access the MongoDB using SQL. This is great tool, if you looking to replace your relational database like Oracle, SQLServer etc with MongoDB. Most of the concepts in your relational world map one to one using Teiid.

    Thank you, If you have any questions please let us know.

    Ramesh..

    Tuesday, June 9, 2015

    Teiid 8.11 CR1

    Teiid 8.11 CR1 is now available from the downloads and maven.  

    There are no new features since Beta3 as we were focused on address as many regular issues as possible, including further refinements to the materialization logic.  The feature set for the entire release is looking good:
    • TEIID-3372 Multiple metadata elements can now be used to configure a vdb.
    • TEIID-3369 Custom query rewrite - via a configurable PreParser.
    • TEIID-3389 Kerberos Delegation JDBC driver can now participate in kerberos delegation based authentication
    • TEIID-3390 SQL/XML improvements in performance and disk utilization.
    • TEIID-3362 ARRAYITERATE system procedure for iterating over an array.
    • TEIID-3393 Embedded AdminApi is now available from EmbeddedServer.getAdmin.
    • TEIID-3270 Virtual Functions can not be defined with procedure syntax.
    • TEIID-3434 More caching control over ttls in the result set cache down to the schema/table level.
    • TEIID-3412 MS Access support via the UCanAccess driver.
    • TEIID-3371 EAP 6.4 as the target platform.
    • TEIID-3491 REGEXP_REPLACE function added
    • TEIID-2973 OData V4 support based on Apache Olingo
    The Final release should be produced by June 18th.

    There are now quite a few issues that are in the 8.12 release, which we will work somewhat in parallel with Teiid 9.0.  If possible we'll try to bridge the gap by offering a Wildfly based 8.12 as well.  Please continue to log issues/requests so that we can get them in the appropriate release.

    Thanks again for all of the community efforts.

    Steve

    Sunday, June 7, 2015

    SQL on MongoDB using Teiid - Part 1

    In this article series, I will showcase how you can use SQL based queries with MongoDB. In general there seems to be resurgence of SQL based access to all NoSQL based stores in the market space, take for example Hive, Impala, Spark, Apache Drill etc. The main reason for this shift is there are abundant amount of talent pool out there for SQL based developers, and even today (and years to come) our strong dependence on the relational stores for the enterprise data. I do not see either of them fading away any time soon. So utilizing the known skills on new types of data stores will save you lot of time and provide better integration with rest of your enterprise applications.

    I know there are plenty of folks offering SQL based access to MongoDB, why you should choose Teiid?
    • Teiid provides full ANSI compatible SQL based access to MongoDB. This includes full SQL-92, and most SQL-99 and SQL-2003 support.
    • Provides JDBC/ODBC access to execute SQL queries.
    • Provides ODATA based access to MongoDB.
    • Provides framework to easily create custom REST based services over MongoDB or any other data source
    • Teiid is a data virtualization system, that means you can integrate data from MongoDB with other enterprise sources like Orcale, DB2, SQL Server etc.
    • Complex nested document support up to three+ levels of embedded documents. I have not seen any other project support more than one level of nested documents.
    • Supports full range of SELECT, INSERT, UPDATE and DELETE calls.
    • Array support, Blob Support, GEO functions support.  
    • A JBoss community Open Source project.
    Depending upon your architecture and needs there are different ways you use Teiid with MongoDB
    • Standalone Teiid server
    • Embedded Teiid
    • Teiid Docker 
    However, in all the above are only different deployment scenarios, as per defining your interaction with MongoDB in-terms of SQL is exactly same. In this article I will focus on Standalone Teiid Server. I will not go in any details about installation of Teiid or MongoDB. Also, I assume user already has familiarity with Teiid concepts, if not please read http://teiid.jboss.org/basics/ and go through a quick start example here https://docs.jboss.org/author/display/teiidexamples/Teiid+Quick+Starts

    So, let's get started. 

      <resource-adapters>   
       <resource-adapter id="mongodb">   
        <module slot="main" id="org.jboss.teiid.resource-adapter.mongodb"/>   
        <transaction-support>NoTransaction</transaction-support>   
        <connection-definitions>   
         <connection-definition class-name="org.teiid.resource.adapter.mongodb.MongoDBManagedConnectionFactory"   
           jndi-name="java:/mongoDS"   
           enabled="true"   
           use-java-context="true"   
           pool-name="teiid-mongodb-ds">   
          <!-- MongoDB server list (host:port[;host:port...]) -->   
          <config-property name="RemoteServerList">localhost:27017</config-property>   
          <!-- Database Name in the MongoDB -->   
          <config-property name="Database">test</config-property>   
           <!--    
            Uncomment these properties to supply user name and password   
           <config-property name="Username">user</config-property>   
           <config-property name="Password">user</config-property>   
           -->    
         </connection-definition>   
        </connection-definitions>   
       </resource-adapter>   
      </resource-adapters>  
    

    • The above will create a JCA connection pool to the MongoDB. Save it and start the JBoss EAP.
    • Now it is time to create VDB. The following shows a very simple VDB

     <vdb name="nothwind" version="1">  
       <model name="northwind">  
         <source name="local" translator-name="mongodb" connection-jndi-name="java:/mongoDS"/>  
           <metadata type="DDL"><![CDATA[  
             CREATE FOREIGN TABLE Customer (  
               customer_id integer,  
               FirstName varchar(25),  
               LastName varchar(25)  
             ) OPTIONS(UPDATABLE 'TRUE');  
           ]]> </metadata>  
       </model>  
     <vdb>  
    

    Now deploy this VDB using either web-console or CLI into Teiid. Make sure it is deployed in ACTIVE state. Now using any JDBC client like SquirreL, and issue SQL queries like


     INSERT INTO Customer(customer_id, FirstName, LastName) VALUES (1, 'John', 'Doe');  
    

    This will produce a document like below in the MongoDB database.

     {  
      _id: ObjectID("509a8fb2f3f4948bd2f983a0"),  
      customer_id: 1,  
      FirstName: "John",  
      LastName: "Doe"  
     }  
    

    If you issue a query like

     SELECT * FROM Customer  
    

    you will see the result like

     customer_id  FirstName  LastName  
     1           John       Doe  
    

    This is a illustration of what it takes to use SQL on MongoDB using Teiid. But we only scratched SQL capabilities. In the next article I will show case, how to create complex nested documents and resulting SQL/DDL for it.

    Thanks

    Ramesh..

    Wednesday, June 3, 2015

    Tech Tip: Teiid SQL Language MAKEDEP Hint Explained

    In this article I will explain what a MAKEDEP hint is, how and when, why it should be used in Teiid.

    What: MAKEDEP is query hint.  When a query hint is defined in the SQL query it influences the Teiid query planner to optimize the query in a way that is driven by the user. MAKEDEP means "make this as a dependent join".

    What is a Dependent Join?

    For example if we have query like:

     SELECT * FROM X INNER JOIN Y ON X.PK = Y.FK  
    

    Where the data for X, and Y are coming from two different sources like Oracle and WebService in Teiid, so in relational algebra you can represent above query as


    Here the result tuples from node X and node Y are being simultaneously fetched by Teiid query engine, then it joins the both the results inside Teiid engine based on the specified X.PK = Y.PK condition and returns the filtered resulted to the user. simple..

    Now, what if, if X table has 5 rows and Y table has 100K rows? In order to do the JOIN naively Teiid need sto read all the 5 rows from X side and 100K rows from Y side then proceed with the JOIN. That is where MAKEDEP comes to rescue if the planner cannot use statistics to automatically determine a better plan for you.

    Let's modify query like this and provide a MAKEDEP hint:

     SELECT * FROM X INNER JOIN /*+ MAKEDEP */ Y ON X.PK = Y.FK  
    

    here what you are suggesting to the query planner is make the node Y as dependent node on X. That means the data from Y is dependent on X data. In this scenario, the execution then be like


    The query planner will do the operations in sequence this time
    (1) Fetch 5 rows from X
    (2) Push the distinct equi-join values from X into Y side using IN clause(2)
    (3) Fetch resultant rows from Y that matches the JOIN condition

    The SQL executed are

    SELECT * FROM X;
    SELECT * FROM Y WHERE Y.FK IN (X Values);

    here the Y node will return ONLY relevant data, which can be significantly less that the full relation. By doing this, you avoided lot of network traffic in retrieving the rows, and also processing inside the Teiid for match the X.PK = Y.FK condition. This results in match faster query.

    Now what if X has 10K rows? Imagine fetching all the 10K rows, and sending them to node Y node in "IN" clause? The issues are some databases do not allow SQL statement bigger than certain size, have limits to values in the IN clause, limits to prepared bindings, etc.  To compensate multiple queries must be issued.  Generally the processing will suffer with a larger numbers as the number of source queries increases. Teiid offers another solution for this - to create a temporary table with the relevant values from X, then issue a join query based on temporary table and Y.

    To do this, for JDBC translators you need to add translator override property called "EnableDependentJoins" to "true".  Then when you submit the query

     SELECT * FROM X INNER JOIN /*+ MAKEDEP */ Y ON X.PK = Y.FK  
    

    The processing will be

    The query planner will do the operations in sequence this time
    (1) Fetch 10K rows from X
    (2) Insert the distinct equi-join values from X into Y side using batched inserts
    (4) Fetch resultant rows from Y that matches the JOIN condition
    (5) send rows back to user

    The executed sudo SQL statements are

    SELECT * FROM X;
    CREATE TABLE #TEIID_XXX (XPK coltype);
    INSERT INTO #TEIID_XXX (X key values);
    SELECT * FROM Y JOIN #TEIID_XXX ON Y.FK = #TEIID_XXX.XPK;

    Depending upon number of values that is being pushed, this can result even faster query.

    You can also customize MAKEDEP to force additional behavior.  The planner will choose when to back off of (if there are too many independent values) a dependent join based upon statistics.  This behavior can be forced with the MAX option:

     SELECT * FROM X INNER JOIN Y MAKEDEP(MAX:5000) ON X.PK = Y.FK  

    That means only create the dependent join when there are less then 5000 rows from X side.

    The planner based upon the plan and the source support for dependent joins can also choose to instead send all of the relevant rows from X over to Y.  This can be forced with the JOIN option:

     SELECT * FROM X INNER JOIN Y MAKEDEP(JOIN) ON X.PK = Y.FK  

    This is same as above temp table scenario, Using a temp table for the entire independent side.  This option is best suited to situations where more of plan can be pushed, for example aggregation and other processing above the join:

     select
         grouping
              join
                  access
                  access

     can become:
       access (performing the join via data shipment)
            select
               grouping

    If there isn't any additional processing and a wide set of values (or something that is using lobs), then the best you can do is just creating a temporary table for the key set (the previous example only uses key values), in which case the plan still looks the same as the default dependent join.

    You can read more about MAKEDEP here https://docs.jboss.org/author/display/TEIID/Federated+Optimizations

    There also MAKEIND hint, which opposite of MAKEDEP which is placed on independent side of a dependent query. MAKENOTDEP forces the query engine not to plan a dependent query.

    Hopefully this gave a good material as to how, when to use the hints to write better performing queries. Note that, when costing information is defined on the tables, most of these decisions are made automatically, if the Teiid not doing it you now know how to force it to use :)

    Thanks

    Ramesh.. 

    Monday, June 1, 2015

    Teiid 8.11 Beta3 Posted

    Teiid 8.11 Beta3 is now available from the downloads and maven.  

    The main feature highlight since Beta2 is a community contribution from Tom Arnold to add support for the REGEXP_REPLACE function - a more powerful version of the REPLACE function that supports regular expressions.

    There were a lot of late changes, thus the decision to release another beta rather than progress to CR1.  It is expected that we'll produce a CR1 by June 10th - with the final to follow at least a week later.

    Also we're looking at starting on Teiid 9.0 soon.  If you have any major features you'd like to see, please start a forum topic of log/vote for a JIRA issue.  Another parallel effort could be to produce a Wildfly enabled 8.x release -please let us know if there is any interest in that.

    For 8.11 we've addressed 90+ issues so far - with your help we'll close out quite a few more before the final release.

    Thanks again for all of the community efforts.

    Steve

    Friday, May 15, 2015

    Looking for training on Teiid or Red Hat Data Virtualization?

    This article mentions two different types of training available. One with a Red Hat training course and another is workshop. For more details read on to http://www.ossmentor.com/2015/05/learn-data-virtualization.html

    Thursday, May 14, 2015

    Teiid 8.11 Beta2 Avaliable

    Teiid 8.11 Beta2 is now available from the downloads and maven.  The main feature highlight of this release since Beta1 is TEIID-3371 which puts us on EAP 6.4 Alpha as the target platform.

    Also more buffer manager configuration has been exposed for embedded and there are now examples showing integration with IronJacamar/Naryana.  We'll be looking into rounding out the examples and Teiid embedded in general to make it's usage as straight-forward as possible in the coming releases. 

    For 8.11 we've addressed nearly 80 issues so far and will start to narrow in on the final release - which should be on track for a little over 3 weeks from now.

    Thanks again for all of the community efforts.

    Steve