Inserting data in multiple tables from single topic using JDBC Sink Connector

Kafka Connect
1

Hello,
We have a topic with data in AVRO schema and need to insert this data into multiple tables (3-4 tables) in Oracle database using JDBC Sink connector. Any pointers would be helpful.
Thanks

2

I’m assuming that you want to route each event to one topic based on the content in each record. Is that the case?

If this is the goal, on its own, the JDBC Sink Connector doesn’t support content based routing like this. You might look into an SMT to dynamically change the ConnectRecord’s topic, either writing your own or looking around to see if there is an open source one available. E.g., I see that Debezium has a content based routing SMT. It seems like this could work. With an SMT that changes the record’s topic, it looks like the connector would batch records up per destination table and do the right thing (based on the source here).

If you don’t make progress with a content based routing SMT, you might consider multiple connector instances, one per destination table. Each connector config could leverage the io.confluent.connect.transforms.Filter SMT to pick out only that table’s events based on a record’s payload. This wouldn’t be as efficient as the earlier suggestions but should get you there using on-the-shelf tools.

3

Hello Dave,
Thank you for the reply.
So the case is that we have a topic which contains consolidated data as part of each event and we want to make this data available in database for reporting. So when we save this data in database, we need to split the data from every event in the topic and save it into multiple database tables.
e.g. If we have below msg in topic

{
field1,
field2,
field3,
field4,
field5,
field6
}

then field1,field2 goes to Table1
field2, field3, field4 goes to Table 2
field2, filed5, field6 goes to Table3 etc.
There can be foreign key relations between the tables.
I am trying to figure out what could be the best way to do this.

4

I could not find any information on splitting a Kafka message in one topic and transform and save it in multiple database tables using sink connector. Is it even possible with using Jdbc Sink Connector?
If not then I will have to split my message in multiple topics and then map the topic to a single database table. Only problem would be to maintain the referential integrity between tables as I have some foreign keys in the tables.
Any thoughts or input?

Thanks,
Hrishi

6

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