Cassandra CQL Table: Create, Alter & Truncate Example

The syntax of Cassandra query language (CQL) resembles 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.” The same clause also sets CLUSTERING ORDER BY to reverse the sort direction of a clustering column, and default_time_to_live to expire every row automatically.

Example

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

CREATE TABLE University.Student (
    RollNo int,
    Name text,
    dept text,
    PRIMARY KEY (RollNo)
);

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, so each student occupies a separate single-row 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
    ADD ColumnName ColumnDataType;

ALTER TABLE KeyspaceName.TableName
    DROP ColumnName;

ALTER TABLE KeyspaceName.TableName
    RENAME ColumnName TO NewColumnName;

ALTER TABLE KeyspaceName.TableName
    WITH PropertyName = PropertyValue;

Each form is issued as a separate statement. Two restrictions apply in current Cassandra releases: the data type of an existing column can no longer be changed, and RENAME works only on a clustering column of the primary key.

Example

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

ALTER TABLE University.Student ADD Semester int;

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 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 IF EXISTS KeyspaceName.TableName;

Example

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

DROP TABLE University.Student;

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 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.

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

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.

Truncate requires every node to be reachable, because it is executed across the whole cluster at once. If any node is down the statement fails, which is a deliberate safeguard against leaving partial data behind.

Drop vs Truncate vs Delete in Cassandra

Three commands remove data, and choosing the wrong one either destroys a schema or leaves a performance problem behind.

The practical rule is to prefer TRUNCATE over a bulk DELETE when clearing an entire table, because thousands of tombstones will slow every subsequent read of that partition. Row-level removal is covered in the CQL insert, update and delete tutorial.

FAQs