Accessing internal materialized views in Teiid

[fhajaj]

Hello,

We're using internal materialized tables in our application. Can we persist those internal tables to disk similar to persisting cached result set?

Does Teiid provide access to the internal tables: can we insert/update/delete data into it? I saw that internal view can be set as updatable but this is limited to updating a single row, correct ?

Thank you.

[jstastny]

Hi Fadi,

have you considered using External materialization? External Materialization - Teiid 8.9 - Project Documentation Editor This would solve your need to persist internal tables.

You can replace your internal views or it may be feasible to create another layer of materialization like this:

CREATE VIEW external_view_table (<COLUMNS>)  OPTIONS (<OPTIONS>) AS SELECT * FROM current_internal_view;

I checked that it works, but I don't know whether this has some pitfalls or not.

Also External materialization gives you better control over the data loading. Thus you can write the definition query in a way that rows inserted directly to the materialized table stay there even after reload of the view (moreover as it is real db table, you can do anything with it, create trigger, etc...).

Jan

[shawkins]

> Can we persist those internal tables to disk similar to persisting cached result set?

They spill to disk just like result set caching through the buffer manager.  So even up to hundreds of gigabytes in size should be fine.

> Does Teiid provide access to the internal tables: can we insert/update/delete data into it? I saw that internal view can be set as updatable but this is limited to updating a single row, correct ?

No we don't currently provide access directly to the internal table.  And yes there is an update procedure for a single row or in 8.9 for a batch of rows.

Jan's response is also good.  If you need more control and want the materialization to be non-transient, then external materialization is the way to go.