Skip to end of metadata
Go to start of metadata

External materialized views cache their data in an external database system. External materialized views give the administrator full control over the loading and refresh strategies.

Starting from Teiid version 8.5, Teiid gives the option to configure metadata on the view to control the simple load and refresh policies. Administrators of Teiid system are free to manage their external materialization table through other means based on their needs.

User Managed Materialization Management

Typical Usage Steps

  • Create materialized views and corresponding physical materialized target tables in Designer. This can be done through setting the materialized and target table manually, or by selecting the desired views, right clicking, then selecting Modeling->"Create Materialized Views"
  • Generate the DDL for your physical model materialization target tables. This can be done by selecting the model, right clicking, then choosing Export->"Metadata Modeling"->"Data Definition Language (DDL) File". This script can be used to create the desired schema for your materialization target on whatever source you choose.
  • Determine a load and refresh strategy. With the schema created the most simplistic approach is to just load the data. The load can even be done through Teiid with

That however may be too simplistic because your index creation may be more performant if deferred until after the table has been created. Also full snapshot refreshes are best done to a staging table then swapping it for the existing physical table to ensure that the refresh does not impact user queries and to ensure that the table is valid prior to use.

Metadata Based Materialization Management

Users when they are designing their views, they can define additional metadata on their views to control the loading and refreshing of external materialization cache. This option provides a limited but a powerful way to manage the materialization views. For this purpose, SYSADMIN Schema model in your VDB defines three stored procedures (loadMatView, updateMatView, matViewStatus) in its schema. Based on the defined metadata on the view, and these SYSADMIN procedures a simple scheduler automatically starts during the VDB deployment and loads and keeps the cache fresh.

The Designer tooling for this feature is lacking at this moment but this will be added in coming releases.

Usage Steps

  • To manage and report the loading and refreshing activity of materialization view, Teiid expects the user to define "Status" table with following schema in any one of the source models. Create this table on the physical database, before you do the import of this physical source.

NOTE: MariaDB have Silent Column Changes function, according to MariaDB document, 'long' type will silently change to 'MEDIUMTEXT' , so If execute above schema in MariaDB, 'Cardinality' and 'LoadNumber' column should change to 'bigint' type.

  • Create Views and corresponding physical materialized target tables in Designer or using DDL in Dynamic VDB. This can be done through setting the materialized and target table manually, or by selecting the desired views, right clicking, then selecting Modeling->"Create Materialized Views" in the Designer.
  • Define the following extension properties on the view.
Property Name Description Optional Default Value
teiid_rel:ALLOW_MATVIEW_MANAGEMENT Allow Teiid based management false false
teiid_rel:MATVIEW_STATUS_TABLE fully qualified Status Table Name defined above false n/a
teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT semi-colon(;) separated DDL/DML commands to run before the actual load of the cache, typically used to truncate staging table true When not defined, no script will be run
teiid_rel:MATVIEW_LOAD_SCRIPT semi-colon(;) separated DDL/DML commands to run for loading of the cache true will be determined based on view transformation
teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT semi-colon(;) separated DDL/DML commands to run after the actual load of the cache. Typically used to rename staging table to actual cache table. Required when MATVIEW_LOAD_SCRIPT not defined to copy data from teiid_rel:MATVIEW_STAGE_TABLE to MATVIEW table true When not defined, no script will be run
teiid_rel:MATVIEW_SHARE_SCOPE Allowed values are {NONE, VDB, SCHEMA}, which define if the cached contents are shared among different VDB versions and different VDBs as long as schema names match true NONE
teiid_rel:MATERIALIZED_STAGE_TABLE When MATVIEW_LOAD_SCRIPT property not defined, Teiid loads the cache contents into this table. Required when MATVIEW_LOAD_SCRIPT not defined false
n/a
teiid_rel:ON_VDB_START_SCRIPT DML commands to run start of vdb true n/a
teiid_rel:ON_VDB_DROP_SCRIPT DML commands to run at VDB un-deploy; typically used for cleaning the cache/status tables true n/a
teiid_rel:MATVIEW_ONERROR_ACTION Action to be taken when mat view contents are requested but cache is invalid. Allowed values are (THROW_EXCEPTION = throws an exception, IGNORE = ignores the warning and supplied invalidated data, WAIT = waits until the data is refreshed and valid then provides the updated data) true WAIT
teiid_rel:MATVIEW_TTL time to live in milliseconds. Provide property or cache hint on view transformation - property takes precedence. true 2^63 milliseconds - effectively the table will not refresh, but will be loaded a single time initially
  • Once the VDB with a model with above properties defined and deployed, the following sequence of events will take place
  1. Upon the VDB deployment, teiid_rel:ON_VDB_START_SCRIPT will be run on completion of the deployment.
  2. Based on the teiid_rel:MATVIEW_TTL defined a scheduler entry will be created to run SYSADMIN.loadMatView procedure, which loads the cache contents.
  3. This procedure, first inserts/updates a entry in teiid_rel:MATVIEW_STATUS_TABLE, which indicates that the cache is being loaded.
  4. In same procedure, then teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT will be run if defined
  5. In same procedure, then teiid_rel:MATVIEW_LOAD_SCRIPT will be run if defined, otherwise one will be automatically created based on the view's transformation logic.
  6. Then, teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT will be run, to close out and create any indexes on the mat view table.
  7. The procedure, then will set teiid_rel:MATVIEW_STATUS_TABLE entry to "LOADED" and valid.
  8. Based on the teiid_rel:MATVIEW_TTL, the SYSADMIN.matViewStatus is ran by the Scheduler, to queue further cache re-loads.
  9. When VDB is un-deployed (not when server is restarted) the teiid_rel:ON_VDB_DROP_SCRIPT script will be run.
The start/stop scripts are not cluster aware - that is they will run on each cluster member as the VDB is deployed. When deploying into a clustered environment, the scripts should be written in such a way as to be cluster safe.

User can any time run SYSADMIN.updateMatView procedure to partially update the cache contents rather than complete refresh of contents with SYSADMIN.loadMatview procedure. When partial update is run the cache expiration time is renewed for new term based on Cache Hint again.

A sample Dynamic VDB with these properties can be defined as below

Materialization with Embedded Server

With Dynamic VDB and above table extension properties, we can set up External Materialization to Embedded Server.

Usage Steps

  • Create MATERIALIZED_TABLE, MATVIEW_STATUS_TABLE and MATERIALIZED_STAGE_TABLE

MATERIALIZED_TABLE is necessary, it can be any name, but it's structure should match to materialization view.
MATVIEW_STATUS_TABLE is necessary, it should be named as 'status', it's structure like the below schema

CREATE TABLE status
(
  VDBName varchar(50) not null,
  VDBVersion integer not null,
  SchemaName varchar(50) not null,
  Name varchar(256) not null,
  TargetSchemaName varchar(50),
  TargetName varchar(256) not null,
  Valid boolean not null,
  LoadState varchar(25) not null,
  Cardinality long,
  Updated timestamp not null,
  LoadNumber long not null,
  PRIMARY KEY (VDBName, VDBVersion, SchemaName, Name)
);

If 'teiid_rel:MATVIEW_LOAD_SCRIPT ' MATERIALIZED_STAGE_TABLE is necessary, it can be any name, but it's structure should match to materialization view.

  • Create Materialization Views

Create Materialization Views and corresponding physical materialized target table, staging table, status table, it should be manually created in a model of Dynamic VDB, the model type should be 'VIRTUAL'.

  • Set up Embedded Server

Excepting add ConnectionFactory and Translator to Embedded Server, TransactionManager also is necessay, the set up code snipets snippet like

EmbeddedServer server = new EmbeddedServer();
...
server.addConnectionFactory("name", Object);
...
server.addTranslator("name", ExecutionFactory);
...
EmbeddedConfiguration config = new EmbeddedConfiguration();
config.setTransactionManager(EmbeddedHelper.getTransactionManager());
server.start(config);
...
server.deployVDB("matView-vdb.xml");

Usage Example

The following steps show a sample Dynamic VDB with Materialization view in Embedded Server

  • Run below schema script against a H2 Database before deploy Dynamic VDB

CREATE TABLE status(
  VDBName varchar(50) not null,
  VDBVersion integer not null,
  SchemaName varchar(50) not null,
  Name varchar(256) not null,
  TargetSchemaName varchar(50),
  TargetName varchar(256) not null,
  Valid boolean not null,
  LoadState varchar(25) not null,
  Cardinality long,
  Updated timestamp not null,
  LoadNumber long not null,
  PRIMARY KEY (VDBName, VDBVersion, SchemaName, Name)
);

CREATE TABLE h2_test_mat(
   product_id integer,
   SYMBOL varchar(16)
);

CREATE TABLE mat_test_staging(
   product_id integer,
   SYMBOL varchar(16)
);

CREATE TABLE  PRODUCT (
   ID integer,
   SYMBOL varchar(16),
   COMPANY_NAME varchar(256),
   CONSTRAINT PRODUCT_PK PRIMARY KEY(ID)
);

INSERT INTO PRODUCT (ID,SYMBOL,COMPANY_NAME) VALUES(100,'IBM','International Business Machines Corporation');
INSERT INTO PRODUCT (ID,SYMBOL,COMPANY_NAME) VALUES(101,'DELL','Dell Computer Corporation');
INSERT INTO PRODUCT (ID,SYMBOL,COMPANY_NAME) VALUES(102,'HPQ','Hewlett-Packard Company');
INSERT INTO PRODUCT (ID,SYMBOL,COMPANY_NAME) VALUES(103,'GE','General Electric Company');
INSERT INTO PRODUCT (ID,SYMBOL,COMPANY_NAME) VALUES(104,'SAP','SAP AG');
INSERT INTO PRODUCT (ID,SYMBOL,COMPANY_NAME) VALUES(105,'TM','Toyota Motor Corporation');

  • Create Dynamic VDB with DDL content as below

        <metadata type="DDL"><![CDATA[
        CREATE VIEW MatView     (
            product_id integer,
            symbol string
        )OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'TRUE',         
               MATERIALIZED_TABLE 'Accounts.h2_test_mat',
               "teiid_rel:MATVIEW_TTL" 20000,
               "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute accounts.native(''truncate table mat_test_staging'');',
               "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT"  'execute accounts.native(''ALTER TABLE h2_test_mat RENAME TO h2_test_mat_temp'');execute accounts.native(''ALTER TABLE mat_test_staging RENAME TO h2_test_mat'');execute accounts.native(''ALTER TABLE h2_test_mat_temp RENAME TO mat_test_staging'');',
               "teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM Accounts.status WHERE Name=''MatView'' AND schemaname = ''Stocks''',
               "teiid_rel:MATERIALIZED_STAGE_TABLE" 'Accounts.mat_test_staging',
               "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
               "teiid_rel:MATVIEW_STATUS_TABLE" 'status',
               "teiid_rel:MATVIEW_SHARE_SCOPE" 'NONE',
               "teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION')
        AS
    SELECT  A.ID, A.symbol FROM  Accounts.PRODUCT AS A;
        ]]> </metadata>
    </model>

  • Run Test

Deploy Dynamic VDB to Embedded Server, run below code

Thread.currentThread().sleep(10 * 1000); // wait loadMatView finish
executeQuery(conn, "select * from MatView");
executeUpdate(conn, "INSERT INTO PRODUCT (ID,SYMBOL,COMPANY_NAME) VALUES(2000,'RHT','Red Hat Inc')");

Thread.currentThread().sleep(30 * 1000); // make sure MatView be updated

executeQuery(conn, "select * from MatView");
executeUpdate(conn, "DELETE FROM PRODUCT  WHERE ID = 2000");

Thread.currentThread().sleep(30 * 1000); // make sure MatView be updated

executeQuery(conn, "select * from MatView");

The test result is the MatView can catch the updated of underlying source table 'PRODUCT'.

Labels:
None
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. Aug 11, 2014

    The steps indicate: teiid_rel:ON_VDB_CREATE_SCRIPT is a property. Is this new, or should this be: teiid_rel:ON_VDB_START_SCRIPT

    1. Aug 12, 2014

      Thanks fixed now.

  2. May 28, 2015

    'MATVIEW_STATUS_TABLE' and  'MATVIEW_LOAD_SCRIPT' can not be null simultaneously, so I set 'MATVIEW_STATUS_TABLE' optional from true to false.

  3. May 29, 2015

    'teiid_rel:MATVIEW_TTL' is necessary, if this not set, the schedule job never started, so change Optional from true to false.

    1. May 29, 2015

      We'll have to have Ramesh weigh in here on the intention. As the before script still runs I would expect that the job should be at least run the initial time and not rescheduled. Alternatively he may intend that no ttl means that the user is manually responsible to triggering a refresh/load.

      1. May 30, 2015

        TTL can also be specified in the query thus it has been defined as optional. We can make this clear in docs.

        1. May 31, 2015

          What we're saying is that the view is never materialized in the first place if a ttl is not set. We just want to make sure whether that is intentional.

          1. May 31, 2015

            Ok, IMO we should have been materializing with indefinite time, so that is bug. Is that what we do with the internal materialization?

          2. Jun 01, 2015

            I think 'no ttl means that the user is manually responsible to execute loadMatView' it's make sense, also the behavior how update/refresh table also be manually responsible by users.