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.