Cassandra Table Example: Create, Alter, Drop & Truncate Table

The syntax of Cassandra query language (CQL) resembles with SQL language.

How to Create Table in Cassandra

Column family in Cassandra is similar to RDBMS table. Column family is used to store data.

Command ‘Create Table’ is used to create column family in Cassandra.

Syntax

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

1. Primary key: There are two types of primary key.

  • Single Primary Key: Single primary key is specified by the following syntax.

Syntax

Primary key (ColumnName)

In the single primary key, there is only a single column. That column is also called partitioning key. Data is partitioned on the basis of that column. Data is spread on different nodes on the basis of the partition key.

2. Compound Primary Key: Compound primary key is specified by the following syntax.

Syntax

Primary key(ColumnName1,ColumnName2 . . .)

In above syntax, ColumnName1 is the partitioning key and ColumnName2 is the Clustering key. Data will be partitioned on the basis of ColumnName1 and data will be clustered on the basis of ColumnName2. Clustering is the process that sorts data in the partition.

3. Compound Partitioning key: Compound partitioning key is specified by the following syntax.

Syntax

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

In above syntax, ColumnName1 and ColumnName2 are the compound partition key. Data will be partitioned on the basis of both columns ColumnName1 and ColumnName2 and data will be clustered on the basis of the ColumnName3. If you have too much data on the single partition. Then, compound partitioning key is used. Compound partitioning key is used to create multiple partitions for the data.

  • With Clause

“With clause” is used to specify any property and its value for the defined table. For example, if you want to compress Cassandra table data. You can set compression property by specifying compression algorithm property value in “With clause.”

Example

Here is the execution of the command ‘Create table’ that will create table name ‘Student’ in the keyspace ‘University.’

Cassandra Create Table

After successful execution of the command ‘Create table’, table ‘Student’ will be created in the keyspace ‘University’ with columns RollNo, Name and dept. RollNo is the primary key. RollNo is also a partition key. All the data will be in the single partition.

Cassandra Alter table

Command ‘Alter Table’ is used to drop column, add a new column, alter column name, alter column type and change the property of the table.

Syntax

Following is the syntax of command ‘Alter Table.’

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

Example

Here is the snapshot of the command ‘Alter Table’ that will add new column in the table Student.

Cassandra Alter table

After successful execution of the command ‘Alter Table’, a new column ‘Semester’ with ‘int’ data type will be added to the table Student.

Here is the screenshot that shows the updated Student table.

Cassandra Alter table

Cassandra Drop Table

Command ‘Drop table’ drops specified table including all the data from the keyspace. Before dropping the table, Cassandra takes a snapshot of the data not the schema as a backup.

Syntax

Drop Table KeyspaceName.TableName

Example

Here is the snapshot of the executed command ‘Drop Table’ that will drop table Student from the keyspace ‘University’.

Cassandra Drop Table

After successful execution of the command ‘Drop Table’, table Student will be dropped from the keyspace University.

Here is the snapshot that shows the error returned by the Cassandra when tried to access Student table that does not exist.

Cassandra Drop Table

Cassandra Truncate Table

Command ‘Truncate table’ removes all the data from the specified table. Before truncating the data, Cassandra takes the snapshot of the data as a backup.

Syntax

Truncate KeyspaceName.TableName

Example

There are three records in the table Student. These are the records in the table.

Cassandra Truncate Table

Here is the snapshot of the executed command ‘Truncate table’ that will remove all the data from the table Student.

Cassandra Truncate Table

After successful execution of the command ‘Truncate Table’, all the data will be removed from the table Student.

Here is the snapshot of the database state where there are no records in the table Student.

Cassandra Truncate Table