Cassandra Alter Table

In Cassandra, tables are the basic unit of data organization, and they are created using the CREATE TABLE statement. Once a table is created, it can be modified using the ALTER TABLE statement. In this tutorial, we will discuss how to use the ALTER TABLE statement in Cassandra.

Syntax:

The syntax for the ALTER TABLE statement in Cassandra is as follows:

ALTER TABLE [keyspace_name.]table_name 
    ADD column_name datatype 
    [column_modifier] 
    [column_modifier] 
    [...];

ALTER TABLE [keyspace_name.]table_name 
    ALTER column_name datatype 
    [column_modifier] 
    [column_modifier] 
    [...];

ALTER TABLE [keyspace_name.]table_name 
    DROP column_name;

The ALTER TABLE statement can be used to add, modify or drop a column from an existing table. Let’s discuss each of these operations in detail.

Adding a Column:

To add a new column to an existing table in Cassandra, we can use the ADD keyword followed by the column name and datatype. We can also include column modifiers like PRIMARY KEY, CLUSTERING ORDER, and STATIC to define the column’s behavior.

For example, let’s add a new column “email” of datatype text to the “users” table in the DeveloperPublish keyspace:

ALTER TABLE DeveloperPublish.users 
    ADD email text;

We can also add a new column with a clustering order like this:

ALTER TABLE DeveloperPublish.users 
    ADD age int 
    CLUSTERING ORDER BY (age DESC);

Modifying a Column:

To modify an existing column in a table, we can use the ALTER keyword followed by the column name and datatype. We can also include column modifiers like RENAME and TYPE to modify the column’s behavior.

For example, let’s modify the “email” column of the “users” table in the DeveloperPublish keyspace to be of datatype varchar:

ALTER TABLE DeveloperPublish.users 
    ALTER email TYPE varchar;

We can also rename a column like this:

ALTER TABLE DeveloperPublish.users 
    RENAME email TO user_email;

Dropping a Column:

To drop a column from an existing table in Cassandra, we can use the DROP keyword followed by the column name.

For example, let’s drop the “email” column from the “users” table in the DeveloperPublish keyspace:

ALTER TABLE DeveloperPublish.users 
    DROP email;

Conclusion:

In this tutorial, we have discussed how to use the ALTER TABLE statement in Cassandra to add, modify, or drop columns from an existing table. It is important to note that altering a table can have an impact on performance and should be done with caution. It is always a good practice to take a backup of the data before making any structural changes to a table.