10 Replies Latest reply on Mar 31, 2015 2:09 PM by rareddy

    How to redirect inside the JBoss Teiid to two Databases(Source)

    madhurika_sharma

      I have just started working with Teiid and i have a peculiar usecase.

      I have two different database. I want one VDB created for them.I mean to say i want to have a routing rule inside the Teiid .

      I have tried to do it. i imported both the table in my source ,customised them in differnt views as in one view i can import only one source.
      After creating View , i created VDB for it which got created but the problem is at the time of odata query i was able to see only one view.

      Can anyone please tell me the right way to continue with this.

        • 1. Re: How to redirect inside the JBoss Teiid to two Databases(Source)
          rareddy

          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.

           

          Ramesh..

          • 2. Re: How to redirect inside the JBoss Teiid to two Databases(Source)
            walla2sl

            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?

            • 3. Re: How to redirect inside the JBoss Teiid to two Databases(Source)
              rareddy

              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.

               

              Ramesh..

              • 4. Re: How to redirect inside the JBoss Teiid to two Databases(Source)
                shawkins

                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.

                • 5. Re: How to redirect inside the JBoss Teiid to two Databases(Source)
                  walla2sl

                  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.

                  • 6. Re: How to redirect inside the JBoss Teiid to two Databases(Source)
                    shawkins

                    > 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.

                    • 7. Re: How to redirect inside the JBoss Teiid to two Databases(Source)
                      walla2sl

                      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.

                      • 8. Re: How to redirect inside the JBoss Teiid to two Databases(Source)
                        rareddy

                        So all views consult this table and then generate a dynamic SQL query?

                        • 9. Re: How to redirect inside the JBoss Teiid to two Databases(Source)
                          walla2sl

                          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.

                          • 10. Re: How to redirect inside the JBoss Teiid to two Databases(Source)
                            rareddy

                            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.