-
1. Re: Cache a view without materialization ...
rareddy May 14, 2015 11:30 AM (in response to ki.alam)Every feature is available in Dynamic VDB, except support for the XML Document model. So, yes materialization is supported. Look at providing the OPTIONS properties for view to define the Materialization.
See Internal Materialization - Teiid 8.11 (draft) - Project Documentation Editor
External Materialization - Teiid 8.11 (draft) - Project Documentation Editor
and for all available properties
DDL Metadata - Teiid 8.11 (draft) - Project Documentation Editor
Ramesh..
-
2. Re: Cache a view without materialization ...
markaddleman May 14, 2015 12:28 PM (in response to rareddy)Related to this, is there a convenient mechanism to mediate the engine's processing of views with a translator? I would like to insert a TeiidExecutionFactory into the view processing pipeline to enable a few use cases:
- result set caching
- manipulating the query before executing
-
3. Re: Cache a view without materialization ...
shawkins May 14, 2015 2:32 PM (in response to markaddleman)Mark, the approach would be to have a translator expose tables, rather than views and then handle the custom logic there. However if anything other than simple table scans are supported, then that will complicate the handling.
-
4. Re: Cache a view without materialization ...
markaddleman May 16, 2015 8:12 AM (in response to shawkins)Thanks
-
5. Re: Cache a view without materialization ...
markaddleman May 17, 2015 6:31 AM (in response to shawkins)We're using Teiid to "up-lift" some legacy data sources into a relational model for OLAP-style analysis. The sources are running a high-volume transactional system so we don't want to burden them for every pivot of the data thus some kind of materialization is helpful. Teiid's current external materialization doesn't fit the bill because the client only needs a small fraction of the sources' data but we cannot know what the client needs are a priori. Essentially, this is an incremental, on-demand ETL job.
I have tried a couple of different approaches but I haven't found anything satisfying. As you point out above, the failure is the inability to give the planner a complete view of what's going on. For efficiency reasons, the source translator supports dependent joins and has an access pattern defined. I haven't found any solution that doesn't end up breaking the access pattern. I thought I would be very clever by following a pattern like this:
create view performant_source_view (
...
constraint access_pattern accesspattern (...)
) as
select t.c1, dj.c2 from t.a, /*+ makedep */ dj where t.z=dj.z;
create view incremental_etl (
...
)
as
select fake_out_planner.c1, fake_out_planner.c2
from performant_source_view, table(call insert_into_cache(a.c1, b.c2)) fake_out_planner
where (c1) not in (select (c1) from physical_cache_table
union
select c1, c2 from physical_cache_table;
The idea behind insert_into_cache stored proc is to stuff each row into the physical_cache_table then echo each row that passed into it. The problem is that the plan changes and the access pattern for performance_source_view cannot be satisfied.
I'm not sure if this is a hopeless approach or I'm missing something fundamental.
-
6. Re: Cache a view without materialization ...
shawkins May 18, 2015 11:08 AM (in response to markaddleman)I'm not sure if this is a hopeless approach or I'm missing something fundamental.
I don't think your missing something. Incremental caching functionality like this is currently only supported by caching procedures - which depending upon how many possible results can be obtained and how large they are could be viable.
Otherwise you'd probably want to log a feature request.
-
7. Re: Cache a view without materialization ...
markaddleman May 28, 2015 9:31 AM (in response to shawkins)> Otherwise you'd probably want to log a feature request - See more at: https://developer.jboss.org/thread/258329#sthash.h3Zc70aS.dpuf
I have a number of feature requests that have fallen out of my latest project. I will bring each one up on a separate thread to help avoid confusion.
-
8. Re: Cache a view without materialization ...
markaddleman May 28, 2015 10:23 AM (in response to shawkins)I meant to also add that I'm starting these feature requests as forum discussion items. I'll turn each one into proper a jira ticket once the discussion settles out.
-
9. Re: Cache a view without materialization ...
shawkins May 28, 2015 2:56 PM (in response to markaddleman)Thanks Mark, it looks like a good set of issues to discuss.