Apache Cassandra is a NoSQL Database Management System designed to store large amounts of data. A Cassandra cluster configuration provides horizontal scaling and replication of data across multiple machines. It can provide high availability and eliminate a single point of failure by replicating data to multiple nodes within a Cassandra cluster. Apache Cassandra is open source and designed to run on low-cost commodity hardware.

Cassandra relaxes the axioms of a traditional relational database management systems (RDBMS) regarding atomicity, consistency, isolation, and durability. When considering implementing Cassandra, it is important to understand its differences from a traditional RDBMS and how those differences affect your specific use case.

Cassandra provides eventual consistency. Under the eventual consistency model, accessing the state of data for a specific row eventually returns the latest state of the data for that row as defined by the most recent change. However, there may be a latency period between the creation and modification of the state of a row and what is returned when the state of that row is queried. The benefit of eventual consistency is that the latency period is predicted based on your Cassandra configuration and the level of work load that your Cassandra cluster is currently under, see http://cassandra.apache.org/.

Review the data type support, see About the Cassandra Data Types.

Prerequisites

• Apache Cassandra cluster must have at least one node up and running.

• Read and write access to CDC commit log files on every live node in the cluster is done through SFTP or NFS. For more information, see Setup SSH Connection to the Cassandra Nodes.

• Every node in the Cassandra cluster must have the cdc_enabled parameter set to true in the cassandra.yaml configuration file.

• Virtual nodes must be enabled on every Cassandra node by setting the num_tokens parameter in cassandra.yaml .

• You must download the third party libraries using Dependency downloader scripts. For more information, see Cassandra Capture Client Dependencies.

• New tables can be created with Change Data Capture (CDC) enabled using the WITH CDC=true clause in the CREATE TABLE command. For example:

  CREATE TABLE ks_demo_rep1.mytable (col1 int, col2 text, col3 text, col4 text, PRIMARY KEY (col1)) WITH cdc=true;

  You can enable CDC on existing tables as follows:

  ALTER TABLE ks_demo_rep1.mytable WITH cdc=true;

One of the features of a Cassandra cluster is its high availability. To support high availability, multiple redundant copies of table data are stored on different nodes in the cluster. Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) Cassandra Capture automatically filters out duplicate rows (deduplicate). Deduplication is active by default. Oracle recommends using it if your data is captured and applied to targets where duplicate records are discouraged (for example RDBMS targets).

Cassandra nodes can change their status (topology change) and the cluster can still be alive. GG for DAA Cassandra Capture can detect the node status changes and react to these changes when applicable. The Cassandra capture process can detect the following events happening in the cluster:

• Node shutdown and boot.

• Node decommission and commission.

• New keyspace and table created.

Due to topology changes, if the capture process detects that an active producer node goes down, it tries to recover any missing rows from an available replica node. During this process, there is a possibility of data duplication for some rows. This is a transient data duplication due to the topology change. For more details about reacting to changes in topology, see Troubleshooting.

The Cassandra CDC API can only read data from commit log files in the CDC directory. There is a latency for the data in the active commit log directory to be archived (moved) to the CDC commit log directory.

The input data source for the Cassandra capture process is the CDC commit log directory. There could be delays for the data to be captured mainly due to the commit log files not yet visible to the capture process.

On a production cluster with a lot of activity, this latency is very minimal as the data is archived from the active commit log directory to the CDC commit log directory in the order of microseconds.

Cassandra Extract supports the standard initial load capability to extract source table data to GG for DAA trail files.

Initial load for Cassandra can be performed to synchronize tables, either as a prerequisite step to replicating changes or as a standalone function.

Direct loading from a source Cassandra table to any target table is not supported.

Review the example .prm files from GG for DAA installation directory under $HOME/AdapterExamples/big-data/cassandracapture.

1. In Administration Service, click Add Extract.
2. Enter a Process Name, select Source as Cassandra and select Change Data Capture Extract.
3. Provide a name for Extract Trail and select Source Credentials you created for Cassandra.
4. Update Managed Options if necessary.
5. In the Parameter File, update the fields marked as TODO.
6. Create & Run.
7. Configure the Extract parameter file:

   Apache Cassandra 4x SDK, compatible with Apache Cassandra 4.0 version

   Extract parameter file:

   -- ggsci> alter credentialstore add user cassandra://db-user@127.0.0.1 password db-passwd alias cass
   EXTRACT groupname
   
   JVMOPTIONS CLASSPATH ggjava/ggjava.jar:DependencyDownloader/dependencies/cassandra_capture_4x/*
   JVMOPTIONS BOOTOPTIONS -Dcassandra.config=file://{/path/to/apache-cassandra-4.x}/config/casandra.yaml -Dcassandra.datacenter={datacenter-name}
   
   TRANLOGOPTIONS CDCREADERSDKVERSION 4x
   TRANLOGOPTIONS CDCLOGDIRTEMPLATE /path/to/data/cdc_raw
   SOURCEDB USERIDALIAS cass
   EXTTRAIL trailprefix
   TABLE source.*;

   1. Provide the cassandra.yaml file path using JVMOPTIONS BOOTOPTIONS.

      JVMOPTIONS BOOTOPTIONS -Dcassandra.config=file://{/path/to/apache-cassandra-4.x}/config/casandra.yaml -Dcassandra.datacenter={datacenter-name}

      Note:

      For a remote capture (when the Cassandra server is not on the same machine as Oracle GoldenGate), you need to copy a sample cassandra.yaml for your Apache Cassandra version onto your GoldenGate Machine and use this path for this cassandra.yaml file path configuration.
   2. Configure cassandra datacenter name under JVMOPTIONS BOOTOPTIONS. If you do not provide a value, then by default, datacenter1 is considered.

   Apache Cassandra 3x SDK, compatible with Apache Cassandra 3.9, 3.10, 3.11

   Extract parameter file:

    -- ggsci> alter credentialstore add user cassandra://db-user@127.0.0.1 password db-passwd alias cass
   JVMOPTIONS CLASSPATH ggjava/ggjava.jar:DependencyDownloader/dependencies/cassandra_capture_3x/*
   TRANLOGOPTIONS CDCREADERSDKVERSION 3x
   TRANLOGOPTIONS CDCLOGDIRTEMPLATE /path/to/data/cdc_raw
   SOURCEDB USERIDALIAS cass
   EXTTRAIL trailprefix
   TABLE source.*;

   DSE Cassandra SDK, compatible with DSE Cassandra 6.x versions

   Extract parameter file

     -- ggsci> alter credentialstore add user cassandra://db-user@127.0.0.1 password db-passwd alias cass
       EXTRACT groupname
       JVMOPTIONS CLASSPATH ggjava/ggjava.jar:{/path/to/dse-6.x}/resources/cassandra/lib/*:{/path/to/dse-6.x}/lib/*:{/path/to/dse-6.x}/resources/dse/lib/*:DependencyDownloader/dependencies/cassandra_capture_dse/*
       JVMOPTIONS BOOTOPTIONS -Dcassandra.config=file://{/path/to/dse-6.x}/resources/cassandra/conf/casandra.yaml -Dcassandra.datacenter={datacenter-name}
       TRANLOGOPTIONS CDCREADERSDKVERSION dse
       TRANLOGOPTIONS CDCLOGDIRTEMPLATE /path/to/data/cdc_raw
       SOURCEDB USERIDALIAS cass
       EXTTRAIL trailprefix
       TABLE source.*;

   1. Provide the cassandra.yaml file path using JVMOPTIONS BOOTOPTIONS:

      JVMOPTIONS BOOTOPTIONS -Dcassandra.config=file://{/path/to/dse-6.x}/resources/cassandra/conf/casandra.yaml -Dcassandra.datacenter={datacenter-name}

      Note:

      For a remote capture (when the Cassandra server is not on the same machine as Oracle GoldenGate), you need to copy a sample cassandra.yaml for your DSE version onto your GoldenGate Machine and use this path for this cassandra.yaml file path configuration.
   2. Configure cassandra datacenter name under JVMOPTIONS BOOTOPTIONS. If you do not provide a value, then by default, Cassandra is considered.

   Note:

   For DSE 5.x version, configure the extract with Apache 3x SDK as explained in the Apache 3x section.

You must take additional care when replicating source UPDATE operations from Cassandra trail files to RDMBS targets. Any source UPDATE operation appears as an INSERT record in the Oracle GoldenGate trail file. Replicat may abend when a source UPDATE operation is applied as an INSERT operation on the target database.

You have these options:

• OVERRIDEDUPS: If you expect that the source database is to contain mostly INSERT operations and very few UPDATE operations, then OVERRIDEDUPS is the recommended option. Replicat can recover from duplicate key errors while replicating the small number of the source UPDATE operations.

• UPDATEINSERTS and INSERTMISSINGUPDATES: Use this configuration if the source database is expected to contain mostly UPDATE operations and very few INSERT operations. With this configuration, Replicat has fewer missing row errors to recover, which leads to better throughput.

• No additional configuration is required if the target table can accept duplicate rows or you want to abend Replicat on duplicate rows.

If you configure Replicat to use BATCHSQL, then there may be duplicate row or missing row errors in batch mode. Although there is a reduction in the Replicat throughput due to these errors, Replicat automatically recovers from these errors. If the source operations are mostly INSERTS, then BATCHSQL is a good option.

When the source Cassandra table has static columns, the static column values can be modified by skipping any clustering key columns that are in the table.

For example:

create table ks_demo_rep1.nls_staticcol
(
    teamname text,
    manager text static,
    location text static,
    membername text,
    nationality text,
    position text,
    PRIMARY KEY ((teamname), membername)
)
WITH cdc=true;
insert into ks_demo_rep1.nls_staticcol (teamname, manager, location) VALUES ('Red Bull', 'Christian Horner', '<unknown>

The insert CQL is missing the clustering key membername. Such an operation is a partition insert.

Similarly, you could also update a static column with just the partition keys in the WHERE clause of the CQL that is a partition update operation. Cassandra Extract cannot write a INSERT or UPDATE operation into the trail with missing key columns. It abends on detecting a partition INSERT or UPDATE operation.

A Cassandra table may have a primary key composed on one or more partition key columns and clustering key columns. When a DELETE operation is performed on a Cassandra table by skipping the clustering key columns from the WHERE clause, it results in a partition delete operation.

For example:

create table ks_demo_rep1.table1
(
 col1 ascii, col2 bigint, col3 boolean, col4 int,
 PRIMARY KEY((col1, col2), col4)
) with cdc=true;

delete from ks_demo_rep1.table1 where col1 = 'asciival' and col2 = 9876543210; /** skipped clustering key column col4 **/

Cassandra Extract cannot write a DELETE operation into the trail with missing key columns and abends on detecting a partition DELETE operation.

• Cassandra Extract can connect to a Cassandra cluster using username and password based authentication and SSL authentication.

• Connection to Kerberos enabled Cassandra clusters is not supported in this release.

For more information, see How to Run the Purge Utility.

Multiple Extract groups in a single Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) installation can be configured to connect to the same Cassandra cluster.

To run multiple Extract groups:

1. One (and only one) Extract group can be configured to move the commit log files in the cdc_raw directory on the Cassandra nodes to a staging directory. The movecommitlogstostagingdir parameter is enabled by default and no additional configuration is required for this Extract group.
2. All the other Extract groups should be configured with the nomovecommitlogstostagingdir parameter in the Extract parameter (.prm) file.

The following properties are used with Cassandra change data capture.

No data captured by the Cassandra Extract process.

• The Cassandra database has not flushed the data from the active commit log files to the CDC commit log files. The flush is dependent on the load of the Cassandra cluster.

• The Cassandra Extract captures data from the CDC commit log files only.

• Check the CDC property of the source table. The CDC property of the source table should be set to true.

• Data is not captured if the TRANLOGOPTIONS CDCREADERSDKVERSION 3.9 parameter is in use and the JVMCLASSPATH is configured to point to Cassandra 3.10 or 3.11 JAR files.

Error: OGG-01115 Function getInstance not implemented.

• The following line is missing from the GLOBALS file.

  OGGSOURCE CASSANDRA

Error: Unable to connect to Cassandra cluster, Exception: com.datastax.driver.core.exceptions.NoHostAvailableException

This indicates that the connection to the Cassandra cluster was unsuccessful.

Check the following parameters:

CLUSTERCONTACTPOINTS

Error: Exception in thread "main" java.lang.NoClassDefFoundError: oracle/goldengate/capture/cassandra/CassandraCDCProcessManager

Check the JVMOPTIONS CLASSPATH parameter in the GLOBALS file.

Error: oracle.goldengate.util.Util - Unable to invoke method while constructing object. Unable to create object of class "oracle.goldengate.capture.cassandracapture311.SchemaLoader3DOT11" Caused by: java.lang.NoSuchMethodError: org.apache.cassandra.config.DatabaseDescriptor.clientInitialization()V

There is a mismatch in the Cassandra SDK version configuration. The TRANLOGOPTIONS CDCREADERSDKVERSION 3.11 parameter is in use and the JVMCLASSPATH may have the Cassandra 3.9 JAR file path.

Error: OGG-25171 Trail file '/path/to/trail/gg' is remote. Only local trail allowed for this extract.

A Cassandra Extract should only be configured to write to local trail files. When adding trail files for Cassandra Extract, use the EXTTRAIL option. For example:

ADD EXTTRAIL ./dirdat/z1, EXTRACT cass

Errors: OGG-868 error message or OGG-4510 error message

The cause could be any of the following:

• Unknown user or invalid password

• Unknown node address

• Insufficient memory

Another cause could be that the connection to the Cassandra database is broken. The error message indicates the database error that has occurred.

Error: OGG-251712 Keyspace keyspacename does not exist in the database.

The issue could be due to these conditions:

• During the Extract initial load process, you may have deleted the KEYSPACE keyspacename from the Cassandra database.

• The KEYSPACE keyspacename does not exist in the Cassandra database.

Error: OGG-25175 Unexpected error while fetching row.

This can occur if the connection to the Cassandra database is broken during initial load process.

Error: “Server-side warning: Read 915936 live rows and 12823104 tombstone cells for query SELECT * FROM keyspace.table(see tombstone_warn_threshold)”.

When the value of the initial load DBOPTIONS FETCHBATCHSIZE parameter is greater than the Cassandra database configuration parameter,tombstone_warn_threshold, this is likely to occur.

Increase the value of tombstone_warn_threshold or reduce the DBOPTIONS FETCHBATCHSIZE value to get around this issue.

Duplicate records in the Cassandra Extract trail.

Internal tests on a multi-node Cassandra cluster have revealed that there is a possibility of duplicate records in the Cassandra CDC commit log files. The duplication in the Cassandra commit log files is more common when there is heavy write parallelism, write errors on nodes, and multiple retry attempts on the Cassandra nodes. In these cases, it is expected that Cassandra trail file will have duplicate records.

JSchException or SftpException in the Extract Report File

Verify that the SFTP credentials (user, password, and privatekey) are correct. Check that the SFTP user has read and write permissions for the cdc_raw directory on each of the nodes in the Cassandra cluster.

ERROR o.g.c.c.CassandraCDCProcessManager - Exception during creation of CDC staging directory [{}]java.nio.file.AccessDeniedException

The Extract process does not have permission to create CDC commit log staging directory. For example, if the cdc_raw commit log directory is /path/to/cassandra/home/data/cdc_raw, then the staging directory would be /path/to/cassandra/home/data/cdc_raw/../cdc_raw_staged.

Extract report file shows a lot of DEBUG log statements

On production system, you do not need to enable debug logging. To use INFO level logging, make sure that the Extract parameter file include this

JVMBOOTOPTIONS -Dlogback.configurationFile=AdapterExamples/big-data/cassandracapture/logback.xml

To enable SSL in Oracle Golden Gate Cassandra Extract you have to enable SSL in the GLOBALS file or in the Extract Parameter file.

If SSL Keyword is missing, then Extract assumes that you wanted to connect without SSL. So if the Cassandra.yaml file has an SSL configuration entry, then the connection fails.

SSL is enabled and it is one-way handshake

You must specify the CPPDRIVEROPTIONS SSL PEMPUBLICKEYFILE /scratch/testcassandra/testssl/ssl/cassandra.pem property.

If this property is missing, then Extract generates this error:.

2018-06-09 01:55:37 ERROR OGG-25180 The PEM formatted public key file used to verify the peer's certificate is missing.

SSL is enabled and it is two-way handshake

You must specify these properties for SSL two-way handshake:

CPPDRIVEROPTIONS SSL ENABLECLIENTAUTH 
CPPDRIVEROPTIONS SSL PEMCLIENTPUBLICKEYFILE /scratch/testcassandra/testssl/ssl/datastax-cppdriver.pem
CPPDRIVEROPTIONS SSL PEMCLIENTPRIVATEKEYFILE /scratch/testcassandra/testssl/ssl/datastax-cppdriver-private.pem
CPPDRIVEROPTIONS SSL PEMCLIENTPRIVATEKEYPASSWD cassandra

Additionally, consider the following:

• If ENABLECLIENTAUTH is missing then Extract assumes that it is one-way handshake so it ignores PEMCLIENTPRIVATEKEYFILE and PEMCLIENTPRIVATEKEYFILE. The following error occurs because the cassandra.yaml file should have require_client_auth set to true.

  2018-06-09 02:00:35  ERROR   OGG-00868  No hosts available for the control connection.

• If ENABLECLIENTAUTH is used and PEMCLIENTPRIVATEKEYFILE is missing, then this error occurs:

  2018-06-09 02:04:46  ERROR   OGG-25178  The PEM formatted private key file used to verify the client's certificate is missing. For two way handshake or if ENABLECLIENTAUTH is set, then it is mandatory to set PEMCLIENTPRIVATEKEYFILE in your Oracle GoldenGate GLOBALS file or in Extract parameter file.

• If ENABLECLIENTAUTH is use and PEMCLIENTPUBLICKEYFILE is missing, then this error occurs:

  2018-06-09 02:06:20  ERROR   OGG-25179  The PEM formatted public key file used to verify the client's certificate is missing. For two way handshake or if ENABLECLIENTAUTH is set, then it is mandatory to set PEMCLIENTPUBLICKEYFILE in your Oracle GoldenGate GLOBALS file or in Extract parameter file.

• If the password is set while generating the client private key file then you must add PEMCLIENTPRIVATEKEYPASSWD to avoid this error:

  2018-06-09 02:09:48  ERROR   OGG-25177  The SSL certificate: /scratch/jitiwari/testcassandra/testssl/ssl/datastax-cppdriver-private.pem can not be loaded. Unable to load private key.

• If any of the PEM file is missing from the specified absolute path, then this error occurs:

  2018-06-09 02:12:39  ERROR   OGG-25176  Can not open the SSL certificate: /scratch/jitiwari/testcassandra/testssl/ssl/cassandra.pem.

com.jcraft.jsch.JSchException: UnknownHostKey

If the extract process ABENDs with this issue, then it is likely that some or all the Cassandra node addresses are missing in the SSH known-hosts file. For more information, see Setup SSH Connection to the Cassandra Nodes.

General SSL Errors

Consider these general errors:

• The SSL connection may fail if you have enabled all SSL required parameters in Extract or GLOBALS file and the SSL is not configured in the cassandra.yaml file.

• The absolute path or the qualified name of the PEM file may not correct. There could be access issue on the PEM file stored location.

• The password added during generating the client private key file may not be correct or you may not have enabled it in the Extract parameter or GLOBALS file.

The following third party libraries are needed to run Cassandra Change Data Capture.

Capturing from Apache Cassandra 3.x versions:

• cassandra-driver-core (com.datastax.cassandra) version 3.3.1
• cassandra-all (org.apache.cassandra) version 3.11.0
• gson (com.google.code.gson) version 2.8.0
• jsch (com.jcraft) version 0.1.54

You can use the Dependency Downloader scripts to download the Datastax Java Driver and its associated dependencies. For more information, see Dependency Downloader.