Database changes to Apache Kafka with Repstance

Repstance reads the changes of a database and publishes them to Apache Kafka as JSON messages, one topic for every table. A row that is inserted, updated or deleted in Oracle, SQL Server, MySQL or PostgreSQL becomes a message that applications, stream processors and loaders read from the topic after the transaction has been committed.

How it works

The Capture Process reads the changes from the log or the change data capture of the Source Database, in the same way as for any other Target. The Apply Process publishes them. It does not change a database: it sends the messages to the brokers of your cluster and saves its position only after the brokers have acknowledged them.

• A topic for every table. The name is made of a prefix of your choice, the schema and the table, for example prod.dbo.emp. Mapping rules can rename a table, leave out tables and columns, or name the columns that make the key of a table.
• The key is the key of the row. All the changes of one row go to the same partition, so a consumer reads them in the order in which they were committed.
• Before and after. An update has the row as it was and as it is now. A delete has the row that was deleted, followed by a tombstone, so that a compacted topic can forget the row.
• The rows that exist now. An Initial Load publishes the current rows of the tables as snapshot messages, and the changes follow them. A new consumer can start from the beginning of the topic.
• Changes of the structure. A new, dropped or renamed column, a new table, a drop or a truncate is an event in a topic of its own, with the columns before and after and the statement of the source.
• Delivery. A message is published at least once. After a failure or a restart the last messages can be published again, identical to the first ones and with the same position (the LSN of the transaction and the number of the change), so a consumer can drop them. A very big transaction (above 120 MiB by default) is published in one Kafka transaction, which a consumer with read_committed sees whole or not at all.
• Secured clusters. TLS, a client certificate, and the logins PLAIN, SCRAM-SHA-256 and SCRAM-SHA-512.

A message

This is the message of an insert into the table dbo.emp. The key is {"id":4}:

Set it up

Create the Capture Process as usual, then check the brokers, create the Apply Process and start it:

The same can be done in the Web UI. The help describes every step and every parameter: Replication to Apache Kafka and Kafka Message Format.

What to know

• Apache Kafka is a Target of Repstance Advanced Edition. The messages are JSON; Avro and a schema registry are not supported.
• Delivery is at least once, not exactly once: a consumer should apply the messages by their key or drop the repeated positions.
• A message is limited by the size that the brokers accept. The longest strings of a bigger row are cut, and the message lists the columns that were cut.

Apache, Apache Kafka, Kafka and the Kafka logo are either registered trademarks or trademarks of the Apache Software Foundation in the United States and/or other countries. No endorsement by The Apache Software Foundation is implied by the use of these marks.