Would Teiid be suitable for federating non-relational document stores?

[delta2038]

We are evaluating Teiid for federating our data stores that are non-relational and mostly speak HTTP JSON. Although we went through the examples that involve vdbs that "consume" web services, we have this feeling that a non-relational document store as a data source is a bit awkward with regard to Teiid.

Specifically, we looked at the mongodb connector documentation, and what Teiid essentially did there (and admitted as such) was to enforce a relational model on top of mongo. That's fine, and mongo actually has the mechanism to build "relational"-like indexes to support queries.

But what about, say, CouchDB, where the querying capability is conceptually vastly different from relational? I can't wrap my head around how a connector/translator/resource adapter can be written to support relational queries without a huge amount of effort. Or, say the querying capabilities are supported by Solr for the underlying data store, which is also sufficiently different.

Does anybody have experience federating such a data store? Or can anybody validate or refute my suspicion that it's going to be a lot of efforts trying to do so with Teiid?

[rareddy]

We are evaluating Teiid for federating our data stores that are non-relational and mostly speak HTTP JSON. Although we went through the examples that involve vdbs that "consume" web services, we have this feeling that a non-relational document store as a data source is a bit awkward with regard to Teiid.

No, not at all. We use for CSV files, SalesForce, LDAP, XML just name a few. Users have done many different stores too. Ultimately it depends upon what you are trying to do. If you are trying to put a relational front for any source, then Teiid is the best fit. As it provides the all the necessary frameworks around relational constructs, and lets you map, as to "how you" view and map your store as a relational store.

Specifically, we looked at the mongodb connector documentation, and what Teiid essentially did there (and admitted as such) was to enforce a relational model on top of mongo. That's fine, and mongo actually has the mechanism to build "relational"-like indexes to support queries.

The goal is to expose MongoDB as relational store, so that the user who is not familiar with MongoDB, can easily work with it using SQL. MongoDB translator provides, some techniques where user has a choice to map their relational tables into MongoDB documents.

If user wants to consume JSON content directly from translator that fine, you can convert the JSON documents to Tables you can use JSON Functions. All I am saying, how you want to map is your choice. MongoDB we did not choose this because, we wanted to spare the users from mapping JSON document to table themselves, we wanted to give a simple abstraction layer that is already mapped. Also, the goal was not to cater to existing MongoDB data, but somebody wanting start a new database.

But what about, say, CouchDB, where the querying capability is conceptually vastly different from relational? I can't wrap my head around how a connector/translator/resource adapter can be written to support relational queries without a huge amount of effort. Or, say the querying capabilities are supported by Solr for the underlying data store, which is also sufficiently different.

I know one user wrote SOLR translator, but unfortunately they were not ready share with the community. Yes, it could be lot work, it would be much more if you were write what Teiid was providing. Also note with Teiid you can write in increments of functionality, where it can pick up functionality where your translator does not. For example, if you do not support "like" functionality, Teiid can provide it, once your translator provides it it can be pushed to your translator. Translator has the bulk of the work.

Does anybody have experience federating such a data store? Or can anybody validate or refute my suspicion that it's going to be a lot of efforts trying to do so with Teiid?

Call me biased +1 for using Teiid, I suggest take day or two to a prototype and see if it is serving your purpose. I have to say if you are looking to put relational front to your store then Teiid is best framework out there right now.

Ramesh..

[delta2038]

One major thing I'm trying to figure out is that I assume Teiid is delegating a (sub) SQL query to a constituent store as much as possible instead of taking in all content from that store. Please let me if my assumption is correct.

Then for something that's not relational, of course we can treat it relationally by providing a mapping between relational columns and data fields. However, what's less clear to me is that how do you avoid Teiid's taking in ALL the data and try to execute SQL on that copy. To us, we have hundreds of thousands of documents in our document store that aren't relational data. Teiid won't be able to delegate any SQL query anywhere.

So my current assumption for how this is going to work is that Teiid will consume a subset of this vast data, "preselected" by the constituent store (a view in CouchDB, for example) already.

Please let me know if any of the above "stream of consciousness" makes sense.

[rareddy]

Yes, there is Teiid comes with much needed help with non-relational stores. Either Teiid delegating a sub SQL query or full query as is really depends upon how a translator is developed. Every translator is developed with a set of "capabilities", as to what is can support and what it does not. For example, a File system does not support any filtering of the contents, so translator is marks such capability as not available, in those situations when a user SQL query comes with "WHERE" clause, Teiid is smart enough to figure out that translator can not support such filtering and steps in re-writes the query with out the WHERE clause and provides that behavior in its engine. This is a simple example, but if you see the API there are many more complex operations you can apply this principle to. Thus I was saying above that you can build your translator incrementally, as you add more capabilities in your translator it can push more (ALL) of the query down to the translator layer.

So, if you want to avoid Teiid taking all the data and avoid the expensive processing at its engine level, your translator need to open up these capabilities, so that it can send more restrictive query, where you interpret and transform to say SOLR queries and based on it only return subset of your vast data to Teiid.

The caveat is, user SQL has to be based on a Schema, and Teiid looks at Translator for metadata as to what is exposes as schema. If you do not want expose as schema, only as plain documents, then user has to write their mapping as to how they view the document as Table using the JSON functions above if the document is JSON document.

Ramesh..

[delta2038]

Thanks, Ramesh. This really helps.

[rareddy]

Great, Good Luck Ning. If you want contribute your work back to community, please talk to us as we would very much appreciate it, and if time permits we will also help you with any tasks.

Thanks

Ramesh..

[delta2038]

Trying to implement a custom translator/resource adapter. Does the documentation here apply and/or up-to-date? http://community.jboss.org/docs/DOC-12955

Thanks.

[rareddy]

The above is for setting up your development environment, and it should be up to date, if you encounter any issues let is know. General Teiid documentation is at Home - Teiid 8.5

[delta2038]

Update: Please disregard. We are in contact with the JBOSS rep for our company.

Ramesh,

My company (Atlanta, GA) is interested in knowing a bit more about commercial support for Teiid, especially the pricing model. Who should I contact to get more information? I found a form to submit to inquire about RedHat consulting service, but am wondering if there is a more direct way to ask about Teiid.

Thanks!