I started my career as a DBA with SQL Server 6.0. around about 2015.  I was a new hire who was scheduled to support our SQL Server and MySQL databases. However, soon after my arrival, the CTO called me into his office to tell me I was the new Cassandra DBA. My reply was “That’s great. What’s Cassandra?”  

The first 6 months were rough. The cluster had been in operation for more than 6 months but was not doing too well. Performance was poor and, worse, it frequently crashed. It was not a fun time. But eventually, the problems got fixed. 

There were several issues (including my inexperience) that caused these problems, but the core one was that the original developer had treated it like another relational database. 

You will not learn Cassandra by reading this. Rather, my goal is to assist with your transition from SQL Server to Apache Cassandra® and hopefully help you avoid most of the pitfalls I encountered when making my transition. 

This article will focus on Cassandra itself and not go into how to query Cassandra. 

What’s Cassandra?

Apache Cassandra is an open source, NoSQL, distributed database. Facebook developed it in 2011 as a “merger of Amazon’s Dynamo distributed storage and replication techniques and Google’s Bigtable data and storage engine model. The current stable version as of August 2022 is 4.0.6

Cassandra is written in Java and runs within a Java virtual machine (JVM). 

Apache Cassandra is licensed under the very permissive Apache Licence 2.0. It can be freely downloaded from the Apache Cassandra download site. Source code can be downloaded from the Apache Git repository. Cassandra runs on most Linux distributions. Microsoft Windows is no longer supported.

NoSQL is a broad term used to describe non-relational databases. Cassandra stores data as rows and columns in tables. Each row is a collection of columns where a column is a mapping of a key, a value, and metadata such as creation time. Relationships between tables are not supported.

A Cassandra cluster is a group of Cassandra installations, called nodes. All nodes are peers and can perform any operation. Data is sharded across the nodes in partitions. Cassandra clusters are easily scaled out by adding nodes which can be done while the cluster is running. Large-scale clusters can handle petabytes of data. 

See our content library for our e-book, A Guide to Apache Cassandra.  For a comprehensive guide to Cassandra Architecture, look here.  

Why on Earth Would I Use Cassandra? 

This was my question after asking “what’s Cassandra?” The simple answer was senior management wanted a database that could easily scale and be highly available. The marketing department was projecting customer usage that would require 20+ TB of storage and it had to survive potential hardware failures. We were a Microsoft SQL Server shop so that was the first place we looked. In the end, Cassandra was chosen.

A Very Simplified View of Cassandra Data Distribution

Every node added to the cluster is assigned a range of hash values, or tokens (in actual practice, it is a range of tokens but let’s keep it simple).  When a new record is created, Cassandra generates a consistent hash, a token, from the partition key which is part of the record’s primary key. The record is then stored on the node that “owns” that token. A collection of records with the same partition key form a partition. All rows in a partition must reside on the same node. 

Glossary of Terms

Node   An instance of Cassandra. 

Cluster   A collection of nodes working together. 

Datacenter  A subdivision of a cluster.  A cluster can have multiple datacenters which can be geographically separated. Data is replicated among the datacenters.

Keyspace  The Cassandra equivalent of a database.  It serves as the namespace for the table. It is also used to define the replication factor of the keyspace for each datacenter. 

Replication Factor   The number of data replicas made.  Each replica is stored on a different node to ensure fault tolerance. 

Partition  A group of rows that share the same partition key. It is how Cassandra shards data.   The location of the partition is a function of its hash value.  For more information see Apache Cassandra Data Partitioning.

Consistency Level  The number of replicas that must successfully respond to a request. 

Tombstone  A special marker written whenever data is deleted, the NULL value is inserted, or a non-frozen collection is used. Excess tombstones can cause long GC pauses, latency, read failures, or out-of-heap errors. For more information see Managing Tombstones in Cassandra. 

SSTable (Sorted String Table)  An immutable data file that Cassandra uses for persisting data on a disk.

Memtable   A memory structure where Cassandra buffers write data. In general, there is one active memtable per table. Eventually, a memtable is flushed onto the disk and becomes an SSTable.

Major Differences From SQL Server

Data is stored as a Log-Structured-Merger (LSM) tree. The use of this structure avoids the need for a read before a write. Cassandra groups inserts and updates in memory, and, at intervals, sequentially writes the data to disk in append mode. Once written to disk, the data is immutable and is never overwritten. Write latency is generally sub-millisecond and Cassandra can inject large amounts of data very quickly. 

Cassandra does not follow the relational model. Data is modeled on the query. Joins and sub-queries are not supported. Data denormalization and table duplication are required. 

The design of tables is driven by queries. Tables, specifically the primary keys, must conform to the restrictions placed on data searching by the architecture. 

Searching data is restricted. Only primary key columns can be used to select data. By default, Cassandra will allow only search arguments that return a single partition, which makes the partition key columns mandatory in a search argument.  

Sort order is a design decision. The sort order available on queries is fixed and is determined entirely by the selection of clustering columns. 

Cassandra is not ACID compliant. Only one command (BATCH) guarantees Atomicity and isolation. 

Data Consistency is a design decision. The level of consistency can be adjusted at the query level. 

Indexing is limited.  Cassandra provides Secondary Indexes and SSTable Attached Secondary Index (SASI) for querying on non-key fields but both types should be used with caution.

CQL is not T-SQL.  Although the syntax is very similar, familiar commands such as DELETE and UPDATE have different behaviors.   

The Relational Model Does Not Work

I was going to title this section “Put Away your E.F. Codd ” but I am not sure if he is still referenced today.  If you sweated to create a correctly normalized database, congratulations. However, if you try to use it with Cassandra it will not work. Many years ago, I went to work for a company whose SQL Server data was described to me as “a collection of Excel spreadsheets”. Thinking back, it might have made a good Cassandra database.

Designing tables in Cassandra is shaped by the following limits:

• Queries that would scan all partitions are prohibited by default. This means the entire partition key must be used in all searches. 
• Table joins are not supported. (see above).
• You cannot rely on secondary indexes. They do not perform well and should be used only in limited use cases. 
• Queries should return only a single partition. Writing a query that returns multiple nodes is expensive because Cassandra must visit multiple nodes. 
• Searches are restricted. Only primary key columns are allowed. Partition key columns allow only equality searches. There are restrictions on how the clustering columns may be searched. 

Tables must be modeled on the query, not the data. You must identify all possible queries at the BEGINNING of the design process not towards the end. 

Data denormalization is required.  If you need data from more than one table, they must be merged into a single table.

Tables must be duplicated. If a table needs to be queried by a non-key column, a duplicate table with a new primary key must be created. 

See data modeling in the Apache Cassandra Documentation for a quick overview.

For a more in-depth treatment, see our e-book  “Apache Cassandra® Data Modeling Guide to Best Practices”, in our content library.

Cassandra Is Not ACID-Compliant

Cassandra is not an ACID (Atomicity, Consistency, Isolation, and Durability) compliant system.  Rather, its behavior is explained by the CAP theorem and its extension, the PACELC theorem. 

What this means is that there is a tradeoff between Availability (every request receives a non-error response) over Consistency (every read receives the most recent write). In addition, there is a tradeoff between consistency and latency. 

By default, Cassandra provides eventual consistency. Simply stated, this means that a read that follows a write is not guaranteed to return the latest data. Once the data is finished replicating, consistency is restored. At least until the next write.  

However, it offers “tunable” consistency. Developers can change (“tune”)  this default behavior at the query level to increase data consistency at the expense of availability. In an extreme case, a developer could make Cassandra behave like SQL Server.

Cassandra does not support isolation or atomicity. If 2 processes update the same data at the same time, Cassandra follows the principle of “last write wins”.  Exceptions to this behavior can be found in using the BATCH command and lightweight transactions. However, there is a performance penalty when either of these commands is used.

Tables

Cassandra stores data in a table organized into rows and columns: 

• A row is a collection of one or more columns uniquely identified by a primary key
• A column is a mapping of the column name (the key), the key value, and metadata such as creation time or expiration date. 

You must define a schema, column names, and data type, for a table with the CREATE TABLE command. Cassandra does not support column constraints. 

It is not necessary for a row to contain all of the defined columns. If data does not exist for a column, the practice in Cassandra is to not set a NULL value. 

A Primary Key Has an Expanded Role

The way primary keys work in Cassandra is an important concept to grasp and it works somewhat differently than in SQL Server.  

The primary key has 2 parts: the partition key and the clustering key. The partition key is mandatory. The neatest equivalent is the clustered primary key.  The entire primary key uniquely identifies a row. However,  the primary key does not enforce the uniqueness constraint. If a duplicate key is entered, the existing row is replaced.

The goal of the partition key is to evenly distribute data across the cluster and to query the data efficiently. A partition key that does not do this severely affects cluster performance and scalability.

The clustering key physically sorts rows with a partition. It determines and fixes the sort order available for queries. The order cannot be changed by using the ORDER BY in your query.

The primary key cannot be dropped or altered after table creation. The only way to resolve problems stemming from poor design choices is to create a new table with a better key and then copy the data from the old table.  

Data Types

There is some overlap between SQL Server and Cassandra. Many of the common data types for integer and decimal values are found in CQL. All text strings are defined by the text type.  There are no char or varchar types. All binary data is defined by the blob type. 

XML data is not supported.

For a complete list of available data types, look here.

Table Features Not Supported by Cassandra

• Constraints 
• Computed columns  
• The NULL property
• Foreign Keys
• Encryption
• Identity property.  The CQL Counter type is the closest analogy but can be used only on counter tables.
• XML data types and functions. 

The Memtable and SSTables

The write path in Cassandra somewhat resembles that of SQL Server. The data is first written to an on-disk commitlog and then to an on-heap structure called a memtable and, when that is full, the contents are flushed to disk in the form of a Sorted String Table (SSTable).

The contents of an SSTable are never overwritten. 

CQL Versus T-SQL

The syntax of Cassandra Query Language is very similar to Transact-SQL. However, there are some familiar commands that behave differently. 

• Object names in CQL are case-sensitive in CQL but DDL and DML commands are not. 
• The DELETE command does not remove data from the table.  Instead, the command inserts a special marker called a tombstone to indicate which data is being deleted.  Deleted data will be removed at a later time during the compaction process. 
• UPDATE will insert a new record that contains the columns which were modified. Data is never overwritten in Cassandra.
• Filtering Data with the WHERE clause is restricted to using primary key columns and must conform to a number of restrictions. For example, the partition keys allow only equality searches. 
• The ORDER BY clause can use only cluster key columns.
• CREATE FUNCTION By default, Cassandra supports defining functions in Java and JavaScript. 
• CREATE INDEX creates a secondary index on a non-key column  Do not use them as you would a non-clustered index. They do not perform well and should be used sparingly and only in limited use cases.
• CREATE VIEW creates a materialized view. Originally intended as a method to replace data redundancy, the feature has numerous problems and is disabled by default in Cassandra 4. Do not use it in a production cluster.
• CREATE TRIGGER supports the JAVA language.
• ALTER TABLE can not alter  PRIMARY KEY columns.
• The standard aggregate functions of min, max, avg, sum, and count are built-in functions.  Cassandra will generate a warning message when an aggregate function is used with no WHERE clause. If this is done on a very large table, there is the possibility that one or more nodes will crash. 

Below is a quick guide to how SQL Server objects map to Cassandra.

Table 1:   SQL Server Object Mapping to CQL

Concluding Thoughts

If there is one thing you must get right when using Cassandra, it is designing the primary key—particularly the partition key. Instaclustr offers a variety of services that can help the first-time Cassandra user. To see how we can help the first-time Cassandra developer, please see our pages on Cassandra consulting services and training.