Google Cloud Datastream: Google Cloud-Native Change Data Capture Implementation

By Manjit Bhattacharyya, Solutions Architecture With information often spread across several different databases and applications, engineers and developers need to synchronize data reliably and with minimal latency and downtime.  At the 2021 Google Cloud Data Summit, Google Cloud introduced Datastream, a serverless change data capture (CDC) and replication service that is available in preview. This new tool will help teams seamlessly replicate data streams in real-time from Oracle and MySQL databases to Google Cloud services such as BigQuery, Cloud SQL, Cloud Spanner, or leverage the event stream directly from cloud storage.  Main benefits include:

• Easy-to-use setup and monitoring experiences
• Synchronizes data streams across different databases and applications
• Services scale up and down automatically as needed with minimal downtime
• Private connectivity options for enhanced security on top of Google Cloud security
• Transparent status reporting and processing around data and schema changes
• Integrates with Dataflow, Cloud Data Fusion, Pub/Sub, BigQuery, and other Google Cloud data services
• Powers analytics, database replication, and synchronization for migrations and hybrid-cloud configurations

But how does Datastream work exactly? Let’s take a look by loading data from Oracle Database hosted on AWS RDS into BigQuery as an example.

Use Case: Loading Data from Oracle Hosted on AWS RDS into BigQuery

Say you have an application running on Oracle Database (DB) hosted on AWS RDS (or on-premises or any other cloud platform, including Google Cloud). You have an analytical workload running on BigQuery that requires a data sync with the Oracle application. That means any data changes on the Oracle DB application end should also be reflected in BigQuery. In this walkthrough, we’ll see how Datastream can be helpful to solve this use case. We’ll set up a real-time CDC workflow that will use Oracle DB hosted on AWS RDS to load data into BigQuery. 

Set up an Oracle DB on AWS RDS

First, let’s set up an Oracle DB on AWS RDS, the point from where you’d capture changes for replication, or CDC. Before getting started, ensure the endpoint for the Oracle DB RDS instance is accessible from Google Cloud. Note: I’m using SQL Developer to connect to Oracle DB in this example – Oracle DB installation isn’t covered here.

Enable ARCHIVELOG

Oracle DB on AWS RDS enables ARCHIVELOG by default, but you can double-check it’s switched on with the following command:

SELECT LOG_MODE FROM V$DATABASE;

If it says ARCHIVELOG, move onto the next step. But, if it says NOARCHIVELOG, follow the commands for enabling ARCHIVELOG mode for non-RDS deployments and RDS deployments below. RDS Deployments exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours', 96); Non-RDS Deployments

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

Then define a data retention policy for your database by running:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;

Enable Supplemental Logging

If you plan to replicate a subset of tables after ARCHIVELOG is enabled, run the following command for each table you want to replicate:

ALTER TABLE [SCHEMA].[TABLE] ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS

Alternatively, you can enable supplemental log data for the entire database. For a self-hosted deployment, run the following command to enable supplemental log data for the entire database:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

For an Oracle deployment on Amazon RDS, run the following command when it displays the SQL prompt:

exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','ALL');

Grant Privileges

Grant the privileges to the user account that will be used to connect to your database by executing the following commands:

GRANT EXECUTE_CATALOG_ROLE TO [user];
GRANT CONNECT TO [user];
GRANT CREATE SESSION TO [user];
GRANT SELECT ON SYS.V_$DATABASE TO [user];
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO [user];
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO [user];
GRANT SELECT ON SYS.V_$LOGMNR_LOGS TO [user];
GRANT EXECUTE ON DBMS_LOGMNR TO [user];
GRANT EXECUTE ON DBMS_LOGMNR_D TO [user];
GRANT SELECT ANY TRANSACTION TO [user];
GRANT SELECT ANY TABLE TO [user];

For an RDS-hosted deployment, run the following commands:

GRANT EXECUTE_CATALOG_ROLE TO [user];
GRANT CONNECT TO [user];
GRANT CREATE SESSION TO [user];
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$DATABASE','[USER]','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$ARCHIVED_LOG','[USER]','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_LOGS','[USER]','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$LOGMNR_CONTENTS','[USER]','SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR','[USER]','EXECUTE');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR_D','[USER]','EXECUTE');
GRANT SELECT ANY TRANSACTION TO [user];
GRANT SELECT ANY TABLE TO [user];

If your source database is Oracle 12c or newer, you’ll need to grant an additional privilege with the following command:

GRANT LOGMINING TO [user];

This will complete the Oracle DB setup. 

Test the Source

Now, using the schema below, we’ll create a sample table labeled cdc_test to perform a CDC test:

Create a Google Cloud Storage Target and Grant Permissions

Next, create a Google Cloud Storage target and grant the following IAM permissions to your Datastream service account at “service-[project_number]@gcp-sa-datastream.iam.gserviceaccount.com” to write to the destination bucket. 

• roles/storage.objectViewer 
• roles/storage.objectCreator
• roles/storage.legacyBucketReader

Configure the Datastream

Now, you’re ready to start building the Datastream configuration in Google Cloud Console. Go to Datastream under the Big Data section in the navigation menu or search for Datastream directly in the search resources and products section. Once you’re on the Datastream page, complete the following:

• Configure the stream name and stream ID. The stream’s region should be the same as the one for the connection profile. Note: Datastream currently only supports the following regions: us-central1, europe-west1, and asia-east1.
• Cross-check the Oracle DB prerequisites listed under “Oracle Source- Open.” (For this use case, we’ve already completed this step in the “AWS- RDS Oracle DB:-” section above.)