Blog

Teiid Materialized Views

• Materialized Views
• Internal Materialization
  • Update Strategies in VDB Deployment
  • The MatViews Table
  • Process of SYSADMIN.refreshMatView
  • A sample Dynamic VDB defining an internal materialization
• External Materialization
  • Mat View Status
  • Mat View Loading
  • Mat View Updating
  • MaterializationManager based Management
• MaterializationManager FinishDeployment
• Materialization Example
  • Example.1: External Materialization with Staging Table

Materialized Views

Teiid Materialized views are just like other views, but their transformations are pre-computed and stored just like a regular table. When queries are issued against the views through the Teiid Server, the cached results are used.

There are 2 categories of Materialization:

• External Materialization - 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.
• Internal Materialization - Internal materialization creates Teiid temporary tables to hold the materialized table. While these tables are not fully durable, they perform well in most circumstances and the data is present at each Teiid instance which removes the single point of failure and network overhead of an external database. Internal materialization also provides more built-in facilities for refreshing and monitoring.

Detailed documentation from Teiid doc.

Internal Materialization

An Internal Materialization have to meet the following 3 conditions:

1. the view should be VIRTUAL, which means the View should be in a VIRTUAL model
2. materialized have to be set and value should be true
3. teiid_rel:ALLOW_MATVIEW_MANAGEMENT should be added and value should be true

A sample Dynamic VDB defining an internal materialization

Update Strategies in VDB Deployment

The MaterializationManager FinishDeployment judge Materialized View is Internal by MATERIALIZED_TABLE option, if MATERIALIZED_TABLE is set, the Materialized View is Internal.

The Update Strategies controled by teiid_rel:MATVIEW_TTL option:

• If teiid_rel:MATVIEW_TTL is set, a Scheduled ExecutorService will be triggered, which execute SYSADMIN.refreshMatView() repeatedly with a specified TTL interval
• If teiid_rel:MATVIEW_TTL is not set or less than/equals 0, the Materialized View be load only one time

MaterializationAction process

Appendix-1

1. If MATERIALIZED_TABLE is not configured, the View is Internal
2. If teiid_rel:MATVIEW_TTL is not configured, or value <= 0, sql select count(*) from MatView pass to executeAsynchQuery(), and execute once.
3. If teiid_rel:MATVIEW_TTL is configured and value > 0, sql call SYSADMIN.refreshMatView('MatView', true) pass to executeAsynchQuery(), and execute repeatedly

Appendix-2

DQPCore Execute Request

NOTE: 2 kinds of probable SQL will be passed to this method, select count(*) from MatView or call SYSADMIN.refreshMatView('MatView', true)

The MatViews Table

SYSADMIN.MatViews supplies information about all the materailized views in the virtual database. It’s creation SQL looks

CREATE FOREIGN TABLE MatViews (
        VDBName string(255) NOT NULL,
        SchemaName string(255) NOT NULL,
        Name string(255) NOT NULL,
        TargetSchemaName string(255),
        TargetName string,
        Valid boolean,
        LoadState string(255),
        Updated timestamp,
        Cardinality integer,
        PRIMARY KEY (VDBName, SchemaName, Name)
);

SYSADMIN.MatViews is a system tables, in this section we will look how System tables be queries in Teiid.

Process of SYSADMIN.refreshMatView

Create Temp Table

As below figure, a Internal Mat View reference a Internal Local Temp Table. The syntax of name a Temp Table is add prefix #MAT_ to Internal Mat View, for example, #MAT_TESTMAT.SAMPLEMATVIEW is the internal temp table for Internal Mat View TESTMAT.SAMPLEMATVIEW.

Insert Temp Table

A sample Dynamic VDB defining an internal materialization

<model name="StocksMatModel" type="VIRTUAL">
    <metadata type="DDL"><![CDATA[
        CREATE view stockPricesInterMatView
                (
            product_id integer,
            symbol string,
            price bigdecimal,
            company_name   varchar(256)
                ) OPTIONS (materialized true,         
               "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true', 
               "teiid_rel:MATVIEW_PREFER_MEMEORY" 'true',
               "teiid_rel:MATVIEW_TTL" 600000,
               "teiid_rel:MATVIEW_UPDATABLE" 'true', 
               "teiid_rel:MATVIEW_SCOPE" 'VDB')
               AS SELECT  A.ID, S.symbol, S.price, A.COMPANY_NAME FROM Stocks.StockPrices AS S, Accounts.PRODUCT AS A WHERE S.symbol = A.SYMBOL;
        ]]>
    </metadata>
</model>

External Materialization

There are 4 aspects logic in Teiid External Materialization:

1. Mat View Status
2. Mat View Loading
3. Mat View Updating
4. MaterializationManager based Management

Mat View Status

Each External Materialization View has reference a row record in a status table, which represents the Mat View Status including: Target Source table name, LoadState, update time, etc. A system procedure SYSADMIN.matViewStatus used to retrive the Mat View Status, the retrive status procedure like:

For example, assume SAMPLEMATVIEW is a Mat View and under a sample model named SampleModel, execute exec SYSADMIN.matViewStatus('SampleModel', 'SAMPLEMATVIEW'), the result will lools

Mat View Loading

Mat View Loading via system procedure SYSADMIN.loadMatView, the loading procedure like:

• Each time SYSADMIN.loadMatView be executed, a reference one row record will be insert into statusTable, which mainly initialize the Mat View Status:

insert into statusTable(Valid, LoadState, Updated, Cardinality, LoadNumber) values(false, 'LOADING', now(), -1, 1)

NOTE: The SQL set initial Mat View’s Valid -> ‘false’, LoadState -> ‘LOADING’, Updated -> now(), Cardinality -> -1, LoadNumber -> 1

• If SYSADMIN.loadMatView be invoked, and Mat View already have a reference status record, Check:

   Mat View's valid is 'false' 
OR (time interval > ttl) 
OR InputParameter invalidate is true 
OR Mat View's loadstate is 'NEEDS_LOADING' 
OR Mat View's loadstate is 'FAILED_LOAD'

If result is true, update statusTable,

update statusTable set loadNumber = loadNumber + 1, updated = now(), LoadState = 'LOADING'

NOTE: The SQL set Mat View’s loadNumber -> loadNumber + 1, updated -> now(), LoadState -> ‘LOADING’

• Three kinds of loading scripts: beforeLoadScript, loadScript, afterLoadScript, which execute in a sequence, If loadScript is null which load Script not defined, the loading process looks:

As depicted in above diagram, 5 native sql executed with a sequence:

truncate table Mat_table_staging
insert into Mat_table_staging select * from MatView option nocache MatView
alter table Mat_table rename to Mat_table_tmp
alter table Mat_table_staging rename to Mat_table
alter table Mat_table_tmp rename to Mat_table_staging

• After Loading, update the statusTable

update statusTable set updated = now(), LoadState = 'LOADED', valid = true, cardinality = rowsUpdated

NOTE: The SQL set Mat View’s Valid -> ‘true’, LoadState -> ‘LOADED’, Updated -> now(), Cardinality -> rowsUpdated

Mat View Updating

Mat View Updating via system procedure SYSADMIN.updateMatView, the updating procedure like:

Note that refreshCriteria is passed as procedure input parameter, statusTable update Criteria form by Mat View Scope, the update peocedures like execute a serial sql in a sequence:

delete from Mat_table where refreshCriteria
insert into Mat_table select * from MatView where refreshCriteria option nocache MatView 
select count(*) as rowsUpdated from Mat_table
update statusTable set updated = now(), LoadState = 'LOADED', valid = true, cardinality = rowsUpdated

NOTE: At the end of updateMatView, Mat View’s status be updated(Valid -> ‘true’, LoadState -> ‘LOADED’, Updated -> now(), Cardinality -> rowsUpdated).

MaterializationManager based Management

Refer to MaterializationManager FinishDeploymen MaterializedTable != null section.

MaterializationManager FinishDeployment

MaterializationManager finishedDeployment() method control some mainly logic of Teiid Materialization as below:

• Materialized View must be defined in VIRTUAL model, and MATERIALIZED option must be set to ‘TRUE’
• Set ALLOW_MATVIEW_MANAGEMENT Option to true is necessary if trigger Teiid ‘s aoto management.
• In Internal Materialization, any query against Mat View, or SYSADMIN.refreshMatView() will load MatView.
• In Internal Materialization, if define MATVIEW_TTL Mat View will relaod(SYSADMIN.refreshMatView()) by a TimerService
• In External Materialization, Timer Service schedule a JobScheduler which repeated load Mat View

Materialization Example

This Section Contain Examples for both Internal and External Materialization.

Example.1: External Materialization with Staging Table

CREATE VIEW SAMPLEMATVIEW (
  id varchar,
  a varchar,
  b varchar,
  c varchar,
  PRIMARY KEY (id, a, b)
) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'TRUE',         
             MATERIALIZED_TABLE 'Accounts.SampleTable_mat', 
             "teiid_rel:MATVIEW_TTL" 30000,
             "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute accounts.native(''truncate table SampleTable_staging'');',
             "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT"  'execute accounts.native(''ALTER TABLE SampleTable_mat RENAME TO SampleTable_mat_temp'');execute accounts.native(''ALTER TABLE SampleTable_staging RENAME TO SampleTable_mat'');execute accounts.native(''ALTER TABLE SampleTable_mat_temp RENAME TO SampleTable_staging'');', 
             "teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM Accounts.status WHERE Name=''SAMPLEMATVIEW'' AND schemaname = ''SampleModel''',
             "teiid_rel:MATERIALIZED_STAGE_TABLE" 'SampleTable_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.a, A.b, A.c FROM Accounts.SampleTable AS A;

• « Understanding Teiid Engine
• Java 执行 scheduled task »