Materialized views in Teiid EmbeddedServer

[jchidiac]

Hi,

Ii'm using Teiid EmbeddedServer, and i want to customize it to support the  Materialized views

Can anyone give me an example how to do it ?

Thank u.

[rareddy]

Customize how?

Basically it is no different from server version, provide OPTION on the view with property "MATERIALIZED" then it will use internal materialized, then if you want to external materialized then also provide the 'MATERIALIZED_TABLE' property. See DDL Metadata - Teiid 8.9 (draft) for definition of the view. If you used 'MATERIALIZED_TABLE' then you also need to provide the that view details to go along with the VDB.

Ramesh..

[jchidiac]

I tried to cstomize the materialize with Teiid Embedded

but when i execute the query on the testView, i didnt get any results

any idea ?

PS : the Test class is attached.

[rareddy]

Joseph,

Did you want to use external materialization? I see your code is trying to use it. To make sure materialization works in your case, have "MATERIALIZED" property to begin with. Once that works, then you can pursue the external materialization.  For external materialization you need to make sure these are correct

				" MATERIALIZED_TABLE '" + teiidModel + ".teiidtest.testView',  " + //
				"\"teiid_rel:MATVIEW_STATUS_TABLE\" '" + teiidModel + ".teiidtest.status'  , " + //
				"\"teiid_rel:MATERIALIZED_STAGE_TABLE\" '" + teiidModel + ".teiidtest.myViewTable_staging'," + //
				"\"teiid_rel:ALLOW_MATVIEW_MANAGEMENT\" 'true' ,  " + //
				"\"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 myViewTable RENAME TO myViewTable_temp;ALTER TABLE myViewTable_staging RENAME TO myViewTable;ALTER TABLE myViewTable_temp RENAME TO myViewTable_staging;'')', " + //

also when you run the program, you can check "status" table contents in the mysql database to see if materialization load happened or not, if there is error there should be in the log. I have not tested external materialization with embedded, but should work.

[shawkins]

> I have not tested external materialization with embedded, but should work.

It will work in so much as the queries will be redirected to the materialization target, but the management piece will not work as the logic to add the MaterializationManager only exists for the server right now.

[rareddy]

Looks like we can extend MaterializationManager to EmbeddedServer with little work. Joseph, can log an enhancement JIRA for this? It will be helpful add a test case along with it.

Thanks

Ramesh..

[jchidiac]

TEIID-3149