How to read Kafka message key in Stored Procedure

I am workin on a CDC solution wherein I am receiving CDC events from my source database into Kafka. I have created pipeline to consume CDC events from Kafka and was able to insert/update records in MemSQL however for delete events, Kafka message is null so I think I have to use Kafka message key to delete the record from MemSQL table.

Is there an example on how can I read message key assuming I need to implement this CDC through Stored Procedure as pipeline into table option doesn’t seem to have an option to indicate on null value delete record based on message key.

What CDC tool are you using to push messages into Kafka?

IBM infosphere CDC Engine

We currently don’t expose the Kafka key in pipelines logic, though we do in fact plan to support this.

The best workaround for now is to copy the key into the message for deletes. I’m not personally familiar with InfoSphere, but it does look like it’s at least feasible to customize the message format with a “custom operation processor”:

Yes this seems to be a feature request. It really matters where we have transit costs from third party brokers where we have to additionally embed the key info into the message which causes a large increase in the size of the message.

Hi Sasha,
Although, it is possible to code a KCOP and get message key populated within the message. This isn’t feasible.

  1. This will impact existing subscribers to Kafka topics
  2. I agree with Mike’s comment, embedding message key into the message causes large increase in the size of the message

I think this should be a new feature request for MemSQL pipelines to support Kafka null messages. If this can be supported with load table option that would be even better since I do not need to develop stored procedure for majority of use cases. An option in the pipeline to indicate delete records from table when message is null using message key.