Cloud Spanner Change Watcher without Commit Timestamp

Google Cloud Spanner is a fully managed, scalable, relational database service for regional and global application data. It is the first scalable, enterprise-grade, globally-distributed, and strongly consistent database service built for the cloud specifically to combine the benefits of relational database structure with non-relational horizontal scale.

The open source spanner-change-watcher library can monitor and publish events for data changes in a Cloud Spanner database as an in-process service of a Java application. A general introduction to this library can be found here.

This article describes how Spanner Change Watcher can be used to watch a table that does not contain a commit timestamp column for changes. Having a commit timestamp column in a data table can be good for auditing purposes, but it also has a couple of drawbacks:

• It makes the table unreadable for the remainder of a transaction after a DML statement has updated the commit timestamp column using the PENDING_COMMIT_TIMESTAMP() function.
• Commit timestamp columns should not be the first part of a secondary index.

Data Model

Spanner Change Watcher can watch one or more tables without commit timestamp columns by using a ChangeSets table. The ChangeSets table contains all transactions that should be watched and reported by Spanner Change Watcher, and the actual data tables reference the change set that last modified the row.

Creating a Change Set

Each transaction that contains (or could contain) one or more changes to a table that should be included in a change set, must create a record in the ChangeSets table. This can be done manually, or by using a database client wrapper that is supplied by the Spanner Change Watcher library. The latter will automatically create a change set record for each read/write transaction.

Creating Change Set Manually

Your application can manually create a change set record for each transaction by generating a unique id for the change set and adding a mutation to the transaction. All mutations or updates in the same transaction should reference the unique change set id.

Creating Change Sets Automatically

Change set records can also be generated automatically by using a wrapped database client. This will reduce the amount of boiler plate code in your application for change sets. The application must still add a reference to the change set for each mutation and update that it executes.

Creating a Spanner Watcher

Once you have a data model that contains a ChangeSets table, and your data tables contain a reference to the change sets, you can create a Spanner Change Watcher that will pick up and report all changes to the underlying data tables.

The Spanner Change Watcher library contains builders for watchers that can watch both a complete database (SpannerDatabaseChangeSetPoller.java) as well as individual tables (SpannerTableChangeSetPoller.java).

Publish Changes to PubSub

The Spanner Change Watcher repository also contains the spanner-change-publisher library. This library can be used to publish changes that are reported by a Spanner Change Watcher to PubSub. Spanner Change Publishers accept any implementation of the SpannerDatabaseChangeWatcher and SpannerTableChangeWatcher interfaces. This means that the change set pollers can also be used to publish changes to PubSub.

More Samples

See https://github.com/cloudspannerecosystem/spanner-change-watcher/tree/master/samples for more samples on how to use Cloud Spanner Watcher and Cloud Spanner Publisher, including samples for:

• Error handling
• Configuring the poll interval
• Using a custom commit timestamp repository
• Using sharding to allow indexing of the commit timestamp column to optimize polling