Teiid: cannot create materialized view for table

[tomcat77]

Hi. everybody!

I'm using Teiid 8.8.1 and trying to enable caching for DB table via Teiid materialized view feature. I've got table t_currency (list of available currencies) in PostgreSQL DB and I want to put in in Teiid cache. I've tried 2 ways to do this:

1. Create new table t_currency_mat with materialzed=true, materializedTable=<null> and relational:NativeQuery=SELECT * FROM t_currency

2. Set materialized=true for original t_currency table (cannot set it in Teiid Designer GUI, but manually editing appropriate .xmi file does the trick )

In both ways I got the same error when trying to refresh materialized view via query:

CALL SYSADMIN.refreshMatView(viewname=>'docoper_postgresql.t_currency_mat', invalidate=>true)

Error:

TEIID30359 org.teiid.api.exception.query.QueryMetadataException: TEIID30359 QueryPlan could not be found for physical group

If I run any query against these table, it executes successfully, but no cache statistics in Admin Console is updated.

Any help would be greatly appreciated.

[rareddy]

Hi,

1. Create new table t_currency_mat with materialzed=true, materializedTable=<null> and relational:NativeQuery=SELECT * FROM t_currency

Did you do this in the Designer? why are you setting the NativeQuery? A materialization table (_mat) table is just another "source" table you can map to, that Teiid at runtime can read from instead of producing result from transformation provided. If this is your backing materialized table, did you also define the view table that this table represents?

2. Set materialized=true for original t_currency table (cannot set it in Teiid Designer GUI, but manually editing appropriate .xmi file does the trick )

This is right approach, however for this view you need to provide the transformation SQL, as to how view contents can be formed if the materialized table is not populated or not exists. Editing XMI files directly never is good idea, nor never need to. That smells like a (bug) regression. Which version of Designer? can you provide details of your environment and re-produce steps?

Here is what I suggest..

1) You have a table called "t_currency" in "postgres" source model.

2) On a view model, create a view called "currecyview" with transformation "select * from postgres.t_currency". Set materialized = true on this view.

3) Create VDB and deploy and execute queries. That will utilize internal memory based caching for materialization.

If you really want to expose the "currencyview" to external table, then you need to define "materializedTable" value in Designer, which needs to point to a source table like your "t_currency" but schema must match "currencyview" schema. Here is old article but still relevant A "How To" Guide For Materialization (caching) Views In Teiid

Also, if you really want little deeper view into external materialization management, then see External Materialization - Teiid 8.10 (draft) - Project Documentation Editor

Let us know if that helped or if any other questions you may have on the subject.

Thanks

Ramesh..

[tomcat77]

Hi, Ramesh!

It looks like I shoot my own leg All these things I did in Teiid Desiner in SOURCE model, because I was not aware of difference between Source model and View model. I read carefully Teiid manual (Home - Teiid 8.10 (draft) - Project Documentation Editor) and didn't found there any description of VDB's metadata. I've googled and discovered metadata description in Teiid Designer User Guide

and then performed steps like you suggest. Everything worked, cache is enabled.

Maybe, it's worth to include chapter about metadata concepts in Teiid documentation? I was suprised to find such important (I'd say, conceptual) information in GUI tool manual, which is IMHO is not necessary to use.

Anyway, thanks a lot for your answer.

[rareddy]

Have you seen this The Basics · Teiid ? may be we should include this link somewhere in front of the documentation.

[tomcat77]

Shame for me, but no It's included in "Overview" section, and I browsed "Docs" section only.

[rareddy]

I will see if I can put a link some where in the front of the docs, Thanks