8 Replies Latest reply on Jul 29, 2011 8:28 AM by markaddleman

    Programmatically creating materialized views

    markaddleman

      I'm trying to programmatically construct a VDB using org.teiid.metadata.* classes.  It is going well except for defining materialized views.  As near as I can tell, the only thing I need to do is set some data in org.teiid.metadata.Table.  The following methods seem to be relevant:

      1. setMaterialized(boolean)
      2. setMaterializedStageTable(Table)
      3. setMaterializedTable(Table)
      4. setSelectTransformation(String)
      5. setVirtual(boolean)

      Currently, I am only using setMaterialized(true), setSelectTransformation(select_string_for_data).  As it stands now, our materialized views get created and appear to be loaded with data but the views appear very buggy.  Sometimes we get NPEs other times, queries return incorrect results.

       

      Should I set data using the other methods?  What is the difference between setMateterializedTable and setMaterializedStageTable?

        • 1. Re: Programmatically creating materialized views
          markaddleman

          A related question:  The documentation indicates that internal materialized views will create indexes when there are constraints on the table.  Can those constraints be primary keys?  Do I specify the primary key on the materialized view table object?  Or the "source" table?

          • 2. Re: Programmatically creating materialized views
            rareddy

            On the Materialized View. We will also reply to above question in little bit.

            • 3. Re: Programmatically creating materialized views
              rareddy

              Mark,

               

              Are you using Internal Materialized Views (IMV) or External Materialized View (EMV)?

               

              The usage pattern you shown above

               

              Table view = ...
              view.setMaterialized(true);
              view.setSelectTransformation(sql);
              

               

              will work for IMV. Note that in the select transformation above you need to use a cache hint to cache the contents for certain duration. If you are not using the cache hint, I am not sure the behaviour is, I suspect it would be going to the source every time, as the cached contents will be invalidated on arrival. For details on cache hint check out the Caching Guide. If you still see issues, please post more details.

               

              For EMV, you need to set the

               

              Table view = ...
              view.setMaterialized(true);
              view.setSelectTransformation(sql);
              view.setMaterializedTable(mvTable);
              view.setMaterializedStageTable(stageTable)
              

               

              in this scenario, you are saying that the contents of the mvTable are the contents of your view. The refreshing the content of the mvTable is entirely an external process controlled by you as user. Teiid does not do any checking of staleness factor. Staging table is used as temporary table during the external refresh process, so that the query engine can still serve the results from the mvTable during loading of the contents. Once the updated contents are loaded to the Staging table, then you can use table rename commands in database to move statging into mv table. See this general guide to Materialization.

               

              Hope this helps.

               

              Ramesh..

              1 of 1 people found this helpful
              • 4. Re: Programmatically creating materialized views
                markaddleman

                Thanks, Ramesh.  We are not applying a cache hint to eh select transformation query.  Do you see a problem with caching it forever?  (ie "/*+ cache */")  Will refreshing the materialized view still work?

                • 5. Re: Programmatically creating materialized views
                  markaddleman

                  Also, can you give an example of adding constraints that trigger the IMV view to create indexes?

                  • 6. Re: Programmatically creating materialized views
                    rareddy

                    Mark,

                     

                    Yes, /*+ cache */ means cache forever. You can either use TTL like /*+ cache(ttl:300000)*/ to cache 300000 milli seconds, or you can refresh any time using

                     

                    call sysadmin.refreshMatView(viewname=>'view', invalidate=>true);
                    

                     

                    Ramesh..

                    • 7. Re: Programmatically creating materialized views
                      rareddy

                      I am not sure I understand the question. You set the indexes on the view by

                       

                      Table view = new Table...
                      view.setIndexes(...); // primary key
                      view.setUniqueKeys(...); // unique keys
                      

                       

                      when you materialize the table, along with the data the indexes will be created and cached.

                      • 8. Re: Programmatically creating materialized views
                        markaddleman

                        That's what I thought; I just wanted to make sure.  Thanks