- Materialized Views
- Internal Materialization
- External Materialization
- MaterializationManager FinishDeployment
- Materialization Example
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.
An Internal Materialization have to meet the following 3 conditions:
- the view should be VIRTUAL, which means the View should be in a
materializedhave to be set and value should be true
teiid_rel:ALLOW_MATVIEW_MANAGEMENTshould be added and value should be true
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_TTLis set, a Scheduled ExecutorService will be triggered, which execute SYSADMIN.refreshMatView() repeatedly with a specified TTL interval
teiid_rel:MATVIEW_TTLis not set or less than/equals 0, the Materialized View be load only one time
- If MATERIALIZED_TABLE is not configured, the View is Internal
- If teiid_rel:MATVIEW_TTL is not configured, or value <= 0, sql
select count(*) from MatViewpass to executeAsynchQuery(), and execute once.
- If teiid_rel:MATVIEW_TTL is configured and value > 0, sql
call SYSADMIN.refreshMatView('MatView', true)pass to executeAsynchQuery(), and execute repeatedly
NOTE: 2 kinds of probable SQL will be passed to this method,
select count(*) from MatViewor
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
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>
There are 4 aspects logic in Teiid External Materialization:
- Mat View Status
- Mat View Loading
- Mat View Updating
- 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
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 finishedDeployment() method control some mainly logic of Teiid Materialization as below:
- Materialized View must be defined in
VIRTUALmodel, and MATERIALIZED option must be set to ‘TRUE’
trueis 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_TTLMat View will relaod(SYSADMIN.refreshMatView()) by a TimerService
- In External Materialization, Timer Service schedule a
JobSchedulerwhich repeated load Mat View
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;