Article Overview

In this tutorial, we'll learn the essential DDL concepts of Cassandra query language (CQL) and how to use them in practical world. If you are not at all familiar with Cassandra, recommend you to read the article, Cassandra Springboot Integration, before progressing further in this tutorial. The article in the link would help you understand the fundamental concepts of Cassandra and how to install Apache Cassandra in different environments.

Pre-requisites for this tutorial

• Basic concepts of Cassandra
• Fundamental knowledge of SQL
• Apache Cassandra software

Cassandra Query Language (CQL) commands

A few CQL commands that you need to know.

Keyspace

The Keyspace in Cassandra is similar to a database in RDBMS. It is an outermost container of data that defines the replication strategy and other attributes especially for all the Keyspace tables.

CREATE Keyspace

The following command is used to create a new Keyspace. The 'replication_factor' denotes number of instances of the Keyspace in a node cluster. However if its a standalone server and replication_factor is still provided in the query as shown below in the example, a warning message is thrown by the system.

The basis syntax is:

CREATE KEYSPACE keyspace_name
WITH replication = {‘class':'replication_strategy', ‘replication_factor' : n};

The replication property is mandatory and must contain the 'class' sub-option that defines the desired replication strategy class. SimpleStrategy and NetworkTopologyStrategy are the commonly used ones. While SimpleStrategy defines a replication factor for data to be spread across the entire cluster and is usually not an automatic choice for production use, NetworkTopologyStrategy is a production-ready replication strategy that sets the replication factor independently for each data-center.

Example:

CREATE KEYSPACE demo_keyspace
WITH replication = {'class':'SimpleStrategy', 'replication_factor' : 3};

DESCRIBE Keyspaces

In CQL it is possible list all Keyspaces as well as to describe a specific Keyspace. Let us have a look at the both the scenarios below.

This is the command to list all Keyspaces within a cluster.

Describe keyspaces;

The following command describes a single Keyspace.

Describe keyspace_name;

ALTER Keyspace

The ALTER keyword is used to modify the definition of an existing keyspace. As seen in the previous example, by default the 'durable_writes' property is 'true'. We will change it to 'false'.

Basic Syntax:

ALTER KEYSPACE keyspace_name
WITH REPLICATION = { ‘class' : replication_strategy_class,
‘replication_factor' : n } AND DURABLE_WRITES = boolean_value;

Example

ALTER KEYSPACE demo_keyspace
WITH REPLICATION = { 'class' : 'SimpleStrategy',
'replication_factor' : 3 } AND DURABLE_WRITES = false;

USE Keyspace

This command connects the client session to the given Keyspace.

DROP Keyspace

The following command is used to drop an existing Keyspace.

Basic Syntax

DROP KEYSPACE keyspace_name;

'demo_keypsace' is dropped and verified using the following commands.

TABLE

A Table in Cassandra resides within the Keyspace. Table comprises of several rows with each row uniquely identifiable from the other by a primary key. Attempting to create an already existing table will return an error unless the 'IF NOT EXISTS' clause is used

CREATE table

This is the basic syntax to create a new Cassandra table.

create_table_statement::= CREATE TABLE [ IF NOT EXISTS ] table_name '('
column_definition  ( ',' column_definition )*
[ ',' PRIMARY KEY '(' primary_key ')' ]
 ')' [ WITH table_options ]

The table_options may define properties like 'COMPACT STORAGE', 'CLUSTERING ORDER'.

Example:

CREATE TABLE library (
    bookid uuid,
    published_month int,
    genre text,
    author text,
    PRIMARY KEY (bookid, author)
) WITH compaction = { 'class' : 'LeveledCompactionStrategy' };

Describe Tables

Similar to Keyspace, it is possible either to list all tables within a Keyspace or describe one particular table. The following command list all tables within a Keyspace

Describe tables;

The following command describes one specific table. The table is defined along with all its properties and default index.

describe table_name;

ALTER table

You can alter a table using the command ALTER TABLE. Using ALTER command, the following operations can be performed −

• Add a column
• Drop a column

Basic Syntax

ALTER (TABLE | COLUMNFAMILY) <tablename> <instruction>

Example 1 - Adding a column

Let us add a new column named bookname to the table created in the previous step and verify as shown below.

ALTER TABLE library
ADD bookname text;

Example 2 - Dropping a column

Given below is the syntax to delete a column from a table using ALTER command. Let us delete the column bookname added in the previous step and verify as shown below

ALTER table library
DROP bookname;

DROP Table

The syntax to drop a table from a Keyspace is as follows:

DROP TABLE table_name

Example

Let us drop the table we created earlier in this tutorial and verify as shown below.

DROP TABLE library;

Conclusion

This article helps to understand the very basic DDL statements of Cassandra Query Language(CQL). We learn how to create and work with a Keyspace and a Table. Hope this article will help you to get started on your journey with Cassandra Query Language(CQL).