Red Hat Summit Red Hat Summit지원콘솔개발자평가판 시작연락처

이 콘텐츠는 선택한 언어로 제공되지 않습니다.

3.3. External Materialization

3.3.1. Use External Materialization
링크 복사

Procedure 3.2. Use External Materialization

  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 materialized table that has been created, and click on "Name In Source" property and change it from "MV1000001" to "mv_{your_table_name}". Typically this could be same name as your virtual table name, (for example, you might name it "mv_UpdateProduct".)
  10. Save your model.

    Note

    The data source this materialized view physical model represents will be the data source for storing the materialized tables. You can select different "physical models" for different materialized tables, creating multiple places to store your materialized tables.
  11. Once you are have finished creating all materialized tables, right click on each model (in most cases this will be a single physical model used for all the materialized views), then use Export - Teiid Designer - Data Definition Language (DDL) File to generate the DDL for the physical model.
  12. Select the type of the database and DDL file name and click Finish.
    A DDL file that contains all of the "create table" commands is generated..
  13. Use your favorite "client" tool for your database and create the database using the DDL file created.
  14. Go back to your VDB and configure the data source and translator for the "materialized" physical model to the database you just created.
  15. Once finished, deploy the VDB to Teiid Server and make sure that it is correctly configured and active.

Important

It is important to ensure that the key/index information is defined as this will be used by the materialization process to enhance the performance of the materialized table.

3.3.2. External Materialization 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 simplest approach is to load the data. The load can even be done through Teiid with this command: insert into target_table select * from matview option nocache matview
That however may be too simplistic because your index creation may perform better 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.

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. 
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)
);
Copy to Clipboard Toggle word wrap

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.
Expand
Table 3.2. Extension Properties
Property Description Optional Default
teiid_rel:ALLOW_MATVIEW_MANAGEMENT
Allow Teiid based management
False
False
teiid_rel:MATVIEW_STATUS_TABLE
fully qualified Status Table Name defined above
False
NA
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 is not defined in order to copy data from the teiid_rel:MATVIEW_STAGE_TABLE the 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
NA
teiid_rel:ON_VDB_START_SCRIPT
DML commands to run start of vdb
True
NA
teiid_rel:ON_VDB_DROP_SCRIPT
DML commands to run at VDB un-deploy; typically used for cleaning the cache/status tables
True
NA
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.
Upon the VDB deployment, teiid_rel:ON_VDB_START_SCRIPT will be run on completion of the deployment.
Based on the teiid_rel:MATVIEW_TTL defined a scheduler entry will be created to run SYSADMIN.loadMatView procedure, which loads the cache contents.
This procedure first inserts/updates an entry in teiid_rel:MATVIEW_STATUS_TABLE, which indicates that the cache is being loaded, then teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT will be run if defined. Next, 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 sets the teiid_rel:MATVIEW_STATUS_TABLE entry to "LOADED" and valid.
Based on the teiid_rel:MATVIEW_TTL, the SYSADMIN.matViewStatus is run 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.
Run the SYSADMIN.updateMatView procedure at any time 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.
Here is a sample dynamic VDB: 
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="sakila" version="1">
    <description>Shows how to call JPA entities</description>
 
    <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'');execute pg.native(''ALTER TABLE mat_actor_staging RENAME TO mat_actor'');execute pg.native(''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:MATVIEW_TTL" 300000,
                "teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM pg.public.status WHERE Name=''actor'' AND schemaname = ''sakila''')
            AS 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>
Copy to Clipboard Toggle word wrap
With the Dynamic VDB and above table extension properties, you can set up an external materialization for an embedded server.
MATERIALIZED_TABLE is necessary, it can be any name, but its structure should match to materialization view. MATVIEW_STATUS_TABLE is necessary, it should be named as 'status': 
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)
);
Copy to Clipboard Toggle word wrap

Note

MariaDB uses a Silent Column Changes function, meaning that the , 'long' type will silently change to 'MEDIUMTEXT'. Therefore, if you execute the schema above in MariaDB, the 'Cardinality' and 'LoadNumber' columns will change to 'bigint'.
If 'teiid_rel:MATVIEW_LOAD_SCRIPT ' MATERIALIZED_STAGE_TABLE is necessary, it can have any name, but its structure should match the materialization view.
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'.
The TransactionManager is necessary except when you add the ConnectionFactory and Translator to the Embedded Server. Here is the configuration code for it: 
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");
Copy to Clipboard Toggle word wrap
The following steps show a sample Dynamic VDB with Materialization view in Embedded Server.
Run this 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');
Copy to Clipboard Toggle word wrap
Create the Dynamic VDB with DDL content: 
        <metadata type="DDL">
        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>
Copy to Clipboard Toggle word wrap
To deploy the Dynamic VDB to the Embedded Server, run this 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");
Copy to Clipboard Toggle word wrap
The test result is the MatView can catch the updated of underlying source table 'PRODUCT'.

3.3.3. External Materialization Options
링크 복사

You must undertake every option in external materialization mode manually.
If you are trying to load the materialized table "Portfolio.UpdateProduct", for which the materialization table is defined as "mv_view.UpdateProduct", use any JDBC Query tool like SquirreL and make a JDBC connection to the VDB you created and issue following SQL command: INSERT INTO mv_view.mv_UpdateProduct SELECT * FROM Portfolio.UpdateProduct OPTION NOCACHE
Here is how you would create an AdminShell script to automatically load the materialized table: 
sql=connect(${url}, ${user}, ${password});  
sql.execute("DELETE FROM mv_view.mv_UpdateProduct");  
sql.execute("INSERT INTO mv_view.mv_UpdateProduct SELECT * FROM Portfolio.UpdateProduct OPTION NOCACHE");  
sql.close();
Copy to Clipboard Toggle word wrap
Use this command to execute the script: adminshell.sh . load.groovy

Note

  • If you want to set up a job to run this script frequently at regular intervals, then on Red Hat Enterprise Linux use "cron tab" or on Microsoft Windows use "Windows Scheduler" to refresh the rows in the materialized table. Every time the script runs it will refresh the contents.
  • This job needs to be run only when user access is restricted.

Important

There are some situation in which this process of loading the cache will not work. Here are some situations in which it will not work:
  • If it is updating all the rows in the materialized table, and you only need to update only few rows to avoid long refresh time.
  • If it takes an hour for your reload your materialized table, queries executed during that time will fail to povide correct results.
  • Also ensure that you create indexes on your materialization table after the data is loaded, as having indexes during the load process slows down the loading of data, especially when you are dealing with a large number of rows.

3.3.4. Set Up a Staging Table
링크 복사

Procedure 3.3. 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
    Copy to Clipboard Toggle word wrap
    Add the script to your cron tab or Windows Scheduler and set it to run at a regular interval to keep your data fresh.
맨 위로 이동
Red Hat logoGithubredditYoutubeTwitter

자세한 정보

평가판, 구매 및 판매

커뮤니티

Red Hat 문서 정보

Red Hat을 사용하는 고객은 신뢰할 수 있는 콘텐츠가 포함된 제품과 서비스를 통해 혁신하고 목표를 달성할 수 있습니다. 최신 업데이트를 확인하세요.

보다 포괄적 수용을 위한 오픈 소스 용어 교체

Red Hat은 코드, 문서, 웹 속성에서 문제가 있는 언어를 교체하기 위해 최선을 다하고 있습니다. 자세한 내용은 다음을 참조하세요.Red Hat 블로그.

Red Hat 소개

Red Hat은 기업이 핵심 데이터 센터에서 네트워크 에지에 이르기까지 플랫폼과 환경 전반에서 더 쉽게 작업할 수 있도록 강화된 솔루션을 제공합니다.

Theme

© 2025 Red Hat