How to delete records in the database through Kafka JDBC connector?

Kafka Connect
1

Hi All
How to delete records in the database through Kafka JDBC connector between 2 MSSQL servers?
Question 1:
JDBC Source connector can be added and be updated from source server to target server, why except deleted,?
Question 2:
Why can’t see any messages from “kafka-console-consumer.sh”
Question 3:
When I add " delete.enabled=true" on the JDBC Sink connector, it isn’t working for me.

what can I do, please help me, thanks

2

Hi @williehao .

I haven’t quite understood this question. Can you rephrase?
Do you mean “How to delete records in the origin database once copied over to the Kafka cluster?” or “Why would one want to?”

3

I wrote an article, and recorded a video, on this very subject :slight_smile:

You can see it here:

:writing_hand: Kafka Connect JDBC Sink deep-dive: Working with Primary Keys
:movie_camera: Kafka Connect JDBC sink deep-dive: Working with Primary Keys - YouTube

2 Likes
4

Sorry guys, I am very new to this forum so I have to merge 5 pictures into one picture.

  1. Source connector
  2. Sink connector
  3. Before deletion data (1. source table and 2. target table )
  4. After using T-SQL to insert , update and delete (1. source table and 2. target table )
  5. kafka-console-consumer

image
image903×2618 239 KB

Question:

  1. Why update and insert data are okay without delete
  2. Cannot see any deletion records or info on the “kafka-console-consumer.sh”

PS 1: Please download my question picture, if you can’t see it very well.

PS 2: I’ve changed the DB server from MSSQL to MYSQL that has the same problems too.

Please help me to fix my problems, thanks again
Willie

5

Hi @vnadkarni
I have edited the content, please help me to fix my issue, if I didn’t explain my question clearly please let me know, thanks, again.

6

Hi @rmoff
I am so sorry, I can’t solve my issue, even though I’ve been seeing your video on youtube many times, please help me to fix my questions and, I have added more steps information into this topic.
thanks, again
Willie

7

The short answer to the question is that the JDBC source connector does a SELECT query. If you run a DELETE between two select queries, how would you expect any client to know that there used to be data there that it hasn’t seen yet? It can’t.

You would need to collect the database logs with change data capture. This is what the Debezium source connectors allow you to do, which I believe Robin mentioned in his posts linked.

To issue deletes in the sink connector, you’d need to write a null value for a given primary key. From Debezium, you’d have to map “op: d” records into null values, which you would be able to see in the console consumer, too

2 Likes
8

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.