Teiid 9.1.2 - Create a Materialized View

Here are the necessary steps to create an External Materialization (in MySQL) with Teiid.

Creating Materialized View (external) on VDB

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<vdb name="MyVDB" version="1">

<description>My VDB</description>

<model name="MyTableMaterialized" type="PHYSICAL">

<property name="importer.useFullSchemaName" value="false"/>

<property name="query-timeout" value="300000"/>

<source name="MyTableMaterializedView" translator-name="mysql-override" connection-jndi-name="java:/dataSourceName" />

</model>

<model name="MyTable" type="VIRTUAL">

<metadata type="DDL"><![CDATA[

CREATE VIEW my_table (

cns varchar(400),

country_code varchar(400)

)

OPTIONS(

MATERIALIZED 'TRUE',

UPDATABLE 'TRUE',

MATERIALIZED_TABLE 'MyTableMaterialized.my_table_cache',

"teiid_rel:MATVIEW_TTL" 300000,

"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',

"teiid_rel:MATVIEW_LOADNUMBER_COLUMN" 'LoadNumber',

"teiid_rel:MATVIEW_STATUS_TABLE" 'MyTableMaterialized.status',

"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute MyTableMaterialized.native(''truncate table schema.my_table_cache_staging;'');',

"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute MyTableMaterialized.native(''RENAME TABLE schema.my_table_cache TO schema.my_table_cache_temp, schema.my_table_cache_staging TO schema.my_table_cache, schema.my_table_cache_temp TO schema.my_table_cache_staging'');',

"teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM MyTableMaterialized.status WHERE Name=''my_table'' AND schemaname = ''MyTable''',

"teiid_rel:MATVIEW_SHARE_SCOPE" 'NONE',

"teiid_rel:MATERIALIZED_STAGE_TABLE" 'MyTableMaterialized.my_table_cache_staging',

"teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION'

)

AS

SELECT A.cns,

A.country_code

FROM (EXEC MyTableCsvData.getTextFiles('MyTable.csv')) AS f,

TEXTTABLE(f.file COLUMNS cns string,

country_code string) AS A;

]]>

</metadata>

</model>

<translator name="mysql-override" type="mysql5">

<property name="SupportsNativeQueries" value="true"/>

</translator>

</vdb>

Note: It is fundamental that mysql-override be present on VDB, or the solution will not work.

Create tables on MySQL

CREATE TABLE my_table_cache_staging (
    cns VARCHAR(400),
    country_code VARCHAR(400)
);

CREATE TABLE my_table_cache (
    cns VARCHAR(400),
    country_code VARCHAR(400)
);

Configuration on WildFly

Here are the steps necessary to create a database datasource in Teiid 9.1.2 (installed on WildFly 10):

Run the following in the server console (in /bin):

./jboss-cli.sh --connect

and run the following commands:

/subsystem=datasources/data-source=adapterName:add(jndi-name=java:/dataSourceName, enabled=true, use-java-context=true, driver-name="mysql", connection-url="jdbc:mysql://mysql_ip:3306/database;useCursorFetch=true",user-name=user_to_use, password=user_to_use_password)

/subsystem=datasources/data-source=adapterName:enable

:reload

Where:

• adapterName - Put the name of the adapter that you want, and that will appear in WildFly console (under resource-adapters);
• dataSourceName - The name of the datasource that will be publicly used;
• database - Database (schema) to connect to

PS: It is assumed that mysql driver is already installed in server (if not see how: http://javari.blogspot.pt/2017/01/add-mysql-driver-to-wildfly-10.html)