Cassandra Table

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

Here are the commands we will illustrating for this tutorial.

• Create
• Alter
• Drop
• Truncate

Creating a Table:

The RDBMS table is comparable to the Cassandra column family. Data is stored by the family Column.

In Cassandra, the command 'CreateTable' is used to construct a column family.

Syntax:

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

• Primary key: Primary keys are divided into two categories.

• Single Primary Key: The below syntax denotes a single primary key.

Syntax:

Primary key (ColumnName) 

In the single primary key, there is just one column. Also termed the partition key, this column. Data are partitioned based on this column. Data are distributed to other nodes on the basis of a partition key.

• Compound Primary Key: The below syntax is used to denote the primary compound key.

Syntax:

Primary key(ColumnName1,ColumnName2 . . .)

• C Clustering method sorts the partition data, which are partitioned by using ColumnName1.
• In the above mentioned format, ColumnName1 is the partition key and ColumnName2 is the cluster key.
• ColumnName1 is the foundation for partitioning data and ColumnName2 clustering data.

• Compound Partition key: The compound partition key is specified using the syntax below.

Syntax:

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

• If a single partition contains excess amount of data, then one can implement compound partition key. 
• The compound partition key can be used to split the data into several partitions.
• The above syntax has the compound partition key ColumnName1 and ColumnName2.
• ColumnName1 and ColumnName2 columns will be the foundation for partitioning up of data, and ColumnName3 is the foundation for clustering of the data.

• With Clause:

The 'With' clause is used to define any property and for defined table its value.

For instance, if the Cassandra table data has to be compressed. One may set the compression property in the clause "with" by giving the property value for the compression technique.

Example

Below is a screenshot that demonstrates the command 'command table' i.e, table name 'Employee' in the keyspace 'Firm.

The table 'Employee' will be created with columns id, Name and dept, salary after the successful execution of the command 'Create table' in the key space 'Firm.' The primary key is id. In a single partition, all data is available.

Cassandra Alter table:

The 'Alter table' command is used to create a new column, change the column type, modify the column name, change the column type, drop a column and change the table's properties.

Syntax:

The syntax for the command 'Alter Table' is as follows.

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

Example

Below is a screenshot of the command 'Alter Table,' which will add a new column to the Employee table.

A new column 'Bonus' will be added to the Employee table after a successful execution of the command 'Alter Table,' using the 'int' data type.

Drop Table:

The command 'Drop table' deletes the given table and all of its contents from the keyspace. Cassandra takes a screenshot of the data, but not the schema, as a backup before removing the table.

Syntax:

Drop Table KeyspaceName.TableName

Example

Below is a screenshot of the command 'Drop Table,' which removes table Employee from the keyspace 'Firm.'

The table Employee will be removed from the keyspace Firm when the command 'Drop Table' is successfully executed.

Below is the screenshot showing the Cassandra error that occurred when attempting to access an unexisting employee table.

Truncate Table:

The 'Truncate Table' command is used to delete all data from the given table. Before truncating the data, Cassandra captures an screenshot of it as a backup.

Syntax:

Truncate KeyspaceName.TableName

Example

The Employee table contains two records. Id, name, dept, salary are the attributes in the table.

Below is the screenshot of the command 'Truncate Table' which removes all the information from the Employee Table.

Once the command 'Truncate Table' has successfully been executed, all data is deleted from the Employee table.

Below is the screenshot indicating that the data/records in the Employee table are deleted.

Summary:

The table’s commands in the CQL are very efficient and flexible, yet mostly resemble to the commands in SQL.