How to implement custom CDC tools in Golang?

CDC

Change Data Capture (CDC) is a technology for tracking database changes that allows developers to capture inserts, updates, and deletes applied to rows. It is an essential component for data integration and real-time processing tasks. In this article, we will discuss how to develop custom CDC tools in Golang for multiple databases such as PostgreSQL, Oracle, MySQL, MongoDB, and SQL Server.

Usually in the CDC field or the big data field, the Java ecosystem is relatively prosperous, such as Flink, Spark, which have become popular recently. paimon They are all written in Java. The prosperity of Java in the data ecosystem provides soil for the development of corresponding data tools. So what if we, Gopher, also want to develop CDC tools? Today we introduce some golang Libs. Based on these libs, we can also implement customized CDC tools.

PostgreSQL

For PostgreSQL we can use pglogrepl library (github.com/jackc/pglogrepl). This library provides a low-level API for logical decoding and streaming replication protocols in PostgreSQL. It allows you to read PostgreSQL's write-ahead logs (WAL), which are where all changes to the database are stored. By reading and decoding these logs we can track changes in the database. Decoding can be done at the plugin level or at the consumer level, depending on the decoding plugin used in PostgreSQL.

Oracle

Creating a CDC tool for Oracle is a bit more complicated. Oracle has a built-in tool called “LogMiner” that allows you to query online and archived redo log files through a SQL interface. The primary source of data will be the V$LOGMNR_CONTENTS view, which is the view of the redo log data after LogMiner mines it.

Our CDC tool needs to periodically query this view and parse the SQL_REDO and SQL_UNDO fields to understand changes made to the database. This requires understanding Oracle's SQL syntax, and possibly working with different versions of Oracle, as the syntax may change.

MySQL

can use go-mysql Library (github.com/go-mysql-org/go-mysql/canal) handles MySQL. This package provides a framework for synchronizing MySQL's binlog to other systems. It supports synchronizing MySQL's binlog to user-defined handlers such as stdout and Kafka message queues. By using this library we can track changes in the database relatively simply.

MongoDB

For MongoDB we can use mongo-driver/mongo package (go.mongodb.org/mongo-driver/mongo). This package provides the MongoDB driver API for Go. The MongoDB driver supports “Change Streams”, which allow applications to access real-time data changes without the complexity and risk of trailing oplogs. Applications can use change streams to subscribe to and respond to all data changes on a single collection, database, or entire deployment immediately.

SQL Server

For SQL Server we can utilize go-mssqldb package (github.com/denisenkom/go-mssqldb). SQL Server supports change tracking, which tracks DML changes (inserts, updates, deletes) on tables. By querying these change tables, we can obtain information about the changes. Note that this only tells us the key of the changed row, not the data itself. To get the changed data we need to make another query to the actual data table.

in conclusion

Creating a custom CDC tool in Golang involves understanding the underlying mechanism that each database uses to record changes. By leveraging the capabilities of existing packages, we can build a powerful tool that can track changes to many types of databases. However, implementing an efficient and effective CDC tool requires a thorough understanding of each database's logging mechanism, as well as a solid mastery of Golang.

---

————-The End————-

subscribe to my blog by scanning my public wechat account