Cassandra Data Model with Simple Database Example

Although Cassandra query language resembles SQL language, their data modelling methods are totally different.

In Cassandra, a bad data model can degrade performance, especially when users try to implement the RDBMS concepts on Cassandra. It is best to keep in mind few rules detailed below.

Cassandra Data Model Rules

In Cassandra, writes are not expensive. Cassandra does not support joins, group by, OR clause, aggregations, etc. So you have to store your data in such a way that it should be completely retrievable. So these rules must be kept in mind while modelling data in Cassandra.

Maximize the number of writes

In Cassandra, writes are very cheap. Cassandra is optimized for high write performance. So try to maximize your writes for better read performance and data availability. There is a tradeoff between data write and data read. So, optimize your data read performance by maximizing the number of data writes.

Maximize Data Duplication

Data denormalization and data duplication are defacto of Cassandra. Disk space is not more expensive than memory, CPU processing and IOs operation. As Cassandra is a distributed database, so data duplication provides instant data availability and no single point of failure.

Cassandra Data Modeling Goals

You should have following goals while modelling data in Cassandra:

Spread Data Evenly Around the Cluster

You want an equal amount of data on each node of Cassandra Cluster. Data is spread to different nodes based on partition keys that is the first part of the primary key. So, try to choose a high-cardinality column as the partition key for spreading data evenly around the cluster.

Minimize number of partitions read while querying data

Partition are a group of records with the same partition key. When the read query is issued, it collects data from different nodes from different partitions.

If there will be many partitions, then all these partitions need to be visited for collecting the query data.

It does not mean that partitions should not be created. If your data is very large, you cannot keep that huge amount of data on the single partition. The single partition will be slowed down.

So try to choose a balanced number of partitions.

Good Primary Key in Cassandra

Both goals above come down to one decision, so the two schemas below show the same table with a poor key and then a sound one.

Let us take an example and find which primary key is good.

Here is the table MusicPlaylist.

CREATE TABLE MusicPlaylist (
    SongId int,
    SongName text,
    Year int,
    Singer text,
    PRIMARY KEY (SongId, SongName)
);

In above example, table MusicPlaylist,

• SongId is the partition key, and
• SongName is the clustering column
• Data will be clustered on the basis of SongName. Only one partition will be created per SongId, and because each song has a distinct identifier, every partition holds a single row.

Data retrieval will be slow by this data model due to the bad primary key.

Here is another table MusicPlaylist.

CREATE TABLE MusicPlaylist (
    SongId int,
    SongName text,
    Year int,
    Singer text,
    PRIMARY KEY ((SongId, Year), SongName)
);

In above example, table MusicPlaylist,

• SongId and Year are the partition key, and
• SongName is the clustering column.
• Data will be clustered on the basis of SongName. In this table, each year, a new partition will be created. All the songs of the year will be on the same node. This primary key will be very useful for the data.

Our data retrieval will be fast by this data model.

Model Your Data in Cassandra

Following things should be kept in mind while modelling your queries:

Determine what queries you want to support

First of all, determine what queries you want.

For example, do you need?

• Joins
• Group by
• Filtering on which column etc.

Create table according to your queries

Create table according to your queries. Create a table that will satisfy your queries. Try to create a table in such a way that a minimum number of partitions needs to be read.

The three sections that follow apply that principle to the three relationship types found in almost every schema.

Handling One to One Relationship in Cassandra

One to one relationship means two tables have one to one correspondence. For example, the student can register only one course, and I want to search on a student that in which course a particular student is registered in.

So in this case, your table schema should encompass all the details of the student in corresponding to that particular course like the name of the course, roll no of the student, student name, etc.

The diagram above shows a single table serving the query, because one student maps to exactly one course.

CREATE TABLE Student_Course (
    Student_rollno int PRIMARY KEY,
    Student_name text,
    Course_name text
);

Because Student_rollno is the partition key, a lookup by roll number reads exactly one partition.

Handling One to Many Relationship in Cassandra

One to many relationships means having one to many correspondence between two tables.

For example, a course can be studied by many students. I want to search all the students that are studying a particular course.

So by querying on course name, I will have many student names that will be studying a particular course.

Here the course name becomes the partition key so that every student on a course lands in the same partition, and the roll number becomes the clustering column so each student remains a distinct row.

CREATE TABLE Student_Course (
    Course_name text,
    Student_rollno int,
    Student_name text,
    PRIMARY KEY (Course_name, Student_rollno)
);

I can retrieve all the students for a particular course by the following query.

SELECT * FROM Student_Course WHERE Course_name = 'Course Name';

Handling Many to Many Relationship in Cassandra

Many to many relationships means having many to many correspondence between two tables.

For example, a course can be studied by many students, and a student can also study many courses.

I want to search all the students that are studying a particular course. Also, I want to search all the course that a particular student is studying.

So in this case, I will have two tables i.e. divide the problem into two cases. This is the clearest illustration of the duplication rule: the same facts are written twice so that each query reads one partition.

First, I will create a table by which you can find courses by a particular student.

CREATE TABLE Student_Course (
    Student_rollno int,
    Course_name text,
    Student_name text,
    PRIMARY KEY (Student_rollno, Course_name)
);

I can find all the courses by a particular student by the following query.

SELECT * FROM Student_Course WHERE Student_rollno = 101;

Second, I will create a table by which you can find how many students are studying a particular course.

CREATE TABLE Course_Student (
    Course_name text,
    Student_rollno int,
    Student_name text,
    PRIMARY KEY (Course_name, Student_rollno)
);

I can find a student in a particular course by the following query.

SELECT * FROM Course_Student WHERE Course_name = 'Cassandra';

Both tables must be written to whenever a student joins a course, normally inside a single logged batch so the two copies stay in step.

Common Cassandra Data Modelling Mistakes

Most poor schemas repeat the same handful of errors, and each traces back to a habit carried over from relational design.

• Unbounded partitions: Choosing a partition key such as a country name puts millions of rows into one partition. Add a time bucket, for example (country, month), to keep partitions within a sensible size.
• Very low cardinality keys: A partition key with only a few possible values, such as a status flag, concentrates all traffic on a few nodes and leaves the rest idle.
• Using ALLOW FILTERING to make a query work: It scans every partition and hides a modelling problem. If a query needs it, the schema needs another table.
• Modelling entities instead of queries: Building a students table and a courses table, then trying to join them in the application, defeats the purpose of the design.
• Frequent deletes and overwrites: Each delete writes a tombstone that must be read and skipped until compaction removes it, which slows reads on hot partitions.

Avoiding these keeps the schema aligned with the rules stated at the top of this page, and with the relational contrasts summarised below.

Difference between RDBMS and Cassandra Data Modelling

These schema decisions are applied in practice in the Cassandra table and keyspace tutorials.

FAQs