Apache Cassandra: CQL Commands

In previous two blogs of Apache Cassandra series, we have already explained the Basics of Apache Cassandra and How Cassandra Reads and Writes. Now here in this blog we will cover another important topic in Apache Cassandra i.e., CQL commands. So let us name this blog as “Apache Cassandra: CQL commands“. We recommend to go through the other two blogs of this series before diving into “Apache Cassandra: CQL commands” for better understanding. In this blog we will learn what CQL is, Partition key and clustering column in Cassandra, Datatypes used in CQL along with hands on some CQL commands.

What is CQL ?

CQL stands for Cassandra Query Language. These are basically computer languages which we use to make queries in databases. For communicating with the Apache Cassandra database, we use Cassandra Query Language, a primary query language. In this blog, we’ll learn about various commands that we generally use for performing operations on the Apache Cassandra database. To start with CQL commands, firstly we need to know the concept of primary key and clustering column.

Primary Key and Clustering Column.

A primary key is a column or a set of columns in a table whose values uniquely identify a row in the table. In Apache Cassandra Primary Key can be compound that means it may be generated from more columns. In this case the first part of primary key is called partition key and the second part is called clustering column.

Primary Key = Partition key + clustering column.

Partition key is responsible for data distribution across the nodes.

Clustering Key is responsible for data sorting within the partition.

Example : –

create table Cassandra_blog(
      column_1 text,
      column_2 int,
      column_3 text,
      PRIMARY KEY(column_1,column_2)      
  );

In above example column_1 is partition key and column_2 is clustering column.

Suppose we have a table structure in which we have more than one partition key then we will define it as –

create table Cassandra_blog(
      column_1 text,
      column_2 int,
      column_3 int,
      column_4 text,
      column_5 text,
      PRIMARY KEY ((column_1,column_2),column_3,column_4)      
  );

Here in this table column_1 and column_2 are partition keys and column_3 and column_4 are clustering columns.

Basic Data types in Apache Cassandra.

Cassandra provides a set of data types. Apart from these built in data types users can also create their own custom data types.

Some of them are –

CQL Type	Constants	Description
ascii	string	It represents US-ASCII character string
int	integers	It represents 32 bit signed integers.
text	strings	It represents UTF8 encoded string
varchar	strings	It represents UTF8 encoded string
timestamp	integers, strings	It represents a timestamp
timeuuid	uuids	It represents type 1 UUID
uuid	uuids	It represents type 1 or type 4 UUID
list	n/a	It represents collection of one or more ordered elements
map	n/a	A map is a collection of key-value pairs.
set	n/a	A set is a collection of one or more elements.

Queries in Apache Cassandra

Here in this section of this blog we will deep dive into writing the queries in Cassandra using CQL (Cassandra query language).

We can interact with Cassandra using command-line interface by using the command ./cqlsh from the bin directory.

1. DESC command

We are using DESC command before table or keyspaces to display list of all tables and keyspaces in the cluster.

2. CREATE command

Before creating a table we have to create a keyspace ( A keyspace in Cassandra is a namespace that defines data replication on nodes ) and then inside the keyspace we can create a table.

Command to create a keyspace –

CREATE KEYSPACE Knoldus_blog
WITH replication = {'class':'SimpleStrategy', 'replication_factor' : 1};

use Knoldus_blog;

Then to create table we will use this command –

CREATE TABLE Knoldus_blog.Blog1 (
line int,
topic text,
agenda text,
views int,
PRIMARY KEY ((line, topic), views) );

3. INSERT command

This command is used to insert the data in a row.

INSERT INTO Knoldus_blog.Blog1 (line, topic, agenda, views) 
   VALUES (2015, 'Apache Cassandra', 'basics', 500);

4. SELECT & UPDATE command

Select command returns one or more rows from a single Cassandra table. If used without a where clause, it returns complete data.

Update command is used to change the existing values in table. While using update command we must have to include all columns that are declared as a primary key when we created the table.

Update Knoldus_blog.Blog1
SET "agenda"='high' WHERE line=60 AND topic='Java' AND views=10;

As you can see into the above screenshot, In update command we must have to include all the primary keys ( line, topic, views ) otherwise it will throw error and the values will not be updated.

5. Delete command

You can delete data from a table using DELETE command. Here also we have to include all the primary keys in “where” clause.

DELETE agenda FROM Knoldus_blog.Blog1
WHERE line=60 AND topic='Java' AND views=10;

Limitations in Cassandra Query Language

There are some features which CQL does not supports , they are –

• Cassandra Query Language does not support group by, having queries.
• CQL does not support aggregation like min, max, sum, avg.
• It does not support OR queries and joins.
• CQL does not support Union, Intersection queries.

Thanks for going through this blog , hope this blog is helpful for you.