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. |
Since the actual physical cache for materialized views is maintained external to the Teiid system, there is no predefined policy for clearing and managing the cache. These policies should be defined and enforced by administrators of the Teiid system based on their needs. |
Starting from Teiid version 8.5, Teiid gives the option to configure metadata on the view to control the loading and refreshing policies for cache. However, this is optional and administrators of Teiid system are free to choose to define and manage this externally through other means based on their needs. |
|
h2. User Managed Materialization Management |
*Typical Usage Steps* |
... |
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. |
h2. 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 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. {note} The Designer tooling for this feature is lacking at this moment but this will be added in coming releases. {note} *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. {code:lang=SQL} 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 integer, OnErrorAction varchar(25), Updated timestamp not null, PRIMARY KEY (VDBName, VDBVersion, SchemaName, Name) ); {code} * 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|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:MATVIEW_STAGE_TABLE|When MATVIEW_LOAD_SCRIPT property not defined, Teiid loads the cache contents into this table|true|n/a| |teiid_rel:ON_VDB_CREATE_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)|false|n/a| * Once the VDB with a model with above properties defined and deployed, the following sequence of events will take place # Upon the VDB deployment, teiid_rel:ON_VDB_CREATE_SCRIPT will be run on completion of the deployment. # Based on the [Cache Hint] defined on the view transformation, a scheduler entry will be created to run SYSADMIN.loadMatView procedure, which loads the cache contents. # This procedure, first inserts/updates a entry in teiid_rel:MATVIEW_STATUS_TABLE, which indicates that the cache is being loaded. # In same procedure, then teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT will be run if defined # 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. # Then, teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT will be run, to close out and create any indexes on the mat view table. # The procedure, then will set teiid_rel:MATVIEW_STATUS_TABLE entry to "LOADED" and valid. # Based on the Cache Hint, the SYSADMIN.matViewStatus is ran by the Scheduler, to queue further cache re-loads. # When VDB is un-deployed (not when server is restarted) the teiid_rel:ON_VDB_DROP_SCRIPT script will be run. 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 partially 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 {code:lang=XML} <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <vdb name="sakila" version="1"> <description>Shows how to call JPA entities</description> <property name="UseConnectorMetadata" value="cached" /> <model name="pg"> <source name="pg" translator-name="postgresql-override" connection-jndi-name="java:/sakila-ds"/> </model> <model name="sakila" type="VIRTUAL"> <metadata type="DDL"><![CDATA[ CREATE VIEW actor ( actor_id integer, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update timestamp NOT NULL ) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'TRUE', MATERIALIZED_TABLE 'pg.public.mat_actor', "teiid_rel:MATERIALIZED_STAGE_TABLE" 'pg.public.mat_actor_staging', "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true', "teiid_rel:MATVIEW_STATUS_TABLE" 'pg.public.status', "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute pg.native(''truncate table mat_actor_staging'');', "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute pg.native(''ALTER TABLE mat_actor RENAME TO mat_actor_temp;ALTER TABLE mat_actor_staging RENAME TO mat_actor;ALTER TABLE mat_actor_temp RENAME TO mat_actor_staging;'')', "teiid_rel:MATVIEW_SHARE_SCOPE" 'NONE', "teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION', "teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM pg.public.status WHERE Name=''actor'' AND schemaname = ''sakila''') AS /*+ cache(ttl:180000)*/SELECT actor_id, first_name, last_name, last_update from pg."public".actor; ]]> </metadata> </model> <translator name="postgresql-override" type="postgresql"> <property name="SupportsNativeQueries" value="true"/> </translator> </vdb> {code} |
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 loading and refreshing policies for cache. However, this is optional and administrators of Teiid system are free to choose to define and manage this externally through other means based on their needs.
Typical Usage Steps
insert into target\_table select * from matview option nocache
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.
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 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
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 integer, OnErrorAction varchar(25), Updated timestamp not null, PRIMARY KEY (VDBName, VDBVersion, SchemaName, Name) );
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 | 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:MATVIEW_STAGE_TABLE | When MATVIEW_LOAD_SCRIPT property not defined, Teiid loads the cache contents into this table | true | n/a |
teiid_rel:ON_VDB_CREATE_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) | false | n/a |
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 partially 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
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <vdb name="sakila" version="1"> <description>Shows how to call JPA entities</description> <property name="UseConnectorMetadata" value="cached" /> <model name="pg"> <source name="pg" translator-name="postgresql-override" connection-jndi-name="java:/sakila-ds"/> </model> <model name="sakila" type="VIRTUAL"> <metadata type="DDL"><![CDATA[ CREATE VIEW actor ( actor_id integer, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update timestamp NOT NULL ) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'TRUE', MATERIALIZED_TABLE 'pg.public.mat_actor', "teiid_rel:MATERIALIZED_STAGE_TABLE" 'pg.public.mat_actor_staging', "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true', "teiid_rel:MATVIEW_STATUS_TABLE" 'pg.public.status', "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute pg.native(''truncate table mat_actor_staging'');', "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute pg.native(''ALTER TABLE mat_actor RENAME TO mat_actor_temp;ALTER TABLE mat_actor_staging RENAME TO mat_actor;ALTER TABLE mat_actor_temp RENAME TO mat_actor_staging;'')', "teiid_rel:MATVIEW_SHARE_SCOPE" 'NONE', "teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION', "teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM pg.public.status WHERE Name=''actor'' AND schemaname = ''sakila''') AS /*+ cache(ttl:180000)*/SELECT actor_id, first_name, last_name, last_update from pg."public".actor; ]]> </metadata> </model> <translator name="postgresql-override" type="postgresql"> <property name="SupportsNativeQueries" value="true"/> </translator> </vdb>