Federating among multiple databases is what Teiid does, so this is very familiar usecase. I tried to follow your question, but I do not think it clear to me where you are having issues? What is routing rule you are trying to achieve?
These are the steps you need to follow
1) Import from both databases and create two separate models
2) Then create views if you need to combine the data from two databases
3) create a vdb and deploy it, then use it.
Depending upon how you created VDB, the odata access to each table and view need to be qualified with the modelname.
Any thoughts on how one might route based on columns requested? For example, let's say I have two views -
1) a detail view, with all columns
2) an aggregate view, with a subset of those same columns, but not including highly detailed attributes
I would like to route requests to the aggregate view if all the requested columns exist there. Otherwise, I need to go down to the detail view.
Is this possible within Teiid or would one need to build a rules engine on top?
Selection view can not determined from a list of columns in a SELECT statement. We have features like ROLLUP and Column masking etc to aggregate or mask out unwanted data.
Also in Teiid 8.11, there is new feature being introduced to manipulate incoming SQL [TEIID-3369] Add a extension point to manipulate incoming sql - JBoss Issue Tracker that may be helpful too for this scenario.
The closest built in mechanism would be something like an optional join. With the view defined as:
select a.agg_col, d.detail_col ... from agg_view as a inner join /*+ optional */ detail_view as d on a.keycol = d.keycol
If you just ask for columns from a, then we'll simply remove the detail_view from the join. However if you ask for any column from the detail view, then we'll perform the join which isn't quite the same in terms of routing.
Thank you both for your prompt responses! We're going to use some pretty complex routing rules, so we may look at using Drools on top of Teiid.
Another question (off topic): does Teiid support dynamic aggregation? Meaning, can I define aggregation on columns in a view and then depending on columns selected, the view is rolled up to that level? The goal would be to dynamically update the GROUP BY clause with the selected columns and aggregate from there.
Thanks again - your responses are very useful/appreciated.
> We're going to use some pretty complex routing rules,
We also do have logic for determining usage of covering indexes for internal temp table that could be generally applicable.
> so we may look at using Drools on top of Teiid.
If you have anything that you think looks a general solution, then please keep us posted.
> does Teiid support dynamic aggregation? Meaning, can I define aggregation on columns in a view and then depending on columns selected, the view is rolled up to that level? The goal would be to dynamically update the GROUP BY clause with the selected columns and aggregate from there.
No the group by clause is fixed and the affect won't be changed based upon what columns are ultimately selected.
Wanted to give update on the routing rules.
We ended up building a simple table containing the tables, columns and ranking. It looks at the column combination to determine the appropriate table to query based on ranking. We can cache this table in memory to reduce overhead of querying for every incoming request.
So all views consult this table and then generate a dynamic SQL query?
Ramesh - right. Our API layer consults this table given an incoming logical query with column list and generates the Teiid query based on the table ranking that can fulfill that request (ie it must have all the columns being requested). We've ranked the aggregate tables manually, but another method could be to make it dynamic based on table row counts.
In that case take a look at my comment TEIID-3369, you should be able to integrate that into Teiid, such that application layer is abstracted from it.