8 Replies Latest reply on Mar 10, 2012 2:37 PM by markaddleman

    Views:  Generating the select transformation on the fly

    markaddleman

      We have a need to create views that whose query definition is generated at runtime.  For example, I want to create a view that dynamically UNIONs together tables/views found in SYS.TABLES that match a particular naming convention.  We currently have a custom execution factory that generates a query of the form "SELECT ... FROM (<unioned select statements)..." and connects back into Teiid to execute the generated query. 

       

      The major problem with this approach is that once we connect back into Teiid, we lose traceability on the query plan making it difficult to follow what's going on with the original query and it complicates many of our support conversations with Steven, Ramesh, etc. 

       

      In light of the upcoming view support in dynamic VDBs, I'd like to scrap our current approach to generating views but I don't see how the proposal would give me a programmatic hook to generate the select transformation.

        • 1. Re: Views:  Generating the select transformation on the fly
          shawkins

          Hi Mark,

           

          What determines the dynamic nature?  Is it something from the user query or is it something that you know ahead of time, but changes?

           

          Steve

          • 2. Re: Views:  Generating the select transformation on the fly
            rareddy

            Steve,

             

            In the case that it is not dependent on the user query, could we based on some event do a implicit re-load of the transformation queries with the help new DefaultMetadataRepository class? One thing I am not sure in this case is, how one could force the resolved group to resolve again?

             

            Thanks

             

            Ramesh..

            • 3. Re: Views:  Generating the select transformation on the fly
              shawkins

              Ramesh,

               

              We'd mentioned this breifly with Rakesh I believe.  The ability exists already to use the EventDistributor (which skips parse/resolve/validation) or a DDL alter statement to update a single view in 7.7, but we haven't yet offered a mechanism to bulk redefine (other than at start-up) at either an entire vdb or schema level.

               

              Steve

              • 4. Re: Views:  Generating the select transformation on the fly
                rareddy

                Steve,

                 

                That makes sense, I do not think whole sale refresh of all views is applicable in real world usecase. If someone wants to do that they can issue reload of the VDB. Loading a specific view is what is required based on requirement like Mark's. In remote case some one wants to do it for entire VDB, they should know their metadata model, so that they can use individual alter view mechanism iteratively on every view.

                 

                Also in 8.0, I did exposed the EventDistributor as service, but did not bind it to a JNDI tree, should I do that? In 8.0 the entry point is really a subsystem, when someone designs a subsystem they have acceess to service. But if usecase is to access from a EJB or Servlet then we need to bind it to the JNDI tree.

                 

                Mark,

                 

                To re-iterate what Steve's suggestion,  in the Teiid engine, you have two options available to accomplish

                 

                - You can deploy a bean that can look up 'EventDistributor' class from JNDI tree in the Teiid VM (in Jboss AS this is easy writing xml file), and call 'setViewDefinition' on it.  Your bean must encapsulate the event mechanism when it needs to call based on your requirements.

                 

                - Use can issue a "alter" call against the VDB using any JDBC connection.

                 

                Ramesh..

                • 5. Re: Views:  Generating the select transformation on the fly
                  shawkins

                  To get the EventDistributor in 7.x, you lookup the runtime engine deployer, which is an instance of a EventDistributorFactory.  It looks like that responsibiltiy has moved over to EventDistributorFactoryService, so it does seem appropriate to bind that as well.

                  • 6. Re: Views:  Generating the select transformation on the fly
                    rareddy
                    • 7. Re: Views:  Generating the select transformation on the fly
                      markaddleman

                      Our primary use-case is about UNIONing together a set of tables that are not known until just after the VDB is deployed.  For this use-case, the view definition is static until the next VDB redeploy.  I hadn't thought about it before, but we could we could satisfy this use case by altering the view definition just after the VDB is deployed and before application accepts connections.  I believe this is possible under current Teiid capabilities.

                       

                      Currently, the view definition is computed at query time, for every query.  I believe we only take advantage of this dynamism for a single use-case:  Again, UNIONing together tables that come from a dynamically changing configuration file.

                      • 8. Re: Views:  Generating the select transformation on the fly
                        markaddleman

                        re:  Using the EventDistributor -  I remember taking a look at this a while ago when you first suggested it.  At the time, I wasn't sure if our views needed to be defined within the context of a user query.  Now, I'm reasonably certain they do not.   I'll look at that mechanism again.  Or, perhaps, use ALTER VIEW (which seems pretty straightforward).