Using Teiid with two different Data sources

[kvajjala]

Hi,

I'm new to Teiid and I am looking at teiid to see if it can be used for my particular use case.

I have the same table in DB1(an indexing database) and Cassandra (could be any),

i would like to federate to either DB1 or Cassandra based on the type of query (for this I should be able to intercept the raw query from client to parse and make a decision)

I want to fire the query on DB1 and fetch the full results from Cassandra based on the results from DB1.

I want to be able to perform some custom logic before I fire a query and after I fetch the results.

I was trying to see if Teiid provides any plugins to do these tasks.

This may not be what it is meant for but would like to get your inputs.

Thanks

[gadeyne.bram]

Hi,

There are different things you could do here I think. Asume that you call your 2 database schemas a and b.

1)You could write a virtual view that UNIONS both results from a and b. I'm not sure what logic you want to execute before executing the query so this might be to limited.

2) You could write a virtual procedure with some parameters and select data based on these parameters from either a or b (or both). Virtual procedures give you a lot of functionality to execute additional logic.

In both cases the layout of the result set should be the same.

[rareddy]

Kay,

Adding to Bram's response.

i would like to federate to either DB1 or Cassandra based on the type of query (for this I should be able to intercept the raw query from client to parse and make a decision)

If your schema is same on both databases, you can use what Teiid calls a "multi-source" where you can select the source to execute by source name. See Multisource Models - Teiid 8.10 (draft) - Project Documentation Editor

If you need to "rewrite" the query before it is submitted to Teiid, then you need to look for some proxy JDBC drivers and re-write the query there. However, that may not be right architecture IMO. The way you want develop the application is such that you provide the abstraction layer to your users, where they are not writing the queries based on the type of the source. Teiid can help with that. You can define a logical view based on your schema in DB2 and Cassendra, then client issues the queries against this logical view, but in the view definition you can handle how the data for that view is handled, like does it come from DB2 or Cassendra or both. Look a virtual view and its transformation. I advise you to take look at "Data Federation" quick start example here Teiid Quick Starts - Teiid Examples - Project Documentation Editor

I want to fire the query on DB1 and fetch the full results from Cassandra based on the results from DB1.

Teiid calls this as dependent join. This can be automatic or handled through query hints. see Dependent Join Pushdown - Teiid 8.10 (draft) - Project Documentation Editor

I want to be able to perform some custom logic before I fire a query and after I fetch the results.

Again here you do not want really filter on individual results, you want filter on generically on view or type of results, here again the view transformation gives you all the necessary tools filter or massage the data you want to return.

HTH

Ramesh..

[kvajjala]

Hi  Folks,

Thanks for your valuable inputs. I would definitely look into the links you have suggested. The custom logic that i want to do was some RW locking on the tables in the indexing database before and after I fire the query. This is like "Initial SQL" typically used in reporting/BI tools. in This is kind of unique to our case and was hoping the virtualization layer could do that so it is transparent to the clients. Would it be possible to do that in Teiid?

Thanks

[rareddy]

Kay,

Ok. Looks like you are trying to build a indexing layer for the NoSQL type sources in your usecase.  I wrote similar article recently using SOLR as index store see Improve Teiid Query Performance using Apache SOLR as Index Store on a View

Coming back to your question, it should be possible. In the transformation query you can handle such details. However since to lock the DB2 table you would need to execute DB2 specific SQL, for that you have to use a functionality that we call "Direct Queries" where you can issue DB specific call. see JDBC Translator - Teiid 8.10 (draft) - Project Documentation Editor . I am assuming you want to do this during insert/update/delete calls not read.

Ramesh..

[kvajjala]

Hi Ramesh,

Thats exactly true. We are trying to put an index on Cassandra. But the index database is not DB2 (I just used an alias for the index database) it could be anything like SOLR or even Impala. The RW locking is for updates done by another process on the indexing server.  The locking could be using ZK or some distributed mechanism. So this has to be a custom Java Code that I need to run based on the query.

Coming back to your question, it should be possible. In the transformation query you can handle such details.

What is the transformation query and where do I get a handle to this.

Thanks

[rareddy]

So it is only index look up on this process then? If yes, the SOLR example link I gave is the perfect example on how things needs to be done in Teiid.

Teiid is query engine for virtual databases. You can define a logical view in Teiid, however the view contents can span over more than single physical datasource. To do that, for the definition of the view you not only define the view semantics, you also define how the view is constructed using the transformation SQL. See a DDL example here DDL Metadata - Teiid 8.10 (draft) - Project Documentation Editor

You can define select transformation for view, you can also define insert/update/delete "instead of" triggers for view to define the behavior when a user query issues a select/insert/update/delete on that view. What I am saying you have flexibility in defining this how this transformation SQL is defined, where you can do any custom processing.

I am thinking example like this

CREATE VIEW PARTS (
      PART_ID integer PRIMARY KEY,
      PART_NAME varchar(255),
      PART_COLOR varchar(30),
      PART_WEIGHT varchar(255)
  ) AS
      select a.id as PART_ID, a.name as PART_NAME, b.color as PART_COLOR, b.weight as PART_WEIGHT from (exec indexDB.native('native code')) as x, modelA.part a, modelB.part b where a.id = b.id

The above is when you need locking during select using common table expression, insert/update/delete more straight forward as trigger blocks are like stored procedures you define multiple statements. There may be other ways, this is one popped into my mind. Another one is using a user defined function in select to invoke custom java code to lock.

Ramesh..