Cassandra Create Table, Alter, Drop And Truncate

Before going to Cassandra create table, Alter, Drop and Truncate you will have the understanding of Basic terminologies of Cassandra, like

What is Cluster in Cassandra?

What is Node in Cassandra?

What is Keyspace in Cassandra?

 The syntax of Cassandra query language (CQL) is almost similar to SQL language.

In this Tutorial You will learn,

1. Cassandra create table
2. Cassandra Alter table
3. Cassandra drop table
4. Cassandra truncate table

What is Cluster in Cassandra?

Database in Cassandra is distributed to multiple machines connected via network and operate together. This is the basic concept of cluster in  Cassandra. Cassandra creates data replication of each node on some other node of the cluster. Thus Cassandra provides no single of failure. In case of failure of any node, its replica takes charge. Nodes are arranged in ring type format. And Cassandra distributes data to different node by itself. 

What is Keyspace in Cassandra?

Keyspace in Cassandra is like “database” in Relational database Management systems(RDBMS). The basic attributes of Keyspace are given below. While creating keyspace (database) you have to give these attributes. Keyspace contains column families (like table in RDBMS).

1. Replication  factor for keyspace. It reflects the number of nodes on which you want to create the copy of data or how many time you want to replicate your data in Cassandra cluster. The consistency is as high as more replication factor. But a trade-off with memory.                 

2. Replica placement strategy. It is about selecting your strategy to place replicas in the Cassandra ring. There are three types of strategies.

Simple Strategy(Rack aware strategy)

Old network topology strategy(Rack aware strategy)

Network topology strategy(data center shared strategy)

Cassandra Create Table

Before going to create a table in Cassandra, first you have to know about what is column family in Cassandra.

Column family in Cassandra

As Keyspace in Cassandra is like database in RDBMS. Same the column family in Cassandra has similarity with table in RDBMS. A keyspace contains one to many column families. A column family is a table so;it contains multiple columns and rows. Each row contains a new record. Basically Column families show the structure of data. Each keyspace contains at least one and commonly many column families.

Syntax Cassandra create table

Create table KeyspaceName.TableName
(
ColumnName DataType,
ColumnName DataType,
ColumnName DataType
.
.
.
Primary key(ColumnName)
) with PropertyName=PropertyValue;


How to select a primary Key in Cassandra create table Query?

There are two type of primary keys in Cassandra as in RDBMS.

Single Value Primary Key

Single Primary key contains only a single column value. This column value is also the partitioning key.  Cassandra does the data distribution on different nodes in cluster based on this partitioning key (Primary key).

Syntax for single value Primary key

Primary key (ColumnName)

Compound Primary key

Syntax for Compound Primary key

Primary Key ((ColumnName1, ColumnName2), ColumnName3...))

With Clause in Cassandra

“With clause” in Cassandra is mainly used to set some property for some specific table while Cassandra create table. For example, if you want to compress Cassandra table data (the table you are creating). You have to set the compression property while creating table in Cassandra. It can be specified by setting compression algorithm property value with “With clause.”

Practical Scenario Example Cassandra Create Table.

Let’s take a practical scenario of Cassandra create table. Suppose we have keyspace (database) name University. And we want to create a table Named student in this keyspace. Then below is piece of query  where you go.

When you successfully execute the above mentioned create table query on you CQL shell. A table named student is created in keyspace University. The table has three columns that is StudentId, StudentName and Degree. The primary key in this table is StudentId.

Cassandra Alter Table

Cassandra alter table command is used to alter the already created table. The following operations can be performed on table in Cassandra using this command.

1. Add a new column in table
2. Delete a column from table
3. Change Column Name
4. Change the column data type
5. Change table properties

Syntax for Cassandra Alter Table

Alter table KeyspaceName.TableName +
Alter ColumnName TYPE ColumnDataType |
Add ColumnName ColumnDataType |
Drop ColumnName |
Rename ColumnName To NewColumnName |
With PropertyName=PropertyValue


Practical Scenario Example Cassandra Alter Table

Let’s take the Practical scenario example of Cassandra Alter table. We have to alter the table Student that we have already created using Cassandra create table command. And we want to add a new column name semester and type int. Then below is piece of query where you go.

After you successfully execute the above mentioned Alter table query on you CQL shell, a column named semester of type integer is added in table Student.

Cassandra Drop Table

Cassandra drop table command is used to drop the specified table along with its data from specified keyspace. When you execute this command Cassandra first take the snapshot of table’s data for Backup purpose. And then delete the table and table’s data. Cassandra did not take the snapshot of table’s Schema.

Syntax for Cassandra Drop Table

Drop Table KeyspaceName.TableName

Practical Scenario Example Cassandra Drop Table

Let’s take the Practical scenario example of Cassandra Drop table. By using this command you can drop the table Student in Keyspace University.

After you successfully execute the above mentioned Cassandra drop table query on you CQL shell, a table named Student dropped from keyspace named University.  

Cassandra Truncate Table

Cassandra truncate table command is used to remove all data from specified table from specified keyspace. When you execute this command Cassandra first take the snapshot of table’s data for backup purpose. And then delete the table and table’s data. Schema is not deleted in truncate command.

Syntax for Cassandra Truncate Table

Truncate KeyspaceName.TableName

Practical Scenario Example Cassandra Truncate Table

Let’s take the Practical scenario example of Cassandra Truncate table. By using this command you can truncate the table Student in KeyspaceUniversity.

After you successfully execute the above mentioned Cassandra Truncate table query on you CQL shell, All data from table named Student from keyspace named University is deleted. 

Read Also:

Cassandra Data Model 

Cassandra Architecture 

Cassandra step by step tutorial 2019

To Download Cassandra setup. Click here

Summary

This tutorial briefly explains the procedure of Cassandra create table, Cassandra alter table, Cassandra drop table and Cassandra truncate table.