Create Table Alter Table Drop Table Truncate Table
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;
- 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.’
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.
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 KeyspaceName.TableName
Example
Here is the snapshot of the executed command ‘Drop Table’ that will drop table Student from the keyspace ‘University’.
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.
