We have a use case wherein the business logic requires us to join tables from different databases and push the end result to an input topic. table1 from schema1 in database1 table2 from schema2 in database2 Business logic SELECT a,b FROM table1 INNER JOIN table2 ON table1.c = table2.d; here a is from table1 and b is from table2 , and value of the message in the input topic looks like { "payload":{ "a":xyz,"b":xyz} } Is there any way to achieve this requirement with a single jdbc source connector? PS: I have referred to Can the JDBC Kafka Connector pull data from multiple databases? , but in the accepted answer messages are pushed to input topic without implementing any business logic. With this implementation we won't be able to push the message to input topic as per our requirement. Alternative way would be using kafka streams, i.e., push the messages to input topic from each table and handle the joining logic at the kafka stream application level. But we are looking for a solution if we could implement the logic at the connector level itself?

Reputation: 173

Can we use kafka JDBC source connector to pull data from multiple databases and put it into one input topic?

We have a use case wherein the business logic requires us to join tables from different databases and push the end result to an input topic.

table1 from schema1 in database1

table2 from schema2 in database2

Business logic

SELECT a,b FROM table1 INNER JOIN table2 ON table1.c = table2.d;

here a is from table1 and b is from table2, and value of the message in the input topic looks like { "payload":{ "a":xyz,"b":xyz} }

Is there any way to achieve this requirement with a single jdbc source connector?

PS:

I have referred to Can the JDBC Kafka Connector pull data from multiple databases?, but in the accepted answer messages are pushed to input topic without implementing any business logic. With this implementation we won't be able to push the message to input topic as per our requirement.
Alternative way would be using kafka streams, i.e., push the messages to input topic from each table and handle the joining logic at the kafka stream application level. But we are looking for a solution if we could implement the logic at the connector level itself?

Upvotes: 0

Reputation: 17

If both the databases are on the same Database Server you can use the CustomQuery and write the SQL joining the tables across database.

Upvotes: 0

Reputation: 32140

Short answer: No, you cannot use the JDBC Source connector in this way.

Longer answer: The JDBC source connector can connect to one database per connector instance. You have a few options:

Stream the contents of both tables into Kafka, and use ksqlDB (or Kafka Streams if you prefer) to join them and push the resulting data to a new Kafka topic.
Write a new connector plugin yourself that connects to both databases and does the join (this sounds like an awful idea)
If the database supports it, use a remote join (e.g. Oracle's DB Link) and the JDBC source connector's query option.

Depending on data volumes and query complexity I'd personally go for option 1. ksqlDB is a perfect fit here.

Upvotes: 4