Goal: Sync data between MySQL and Redshift via redshift sink connector and debezium cdc source connector
Exploring Redshift Sink Connector
We're able to see inserts and deletes queries in mysql being achieved in redshift table, here's the configs for both -
rs sink properties
pk.mode=record_key
insert.mode=insert
auto.create=true
delete.enabled=true
tasks.max=1
schema.context=default
dbz properties
tombstones.on.delete=true
after.state.only=true
topic.cleanup.policy=delete
transforms=unwrap
transforms.unwrap.type=io.debezium.transforms.ExtractNewRecordState
transforms.unwrap.drop.tombstones=false`
But, given insert.mode does not support upsert, UPDATE queries are seen as new rows in redshift. Which is undesirable.
Confluent support mentioned
We have a feature request opened for allowing deletes by the connector but currently there is no ETA for the same(FF-7002). Upserts will also not work, and there is another feature request for the same(FF-10649)
Can delete + insert be implemented?
Is there a transformation logic that can be configured s.t for any UPDATE query, cdc connector sends two events - 1 tombstone (for the key, which gets deleted in rs) + 1 insert (after state)?
I understand this was asked before, however want to first try a single connector, if possible