Course – LS – All

Get started with Spring and Spring Boot, through the Learn Spring course:


1. Introduction

Sometimes, we need a quick reference guide to get started in our learning path. In particular, a cheat sheet is a document that contains all the critical information.

In this tutorial, we’ll learn the essential concepts of Cassandra query language (CQL) and how to apply them using a cheat sheet that we’ll build along the way.

2. Cassandra at a Glance

Apache Cassandra is an open-source, NoSQL, and distributed data storage system. This means instead of being able to live only on one server, it spreads across multiple servers. It’s also known for its high availability and partition tolerance.

To put it another way, the design of the Cassandra database is inspired by the “AP” of the CAP theorem.

Furthermore, Cassandra is a masterless architecture, is massively scalable, and above all, provides easy fault detection and recovery.

3. Data Types

Generally, Cassandra supports a rich set of data types. These include native types, collection types, user-defined types, and tuples, together with custom types.

3.1. Native Types

The native types are the built-in types and provide support to a range of constants in Cassandra.

To begin with, a string is a very popular datatype in the programming world.

CQL offers four different datatypes for strings:

Data Type Constants Supported Description
ascii string ASCII character string
inet string IPv4 or IPv6 address string
text string UTF8 encoded string
varchar string UTF8 encoded string

A boolean has one of two possible values, either true or false:

Data Type Constants Supported Description
boolean boolean true or false

Using the blob data type, we can store images or multimedia data as a binary stream in a database:

Data Type Constants Supported Description
blob blob Arbitrary bytes

Duration is a three-signed integer that represents months, days, and nanoseconds:

Data Type Constants Supported Description
duration duration A duration value

Cassandra offers a wide range of data types for integer data:

Data Type Constants Supported Description
tinyint integer 8-bit signed int
smallint integer 16-bit signed int
int integer 32-bit signed int
bigint integer 64-bit signed long
variant integer Arbitrary-precision integer
counter integer Counter column (64-bit signed)

For integer and float, we have three data types:

Data Type Constants Supported Description
decimal integer, float Variable precision decimal
double integer, float 64-bit floating-point
float integer, float 32-bit floating-point

For date- and time-related needs, Cassandra provides three data types:

Data Type Constants Supported Description
date integer, string A date value (without time)
time integer, string A time value (without date)
timestamp integer, string A timestamp (with date & time)

Generally, we have to avoid collision while using the INSERT or UPDATE commands:

Data Type Constants Supported Description
uuid uuid A UUID (any version)
timeuuid uuid A version 1 UUID

3.2. Collection Types

When a user has multiple values against one field in a relational database, it’s common to store them in a separate table. For example, a user has numerous bank accounts, contact information, or email addresses. Therefore, we need to apply joins between two tables to retrieve all the data in this case.

Cassandra provides a way to group and store data together in a column using collection types.

Let’s quickly look at those types:

  • set – unique values; stored as unordered
  • list – can contain duplicate values; order matters
  • map – data stores in the form of key-value pairs

3.3. User-Defined Types

User-defined types give us the liberty to attach multiple data fields in a single column:

CREATE TYPE student.basic_info (
  birthday timestamp,
  race text,
  weight text,
  height text

3.4. Tuple Type

A tuple is an alternative to a user-defined type. It’s created using angle brackets and a comma delimiter to separate the types of elements it contains.

Here are the commands for a simple tuple:

-- create a tuple
CREATE TABLE subjects (
  k int PRIMARY KEY,
  v tuple<int, text, float>

-- insert values
INSERT INTO subjects  (k, v) VALUES(0, (3, 'cs', 2.1));

-- retrieve values
SELECT * FROM subjects;

4. Cassandra CQL Commands

Let’s look at several categories of CQL commands.

4.1. Keyspace Commands

The first thing to remember is that a keyspace in Cassandra is much like a database in RDBMS. It is an outermost container of data that defines the replication strategy and other options, particularly for all the keyspace tables. With this in mind, a good general rule is one keyspace per application.

Let’s look at the related commands:

Command Example Description
CREATE keyspace CREATE KEYSPACE keyspace_name WITH replication = {‘class’:’SimpleStrategy’, ‘replication_factor’ : 2}; To create a keyspace.
DESCRIBE keyspace DESCRIBE KEYSPACES; It will list all the key spaces.
USE keyspace USE keyspace_name; This command connects the client session to a keyspace.
ALTER keyspace ALTER KEYSPACE keyspace_name WITH REPLICATION = { ‘class’ : ‘SimpleStrategy’, ‘replication_factor’ : 3 } AND DURABLE_WRITES = false; To alter a keyspace.
DROP keyspace DROP KEYSPACE keyspace_name; To drop a keyspace.

4.2. Table Commands

In Cassandra, a table is also referred to as a column family. We already know the importance of a primary key. However, it is mandatory to define the primary key while creating the table.

Let’s review these commands:

Command Example Description
CREATE table CREATE TABLE table_name ( column_name UUID PRIMARY KEY, column_name text, column_name text, column_name timestamp); To create a table.
ALTER table ALTER TABLE table_name ADD column_name int; It will add a new column to a table.
ALTER table ALTER TABLE table_name ALTER column_name TYPE datatype; We can change the data type of an existing column.
ALTER table ALTER TABLE table_name WITH caching = {‘keys’ : ‘NONE’, ‘rows_per_partition’ : ‘1’ }; This command helps to alter the properties of a table.
DROP table DROP TABLE table_name; To drop a table.
TRUNCATE table TRUNCATE table_name; Using this, we can remove all the data permanently.

4.3. Index Commands

Instead of scanning a whole table and waiting for results, we can use indexes to speed up queries. However, we must remember that the primary key in Cassandra is already indexed. Therefore, it cannot be used for the same purpose again.

Let’s look at the commands:

Command Example Description
CREATE index CREATE INDEX index_name on table_name (column_name); To create an index.
DELETE index DROP INDEX IF EXISTS index_name; To drop an index.

4.4. Basic Commands

These commands are used to read and manipulate the table values:

Command Example Description
INSERT INSERT INTO table_name (column_name1, column_name2) VALUES(value1, value2); To insert a record in a table.
SELECT SELECT * FROM table_name; The command is used to fetch data from a specific table.
WHERE SELECT * FROM table_name WHERE column_name=value; It filters out records on a predicate.
UPDATE UPDATE table_name SET column_name2=value2 WHERE column_name1=value1; It is used to edit records.
DELETE DELETE identifier FROM table_name WHERE condition; This statement deletes the value from a table.

4.5. Other Commands

Cassandra has two different types of keys: partition key and clustering key. A partition key indicates the node(s) where the data is stored.

In comparison, the clustering key determines the order of data within a partition key:

Command Example Description
ORDER BY SELECT * FROM table_name WHERE column_name1 = value ORDER BY cloumn_name2 ASC; For this, the partition key must be defined in the WHERE clause. Also, the ORDER BY clause represents the clustering column to use for ordering.
GROUP BY SELECT column_name FROM table_name GROUP BY condition1, condition2; This clause only supports with Partition Key or Partition Key and Clustering Key.
LIMIT SELECT * FROM table_name LIMIT 3; For a large table, limit the number of rows retrieved.

5. Operators

Cassandra supports both arithmetic and conditional types of operators. Under the arithmetic operators, we have +, -, *, /, %, and – (unary) for addition, subtraction, multiplication, division, reminder, and negation, respectively.

The WHERE clause is significant in Cassandra. The conditional operators are used in this clause with certain scenarios and limitations. These operators are CONTAINS, CONTAINS KEY, IN, =, >, >=, <, and <=.

6. Common Functions

Without a doubt, functions, either aggregate or scalar, play an essential part in transforming values from one to another. For this reason, Cassandra offers several native functions in both categories.

Let’s look at those functions:

  • Blob conversion functions
  • UUID & Timeuuid functions
  • Token function
  • WRITETIME function
  • TTL function
  • TOKEN function
  • MIN(), MAX(), SUM(), AVG()

Along with these native functions, it also allows users to define the functions and aggregates.

7. Conclusion

In this short article, we’ve seen what the building blocks of Cassandra’s query language are. First, we studied the data types it supports and how to define them. Then, we looked at common commands to perform database operations. Finally, we discussed the operators and functions of the language.

Course – LSD (cat=Persistence)

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:

res – Persistence (eBook) (cat=Persistence)
Comments are open for 30 days after publishing a post. For any issues past this date, use the Contact form on the site.