Best way for message structure for Kafka Events for CUD operations to a table

Question

I am creating three different event types for Kafka or any queue for an insert, update or delete to a table in SQL server. I am thinking of how the message should be structured. What is the best way to structure these messages in Kafka or any streaming queue like azure event hubs, rabbitmq?

Message Value for Update

{
  "tableName": "string",
  "tableKey": [
    {
      "key": "string",
      "value": "string"
    }
  ],
  "columns": [
    {
      "columnName": "string",
      "columnValue": "string"
    },
    {
      "columnName": "string",
      "columnValue": "string"
    }
  ]
}

Message Value for Delete

{
   "tableName":"string",
   "tableKey": [
    {
      "key": "string",
      "value": "string"
    }
  ]
}

Message Value for Insert

{
   "tableName":"string",
   "tableKey": [
    {
      "key": "string",
      "value": "string"
    }
  ],
   "Not sure what should be there because there can be 100 columns in a table"
}

There a lot of CDC tools around which already captures the changes made to SQL tables and generate events for them. You could probably use them directly. For some reason if you don't want to, you can look at their messages and take reference from them. — Rishabh Sharma, Dec 01 '21 at 09:45
@RishabhSharma I have explored debezium but it won't work for us because we have microservices architecture and we have more than 1000 databases for many clients and debezium creates one topic for each table which means it is going to be a massive architecture. Do you know a better CDC tool? — Learn AspNet, Dec 01 '21 at 17:39

Best way for message structure for Kafka Events for CUD operations to a table

0 Answers0