[teiid-issues] [JBoss JIRA] (TEIID-4526) Integrate with Debezium for CDC for maintaining materialized views

[Emmanuel Bernard (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4526?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13327905#comment-13327905 ] 

Emmanuel Bernard commented on TEIID-4526:
-----------------------------------------

I know you're starting on a different direction but here is an idea [~rhauch] and I discussed on the subject.

This proposal is ignoring aggregation for the moment.

The idea is to get a list of id that changed from Debezium and execute the query on this subset of ids.
This avoids the need to deeply understand state changes and run or approximate them on the materialized view.
It also has some performance feedback loop.

Here is the proposal proper.
- Materialized views keep a reference to the primary keys (joined columns) of the original table rows composing it
- upon Debezium change events, capture the table and id (and joined column values) at bay
- place the rows involving the table and id (and joined column values) into an update queue
- take the queue and run the original materialized view query restricted to the relevant ids or joined column value
- upgrade the materialized view with the new information for the touched rows
- if the update queue has filled up since then, repeat and rince

I understand that we might only be able to run a subset of the materialized views this way but they should be identifiable and we could use the fallback approach mentioned above for the others.
The benefits of this method are:
- it has a natural back pressure mechanism to batch refreshes
- it does not try to be _too_ smart and change the materialized view just from the individuals change events but rather collect the dirty rows
- the query is "incremental" in the sense that only the impacted rows are recomputed.

> Integrate with Debezium for CDC for maintaining materialized views
> ------------------------------------------------------------------
>
>                 Key: TEIID-4526
>                 URL: https://issues.jboss.org/browse/TEIID-4526
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Server
>    Affects Versions: 9.2
>            Reporter: Van Halbert
>            Assignee: Steven Hawkins
>            Priority: Critical
>
> Integrate with Debezium so that Teiid will be able to consume and react to the row-level change events and do something interesting with them, such as update the materialized view(s).



--
This message was sent by Atlassian JIRA
(v7.2.3#72005)