How to configure Kafka JDBC Source Connector with stored procedure?

3
votes

I am very new to Kafka, I'm doing a PoC (my first Kafka app) and was curious to know if anyone has come across or worked on a custom JDBC Source Connector that uses a stored procedure to export data from Oracle into Kafka?

I know this could be considered an open-ended question by the SO community but thanks for your patience and I'd appreciate any feedback.

Thank you!

Some background:

  • I already have stored procedures in Oracle which are used by an existing Spring app to extract and load data in a new DB (for a new version of an existing enterprise application).

  • The table structure in the new database is different than that of the old.

  • The Spring app (which uses Spring JDBC and works with Stored Procedures, Row Mappers etc.) was developed as a PoC to check connectivity, integration and to load data in the new DB.

  • Now that it works, we're trying to introduce Kafka that would actually store the data and then load it into the new DB. The JDBC Sink Connector would be developed later.

  • I am looking for an example (using Java, Maven, Spring) that would help me in getting started towards building a custom connector. Most of the example/docs show curl examples which doesn't fit my existing app. I maybe missing something here.

1
jdbcapache-kafkaapache-kafka-connect
Please provide a simplified form of the stored procedure. Are there OUT parameters or a cursor is returned to get the data? And above all, clarify who should call the procedure? Do you expect that Kafka will poll the procedure? – Marmite Bomber
Ideally, I would prefer the existing app to execute the stored procedures and transform the result sets according to RowMappers. However, I'm not aware how its performance would compare to Kafka's poll feature. Following is the stored procedure OUT parameter example: declareParameter(new SqlOutParameter("param_ref_cursor", OracleTypes.CURSOR, new CustomRowCallbackHandler(<params>))) – RGB314
@RGB314 Did you get a way to call SP from connector? Please answer so that it will be helpful for the community. – Mahesh Bongani
I did not proceed with this approach as it was made redundant due to change in the application architecture and the way data was to be streamed. – RGB314

1 Answers

1
votes

There is way that we can call SP though source connector property - query: call sp here by creating temp table and insert data