3.6. Set Up a Staging Table

Procedure 3.2. Set Up a Staging Table

1. Build a VDB using the Teiid Designer for your use case.
2. Identify all the "Virtual Tables", that you think can use caching,
3. Click on the table, then in the Properties panel, switch the Materialized property to "true".
4. Right click on each materialized table, then choose Modeling - Create Materialized Views.
5. Click on ... button on the Materialization Model input box.
6. Select a "physical model" that already exists or create a new name for "physical model".
7. Click Finish.
   This will create the new model (if applicable) and a table with exact schema as your selected virtual table.
8. Verify that the "Materialization Table" property is now updated with name of table that has just been created.
9. Navigate to the new table materialized table that has been created, and click on "Name In Source" property.
10. Create two identical tables, one with "mv_" prefix and another with "st_" prefix. ("mv_" will stand for materialized view and "st_" will stand for the staging table.)
11. Make sure that the "Materialized Table" property is set to the table with "mv_" prefix. Also make sure that both these tables have their "Name in Source" edited and renamed with respective prefixed name.
12. Create the DDL file and the database. (The difference now is there are two identical tables for each materialized view.)
13. Load the contents into staging table using this query (substitute with the names of your own tables): INSERT INTO mv_view.ST_UpdateProduct SELECT * FROM Portfolio.UpdateProduct OPTION NOCACHE
14. Once the table is loaded, use the database's "RENAME" table command to rename the staging table ( st_xxx ) to the materialized table ( mv_xxx ). Here is the syntax to do this with MySQL: ALTER TABLE ST_UpdateProduct RENAME MV_UpdateProduct
    The reason you need to run the "rename" command is that the staging table can be used for loading the data (except for the very first time you use it). Meanwhile, materialized table will serve the user queries. Once the staging is loaded, rename will switch the identity of staged with the materialized such that any future queries will be against newly loaded contents. You can keep repeating this cycle in regular intervals and never serve empty results or wait for results to load. To populate the results via an ETL tool, you can do so too in this step by disregarding the above SQL command.

    Note

    You can script this entire process so that it runs at a regular interval. This example code shows you how to script it if you are using the MySQL database. (Make sure you provide the MySQL jdbc driver in the AdminShell classpath before running the script):

        // connect to mysql first  
        mysql = Sql.newInstance("jdbc:mysql://host_name:port/dbname", "username","password", "com.mysql.jdbc.Driver")  
        mysql.execute("TRUNCATE TABLE ST_UpdateProducts");                       
          
        // use the Teiid connection and load the staging table  
        sql=connect(${url}, ${user}, ${password});  
        sql.execute("INSERT INTO mv_view.st_UpdateProduct SELECT * FROM Portfolio.UpdateProduct OPTION NOCACHE");  
        sql.close();  
          
        // issue the rename command, so that materialization takes into effect  
        mysql.execute("ALTER TABLE ST_UpdateProduct RENAME MV_UpdateProduct");  
        mysql.close();  
          
        // create any indexes you need to  
    

    Add the script to your cron tab or Windows Scheduler and set it to run at a regular interval to keep your data fresh.