Teiid 8.4 Beta2 Posted

Teiid 8.4 Beta2 for EAP 6.1.Alpha is now available in our downloads.  The new features in this this beta (since Teiid 8.3 Final):

• TEIID-2444 EAP Platform - the deployment platform for Teiid has been changed to EAP 6.1.Alpha1
• TEIID-2429 Improved sort performance - improved sorts for large data sets especially under heavy load.
• TEIID-2449 VDB Zips - added the ability to deploy non-Designer based vdb zip artifacts with the ability to place DDL outside of the VDB.xml via the DDL-FILE metadata repository.
• TEIID-2423 TEIID-2470 Additional Embedded deployments - xml and zip vdb deployment options are available for Teiid Embedded.
• TEIID-1092 Session Variables - added the teiid_session_set and teiid_session_get methods to maintain session variables.
• TEIID-2326 Shared Mat Views - materialized views from imported vdbs will be reused rather than recreated in each importing vdb.
• TEIID-2344 Automatic DB Version Detection - the JDBC translator database version property in most circumstances will not need to be set manually (non-available JDBC sources in partial results mode or source with connection factories that require an ExecutionContext to obtain a connection will require manual setting of the database version metadata property.  The affected sources are: db2, derby, oracle, postgresql, sqlserver, sybase, teiid)
• TEIID-2453 STRING_AGG - added the string_agg aggregate function for concatenating binary and character strings.

As you can see we have been quite busy.  There have been nearly 60 issues in total addressed as well as an initial commit of the column masking feature (more to come on this before final).  If all goes well additional ws* security features and mongo connectivity will make it into 8.4 also.

From here a CR can be expected in approximately 2 weeks.  Please take the time to try this release as there is plenty of time to get fixes in.  Beyond the 8.4 Final we now have the 8.4.1 and 8.5 release version targets active to take additional issues.

Thanks,
Steve

Teiid Designer 8.1 Final Released

(download/install)


Key feature for this release is providing the ability to develop and test your VDBs against both Teiid 7.7 and Teiid 8.3 runtimes (JBoss AS 7.1 and AS 5.1 platforms).  This feature allows managing your older VDB artifacts with latest tooling and provides a mechanism to migrate and test your data services on the newer server version.

Other features include:

• New relational object wizards (Table, View, Procedure, Index)
• Improved synchronization of workspace models with VDB artifacts
• Multi-source binding support in VDBs (TEIIDDES-472)
• (See also TEIID-2253 Multi-source improvements)
• New model importer using deployed data source through Teiid runtime (TEIIDDES-1575)

For more details, check out What's New !

(Teiid 8.4 + EAP 6.1) Alpha

Teiid has been using JBoss AS 7.1.1 as deployment server for last few versions. We wanted to move to later versions of the JBoss AS, but no JBoss AS community versions released after 7.1.1. Then JBoss EAP came along...

Teiid 8.4 Alpha1 is our first attempt to make Teiid deploy using EAP 6.1 Alpha. Even though EAP 6.1 Alpha, has a tag of "Alpha" this is much more stable version of the JBoss AS (7.2.Final).

If you are concerned about licensing issues with EAP 6.1 Alpha, please see this thread.

The installation is same as before, download the EAP 6.1 Alpha and then Teiid 8.4.Alpha1 and unzip the contents of Teiid archive over the EAP's directory.

One notable change you will see is, how Teiid deploys the resource adapters in this new version. Instead of deploying them as RAR files, they are now deployed as the JBoss Modules.  You can still use the RAR file deployment as before,as there is no changes there.

Take it for spin and let us know if you find any issues. Also, we are still considering features for 8.4 version, so bring them to our forums.

Thanks.

Ramesh & Steve.

Teiid 8.3.0.Final Released

It took us little more than 12 weeks from 8.2 Final, however Teiid 8.3.0.Final is released with many many exciting features.

The features we really excited about are

1. OData Support - Teiid can now either consume from a OData source or produce results using OData protocol. See related blogs on this topic.
2. JSON Support - Similar to how user can use SQLXML and define results in XML, Teiid is introducing JSON  language constructs, such that you can produce results in JSON. Now you can generate that Rest web service that returns JSON!!!
3. Source Temp Tables - create a temporary tables on sources, and generate plans that are efficient in pushing down more work towards the sources.

Many more.. See below for full list

Teiid 8.3 is still based on JBoss AS 7.1.1  The JBoss folks have decided not to release any community releases off of AS 7.x branches, however they recently announced  that EAP 6.1-alpha1 (a productised version of JBoss AS that RedHat offers subscription) is released to community developers.  We will release Teiid 8.4 based on EAP 6.1.x.

Highlighted Features:

• TEIID-2253 Multi-source improvements - multi-source planning was significantly improved, which will result in much better plans when using multi-source models. The user may also now configure the multi-source column name, auto-populate the multi-column in the actual metadata, and use non-literal multi-source column values.
• TEIID-1557 Generated Key Retrieval - JDBC now supports generated key retrieval, see the Client and the Developers Guides for more.
• TEIID-2311 Permission Conditions - data roles can now specify SQL conditions that will enforce row-based security and physical table insert/update checked constraints. See the Reference Guide for more.
• TEIID-2341 Join Planning Improvements - ANSI join structures can now be preserved with the preserve hint, i.e. FROM /*+ PRESERVE */ (tbl1 INNER JOIN tbl2 ON ...), which is similar to the Oracle ORDERED hint. Also sources restricted to only outer joins (such as Salesforce) can have INNER or effectively INNER joins pushed to them.
• TEIID-2312 Source security domains - Ldap, Salesforce, google-spreadsheet and ws (basic auth) translators can now define "security-domain" in their resource-adaptor configuration for security. When defined along with caller-identity the logged in user's credentials are passed to source for authentication.
• TEIID-2182 Events - Capture the VDB loading and unloading events now from registering a listener on EventDistributor
• TEIID-2241 CLI script for Standalone - Install Teiid into any AS7 standalone configuration using the provided CLI script.
• TEIID-2152 Basic MERGE support - support for a simplistic MERGE statement was added. See the Reference for more.
• TEIID-1757 OData Translator - A translator for OData based sources has been added. 
• TEIID-1854 OData Transport - Access any deployed VDB into Teiid using OData protocol, essentially Teiid is now a OData server.
• TEIID-2363 Concurrent Join Processing Enhancements - both sides of a join will processed more when possible. See also the org.teiid.joinPrefetchBatches system property in the Admin Guide.
• TEIID-196 Source Temporary Tables - temporary (locally scoped) tables may be defined at runtime against physical sources with a CREATE FOREIGN TEMPORARY TABLE statement. See the Reference for more.
• TEIID-2159 JSON Production - added the JSONARRAY, JSONARRAY_AGG, JSONOBJECT, and JSONPARSE constructs for producing JSON. See the Reference for more.
• TEIID-2248 Bulk Salesforce Inserts - added the ability for the Salesforce translator to perform bulk inserts.

This work was due in large part to the great participation from Teiid community users, a BIG thanks to all who participated in providing valuable input through their JIRAs and community suggestions.

Get Involved with Teiid:

Got a idea to better way plan a query or some cool feature that Teiid can implement, come and open a forum discussion, let's get it captured and put it on roadmap.

Wrote a blog? did any performance testing on Teiid? Tell us what worked and what didn't? Help us fix the issues to make Teiid successful.

Thank you.

Ramesh & Steve

Teiid 8.3 CR1 Available

Teiid 8.3 CR1 has been posted to the downloads.  The new features since beta2 include:

• TEIID-196 Source Temporary Tables - temporary (locally scoped) tables may be defined at runtime against physical sources with a CREATE FOREIGN TEMPORARY TABLE statement. See the Reference for more.
• TEIID-2159 JSON Production - added the JSONARRAY, JSONARRAY_AGG, JSONOBJECT, and JSONPARSE constructs for producing JSON. See the Reference for more.
• TEIID-2248 Bulk Salesforce Inserts - added the ability for the Salesforce translator to perform bulk inserts.

We've been getting great feedback on the beta releases, which has led to several important fixes - so keep up the good work with the CR!  Be sure to contribute any documentation or Javadoc updates that you see are needed as well. 

8.3 Final is on track for 2 weeks from now so now is a great time to help shape the 8.4 release.

Steve

Integrate OData Source Using Teiid

Teiid 8.3.x release has a new translator "odata", that supports consuming of OData sources. Using this translator a user can integrate data from a OData source with other sources in Teiid.

If you like to know what OData is and Teiid support for it, please read this previous post http://teiid.blogspot.com/2013/02/odata-support-in-teiid.html

In this blog, we will write a Teiid VDB, that shows how to consume a OData source, and how Teiid uses OData's CSDL metadata document to expose it in relational form such that it can be integrated with other sources.

So, let's begin..

In a Teiid system, OData translator is a extension of the "ws" web services translator, since all OData queries are REST based web service calls, and results are published in "atom/pub" or "json" format, it uses the same resource adapter "teiid-connector-ws.rar". For this example, we will use a example OData service at http://services.odata.org/Northwind/Northwind.svc/

The below is a CLI Script to create connection to OData source

 
/subsystem=resource-adapters/resource-adapter=northwindDS:add(archive=teiid-connector-ws.rar, transaction-support=NoTransaction)
/subsystem=resource-adapters/resource-adapter=northwindDS/connection-definitions=northwindDS:add(jndi-name=java:/northwindDS, class-name=org.teiid.resource.adapter.ws.WSManagedConnectionFactory, enabled=true, use-java-context=true)
/subsystem=resource-adapters/resource-adapter=northwindDS/connection-definitions=northwindDS/config-properties=EndPoint:add(value=http://services.odata.org/Northwind/Northwind.svc)
/subsystem=resource-adapters/resource-adapter=northwindDS:activate

Once the connection is created, let's create a Dynamic VDB (northwind-vdb.xml) that uses the connection created above and defines virtual database

<vdb name="northwind-rw" version="1">
    <model name="nw">
        <property name="importer.importKeys" value="true"/>
        <property name="importer.importProcedures" value="true">
         <source connection-jndi-name="java:/northwindDS" name="northwind-connector" translator-name="odata"/>
    </model>
</vdb>

When you deploy the above VDB in Teiid server, it automatically imports the metadata of this web service from http://services.odata.org/Northwind/Northwind.svc/$metadata and creates relational structure based on it.

For example, from the metadata call above one of the entity type in the CSDL document looks like below

<EntityType Name="Customer">
  <Key><PropertyRef Name="CustomerID"/></Key>
  <Property Name="CustomerID" Type="Edm.String" Nullable="false" MaxLength="5" Unicode="true" FixedLength="true"/>
  <Property Name="CompanyName" Type="Edm.String" Nullable="false" MaxLength="40" Unicode="true" FixedLength="false"/>
  <Property Name="ContactName" Type="Edm.String" Nullable="true" MaxLength="30" Unicode="true" FixedLength="false"/>
  <Property Name="ContactTitle" Type="Edm.String" Nullable="true" MaxLength="30" Unicode="true" FixedLength="false"/>
  <Property Name="Address" Type="Edm.String" Nullable="true" MaxLength="60" Unicode="true" FixedLength="false"/>
  <Property Name="City" Type="Edm.String" Nullable="true" MaxLength="15" Unicode="true" FixedLength="false"/>
  <Property Name="Region" Type="Edm.String" Nullable="true" MaxLength="15" Unicode="true" FixedLength="false"/>
  <Property Name="PostalCode" Type="Edm.String" Nullable="true" MaxLength="10" Unicode="true" FixedLength="false"/>
  <Property Name="Country" Type="Edm.String" Nullable="true" MaxLength="15" Unicode="true" FixedLength="false"/>
  <Property Name="Phone" Type="Edm.String" Nullable="true" MaxLength="24" Unicode="true" FixedLength="false"/>
  <Property Name="Fax" Type="Edm.String" Nullable="true" MaxLength="24" Unicode="true" FixedLength="false"/>
  <NavigationProperty Name="Orders" Relationship="NorthwindModel.FK_Orders_Customers" FromRole="Customers" ToRole="Orders"/>
  <NavigationProperty Name="CustomerDemographics" Relationship="NorthwindModel.CustomerCustomerDemo" FromRole="Customers" ToRole="CustomerDemographics"/>
</EntityType>

It will be converted to a relational table like below in the Teiid's metadata

CREATE FOREIGN TABLE Customers (
    CustomerID string(5) NOT NULL OPTIONS (FIXED_LENGTH TRUE),
    CompanyName string(40) NOT NULL,
    ContactName string(30),
    ContactTitle string(30),
    Address string(60),
    City string(15),
    Region string(15),
    PostalCode string(10),
    Country string(15),
    Phone string(24),
    Fax string(24),
    PRIMARY KEY(CustomerID)
) OPTIONS (UPDATABLE TRUE, EntityAlias 'Customer', EntityType 'NorthwindModel.Customer');

All "NavigationProperty" elements will be converted into FOREIGN KEYS, based on the "Association" and "AssociationSet" elements defined in the CSDL document.

 * If there is One-to-One relation, the FK are created at both ends of the table.
* If there is One-to-Many relation, at Many end of the table, a FK is created to single end of the table.
* If there are Many-to-Many relation, a virtual mapping table is created with PK's of the both tables.

The mapping table is created so that a VDB user can issue a SQL query that joins these both tables together in their user queries. One limitation is user can not select the columns from this mapping table, it can be strictly used only for the table join purposes. When a user queries their deployed VDB using JDBC/ODBC using SQL, those queries will be automatically converted into OData queries and results will be gathered and presented to user in relation form. For example you can issue a query like

select CustomerID, CompanyName from Customers

CustomerID	CompanyName
ALFKI	Alfreds Futterkiste
ANATR	Ana Trujillo Emparedados y helados
ANTON	Antonio Moreno Taquería
AROUT	Around the Horn

I hope the above gave glimpse of how OData translator works in Teiid. So, take it for test drive and let us know any issues or comments you may have on this subject.

 Ramesh..

Access Teiid from node.js

Are you writing a "node.js" application and would like to access Teiid VDB from it?

If "yes", this is currently possible using NPM package "pg".  Since, Teiid supports the PG transport, you can use this PostgreSQL client for "node.js" for accessing the Teiid.

For example if you have VDB called "northwind" deployed on your Teiid server, and it has table called "customers" and you are using default configuration such as

user = 'user'
password = 'user'
host = 127.0.0.1
port = 35432

then you can use following to write simple access program to Teiid

    
    var pg = require('pg');
    var connectionString = "pg://user:user@localhost:35432/northwind"
    pg.connect(connectionString, function(err, client) {
        client.query('SELECT CustomerID, ContactName, ContactTitle FROM Customers', function(err, result) {
        console.log(result.rows)
    });
    });

If you want access one row at a time, you can also use event mechanism and write

    var pg = require('pg');
    var connectionString = "pg://user:user@localhost:35432/northwind"
    pg.connect(connectionString, function(err, client) {
        var query = client.query('SELECT CustomerID, ContactName, ContactTitle FROM Customers');
        query.on('row', function(row) {
            console.log(row);
        });
        query.on('error', function(error) {
            console.log("failed to query");
        });
        query.on('end', function(error) {
            console.log("closing client");
            client.end();
        });
    });

For more information please take look at

https://npmjs.org/package/pg
https://github.com/brianc/node-postgres

If you do write an application using these please do share your experiences with us.