Wednesday, February 27, 2013

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.


Monday, February 25, 2013

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

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

The below is a CLI Script to create connection to OData source
/subsystem=resource-adapters/resource-adapter=northwindDS/connection-definitions=northwindDS:add(jndi-name=java:/northwindDS,, enabled=true, use-java-context=true)

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

When you deploy the above VDB in Teiid server, it automatically imports the metadata of this web service from$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"/>

It will be converted to a relational table like below in the Teiid's metadata
    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

ALFKIAlfreds Futterkiste
ANATRAna Trujillo Emparedados y helados
ANTONAntonio Moreno Taquería
AROUTAround 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.


Monday, February 18, 2013

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 =
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) {
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) {
        query.on('error', function(error) {
            console.log("failed to query");
        query.on('end', function(error) {
            console.log("closing client");
For more information please take look at

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

Tuesday, February 12, 2013

OData Support In Teiid

What Is OData?

"The Open Data Protocol (OData) is a Web protocol for querying and updating data that provides a way to unlock your data and free it from silos that exist in applications today. OData does this by applying and building upon Web technologies such as HTTP, Atom Publishing Protocol (AtomPub) and JSON to provide access to information from a variety of applications, services, and stores. The protocol emerged from experiences implementing AtomPub clients and servers in a variety of products over the past several years.  OData is being used to expose and access information from a variety of sources including, but not limited to, relational databases, file systems, content management systems and traditional Web sites. " - taken directly from

 OData is specification from OASIS committee, and this specification is backed by Microsoft, SAP AG, IBM, Citrix, Progress Software and WSO2.

Why you need OData?

In today's service oriented enterprise architectures more and more companies are exposing their enterprise data and business services using the REST based services. Even though REST defines easy ways to access your services, it does not define a unified calling semantics to access your services nor provides details data model. Thus everybody inventing their own REST API access mechanisms, defining specific ways define their data model to the end user application.  OData fills this gap, and provides specification that defines standard ways define CRUD operations on your data source along with standard ways to define your data/schema model.

How OData helps?

If an enterprise implements OData specification to expose their data and services, this layer self describes the data model and will define standard ways access and query in very powerful ways. Thus the client applications that are trying to use these services can write their applications using the rich metadata provided by OData and at the same time avoid vendor lock-in as it is a standard.

For example, Netflix a on-line movie streaming service, exposes details about their movie catalogue using OData. Using this service any developer who is writing a enterprise application or mobile application, can show case movies and details about them using OData. Since the OData also exposes the data/schema model about the services, they are self explanatory.

Another example is SAP Netweaver Gateway. SAP also uses OData to expose their services to end customers. The result, customers know exactly how to access SAP services and integrate them in their applications.

See the growing ecosystem of OData implementations.

What this got to do with Teiid?

Well, good question. A LOT.

Teiid is a data integration and virtualization engine. Teiid provides seamless integrations with many different varieties of sources like RDBMS, Flat Files, Web Services, packaged applications etc.  So, OData was a natural fit to provide features around data integration.

The support for OData in Teiid is two (2) fold.

OData Translator - Using this translator, you can bring in any OData sources as part of virtual database you create in Teiid and integrate this source's data with any other sources. Given SQL based user query, Teiid is capable of converting that request into a REST based OData call and fetch the results automatically and provide them in a relational perspective.

OData Server - If you have created a virtual database using Teiid, then with out any additional work, you can expose your VDB through OData. Previously Teiid only supported access through JDBC, ODBC and custom SOAP and REST  protocols. Now Teiid supports ability to access your VDB using OData.

Teiid uses couple different frameworks to make this possible. Primarily, the OData support is provided through OData4J project. Then Resteasy used for rest engine inside JEE container. Since Teiid server is already installed inside a JBoss Application Server, there is no more additional software you need to install. Both the translator and server are available when you install Teiid on JBoss AS.

How can I use them? any examples?

The support for both features is available in Teiid 8.3 Beta2  or later. I am in process of writing two more blogs in next couple weeks, one on each feature as show case, how to use them. So, stay tuned. If you are eager, there is documentation available.

The translator usage is no different to others, so, take a test drive and let us know if you have any questions or see any issues.



Monday, February 11, 2013

Teiid 8.3 Beta2 Posted

Teiid 8.3 Beta2 is now available in our downloads.  The new features in this this beta:
  • 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.
We are on track for the first candidate release in two weeks.  Looking ahead we'll be creating an 8.4 release target and should begin work on a 9.0 release by the end of that cycle.  Of course any feedback on the 8.3 release before final is greatly appreciated.

In addition to the AS Teiid Web Console management option, an initial plugin binary for RHQ 4.* has also been posted.  There should be another blog post on this along with subsequent pre-releases so stay tuned.