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.