DbSchema | Cassandra - How to Alter a Table?

Introduction
What does the term “Alter Table” mean?
Prerequisites
Introduction to CQL
CQL Types
Altering a Table in cqlsh and DbSchema
Restrictions on Altering a Table
Conclusion
References

Introduction

Cassandra is a highly scalable and distributed NoSQL database known for its ability to handle large amounts of data across multiple nodes. In Cassandra, tables are a fundamental component used for organizing and storing data. Over time, you may need to modify the structure of your tables to accommodate changing requirements. This article will guide you through the process of altering a table in Cassandra using __cqlsh (Cassandra Query Language shell) and __DbSchema.

What does the term “Alter Table” mean?

In the context of databases, the term "Alter Table" refers to the process of modifying the structure of an existing table. It allows you to add, remove, or modify columns, change data types, alter table properties, and perform other operations to adapt the table schema to new requirements without having to recreate the entire table.

Prerequisites

To follow along with the examples in this article, you will need the following:

__Cassandra` installed on your machine
__cqlsh` (Cassandra Query Language shell) installed
__DbSchema`, a visual database designer and management tool (optional but recommended for a graphical interface)

Introduction to CQL

Cassandra Query Language (CQL) is the language used to interact with the Cassandra database. It is similar to SQL but has some differences and additional features specific to Cassandra. CQL provides a straightforward and expressive way to define and manipulate the schema, insert and retrieve data, and perform various operations on the database.

CQL Types

CQL provides several built-in data types to define the structure of columns in a table. Here are some commonly used CQL types:

CQL Type	Description
__ascii`	ASCII character string
__bigint`	64-bit signed integer
__blob`	Arbitrary bytes
__boolean`	Boolean value (true or false)
__counter`	Distributed counter value
__date`	Date without time
__decimal`	Fixed-precision decimal
__double`	64-bit IEEE-754 floating-point
__float`	32-bit IEEE-754 floating-point
__frozen`	User-defined type that cannot be modified
__timestamp`	Date and time

Altering a Table in cqlsh and DbSchema

Adding Columns

To add a new column to an existing table, you can use the __ALTER TABLE` statement in cqlsh or the graphical interface provided by DbSchema.

In cqlsh:

ALTER TABLE table_name ADD new_column_name data_type;

Replace table_name with the name of your table and new_column_name with the new column name that you want to add in your table.

In DbSchema:
In DbSchema, you can navigate to the table, right-click on the columns area, and select “Add Column” from the context menu.

Dropping Columns

To remove a column from a table, you can use the __ALTER TABLE` statement in cqlsh or the graphical interface in DbSchema.

In cqlsh:

ALTER TABLE table_name DROP column_name;

Replace table_name with the name of your table and column_name with the name of column that you want to drop from your table.

In DbSchema:
In DbSchema, you can right-click on the column you want to drop and select “Drop Column” from the context menu.

Changing Data Types

Cassandra allows you to change the data type of column, but there are some restrictions and considerations. To alter the data type of a column, you can use the __ALTER TABLE` statement in cqlsh or the graphical interface in DbSchema.

In cqlsh:

ALTER TABLE table_name ALTER column_name TYPE new_data_type;

In DbSchema:
In DbSchema, you can right-click on the column and select “Change Data Type” from the context menu to modify the data type.

Modifying Table Properties

Apart from column-related changes, you can also modify other properties of a table, such as the table name, keyspace, or compression options. These modifications are done using the __ALTER TABLE` statement in cqlsh or the relevant options in DbSchema’s graphical interface.

Renaming a Table

To __rename a table, you can use the __ALTER TABLE statement in cqlsh or the renaming functionality in DbSchema.

In cqlsh:

ALTER TABLE old_table_name RENAME TO new_table_name;

In DbSchema:
In DbSchema, you can right-click on the table name and select “Rename Table” to provide the new name.

Understanding Concept of Clustering Column

In Apache Cassandra, a clustering column is a column that defines the order of data within a partition. It is used to determine the physical storage order of rows on disk and the logical sorting order of rows within a partition.

Restrictions on Altering a Table

While Cassandra provides flexibility in altering tables, there are some restrictions to keep in mind:

You cannot remove a clustering column without removing all the corresponding columns that follow it.
You cannot drop or alter a column that is part of the __primary key`.
Changing the type of column may require additional steps if there is existing data, as it needs to be converted to the new type.
Renaming a table requires appropriate permissions and might involve additional considerations, such as ensuring dependent objects are updated.

Alter Tables and Visually Manage Cassandra using DbSchema

DbSchema is a Cassandra client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Create Table >

Alter Table

Start the application and connect to the Cassandra database. Navigate to the table. Right-click on the column area and make alterations like adding columns, dropping columns etc.

Add Columns >

Add Table Columns

Add the columns to the table.

Conclusion

Altering tables is an essential aspect of maintaining a Cassandra database as your requirements evolve. Whether you choose to use cqlsh or a tool like DbSchema, understanding the syntax and available options for altering tables in Cassandra is crucial. By following the guidelines presented in this article, you can confidently make changes to your Cassandra tables while preserving your data and ensuring consistency in your schema.