Materialized view support in Debezium

[Daniel Petisme]

Hi team, hope you are well.

I was looking for pieces of evidence clarifying if Debezium was able to track Materialized Views changes or not.

it makes sense to not support "classic" views since they are a logical "on the fly" representation of underlying tables but when it comes to materialized view, the logical representation gets persisted on disk.

So I assume it triggers similar table mechanisms to track the data changes.

Can Debezium track a materialized view change? Are they any limitations regarding databases?

Maybe I missed something in the documentation, so please feel free to simply point me in the right direction.

Thank you for your help

PS: Guys you rock Debezium is awesome :) 

Daniel PETISME
daniel....@gmail.com

[Chris Cranford]

Hi Daneil -

The support for materialized views varies across the connectors because they're often handled differently depending on the source database.  This discussion came up recently regarding the Oracle connector and we did confirm that materialized views are in fact supported but for other connectors their support may be more limited or not at all.  Which connector are you inquiring about?

Thanks,
Chris

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CACHzTrakJGpFqoRxj42j104WO6fpeQFYMpraL8SNzaHxAgXX1g%40mail.gmail.com.

[Daniel Petisme]

Hi Chris, thanks for your answer.

I'm looking for the MS SQL Server one.

Daniel PETISME
daniel....@gmail.com

[Chris Cranford]

Hi Daniel,

So I know MSSQL specifically requires the base object to be a table in order to create a capture instance.  What I cannot get clarification on from the docs is whether or a materialized view falls into the scope of a table by MSSQL standards.  If you can create a capture instance for a materialized view in MSSQL then you should be able to capture changes with the connector.  If MSSQL prevents creating a capture instance for the materialized view, then unfortunately those types are not supported.

HTH,
Chris

[Niels Berglund]

Hi Chris, Daniel

A materialized view, (AFAIK), does not fall under the scope of a table in SQL Server.

To prove/disprove this I just created a materialized (indexed) view in MS SQL, and when I tried to enable the view for CDC (after having enabled the db) I got the error:

"Source table 'Sales.vOrders' does not exist in the current database. Ensure that the correct database context is set. Specify a valid schema and table name for the database."

So it looks like it is not possible.

Niels

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/c0012109-c0fc-ccb1-388b-ee7b439ea55d%40gmail.com.

[Chris Cranford]

Hi Niels, thanks for confirming.  That was my suspicion as well.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CAL0uK6CWt2xO2a0KjQoXUun0kzGEGHQJOTakYdpEsCF%2BLTBmbw%40mail.gmail.com.

[Daniel Petisme]

Hi team,

Thanks a lot for confirming!

Worth to be on the official documentation?

Do you want me to create an issue it in a GitHub repo? I can create the PR if needed.

Anycase, thank you again for your reactive answer

[Chris Cranford]

Hi Daniel,

Yes please feel free to open a Jira issue and submit a documentation update that clarifies this point.  Anything that avoids confusion and provides clarity is always welcomed.

Chris