Handling Updates/Deletes on tables without primary key. Debezium+Confluent JDBC Sink

Dominik Maciejewski

unread,

May 26, 2021, 11:51:27 AM5/26/21

to debezium

Hi guys :)
At the begging I want to thank You for Your excellent job creating Debezium ! I see really good use cases for this tool and I hope more people soon will use it in production. Now, I have small question to You.
I got table without any primary key on source (both in Postgre (pgoutput) and Oracle (logminer)) and I want to transfer changes to another database using Confluent JDBC Sink.
Unfortunately I got really big problems with updates and deletes. As You know Debezium in tables without PK send null as key for changes.
According to Confluent documentation https://docs.confluent.io/kafka-connect-jdbc/current/sink-connector/sink_config_options.html#sink-pk-config-options deletes requires pk.mode to be record_key.
I want to be able to transform updates and deletes from source DB to target. Is there any possibility to force Debezium to use all columns from source table as keys for message?
I know that I can lost some data due to uniqe indexes on target but I think this is only possibility to transform updates and deletes from source to target.
Thank You in advance for Your help and sorry for my poor English skills.

Gunnar Morling

unread,

May 26, 2021, 11:55:35 AM5/26/21

to debezium

Hey,

Thanks for the nice words!

You can define custom key columns by means of the message.key.columns property:

https://debezium.io/documentation/reference/connectors/postgresql.html#postgresql-property-message-key-columns

Ideally, you have key comprising one or only a few columns, but in the worst case you could set all columns of a table as the key. Note this requires per-table configuration, there's no generic "use all columns as key" catch all option.

--Gunnar

Dominik Maciejewski

unread,

May 26, 2021, 12:05:25 PM5/26/21

to debezium

Great ! Thank You for Your answer.

Dominik Maciejewski

unread,

May 26, 2021, 1:18:57 PM5/26/21

to debezium

Gunnar this message.key.columns worked thank You for this one.

But there is still issue when primary keys on target prevent from two same rows in source.

For example:

1) insert into table values (21,'Dominik',Dom')

Source Target

Table Table

__________________ ___________________

| 21| Dominik| Dom| | 21| Dominik| Dom|

__________________ ___________________

2) insert into table values (21,'Sebastian',Seb')

Source Target

Table Table

__________________ ___________________

| 21| Dominik| Dom| | 21| Dominik| Dom|

| 21| Sebastian| Seb| | 21| Sebastian| Seb|

__________________ ___________________

3) update table set Name = Dominik where AGE = 21

Source Target

Table Table

__________________ ___________________

| 21| Dominik| Dom| | 21| Dominik| Dom|

| 21| Dominik| Seb| | 21| Dominik| Seb|

__________________ ___________________

4) update table set Nick= Dom where AGE = 21

Source Target

Table Table

__________________ ___________________

| 21| Dominik| Dom| | 21| Dominik| Dom|

| 21| Dominik| Dom| X - no row error with unique key

__________________ ___________________

I know this question is more about Confluent JDBC Sink but maybe You have any idea how I can manages this ?

środa, 26 maja 2021 o 17:55:35 UTC+2 gunnar....@googlemail.com napisał(a):

Reply all

Reply to author

Forward