How to rename Column in Cassandra - Cassandra / CQL Tutorial

How to rename Column in Cassandra

Often we get in situation where we have to rename the column name after creating the table.  We can use ALTER Table statement in Cassandra to rename the column. 


CQLSH:techbrotherstutorials>ALTER TABLE tablename rename current_columnname TO newcolumnname;

There are some restrictions apply which columns we can rename or can not rename.We can rename the columns which are part of Primary key. I tried to rename other columns but got error. Let's check by performing an example.


Create employee table by using below script.

CQLSH:techbrotherstutorials>CREATE TABLE employee 
                          employeeid UUID , 
                          fname TEXT, 
                          lname TEXT, 
                          phone TEXT, 
                          age TINYINT, 
                          PRIMARY KEY((employeeid,fname),lname) 
             WITH clustering 
ORDER BY     ( 
                          lname DESC );


Now we would like to rename Employeeid to ID, FName to FirstName and LName to LastName. Notice that all the columns are part of Primary key so we should not have any problem renaming them.

ALTER TABLE employee rename employeeid TO id;


CQLSH:techbrotherstutorials>ALTER TABLE employee rename fname TO firstname;


 CQLSH:techbrotherstutorials>ALTER TABLE employee rename lname TO lastname;


You can verify the changes by Select all the column from table or use Describe command.

 CQLSH:techbrotherstutorials> describe TABLE employee;



cqlsh:techbrotherstutorials> CREATE TABLE techbrotherstutorials.employee (
                         ...     id uuid,
                         ...     fastname text,
                         ...     lastname text,
                         ...     age tinyint,
                         ...     phone text,
                         ...     PRIMARY KEY ((id, fastname), lastname)
                         ... ) WITH CLUSTERING ORDER BY (lastname DESC)
                         ...     AND bloom_filter_fp_chance = 0.01
                         ...     AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
                         ...     AND comment = ''
                         ...     AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
                         ...     AND compression = {'chunk_length_in_kb': '64', 'class': ''}
                         ...     AND crc_check_chance = 1.0
                         ...     AND dclocal_read_repair_chance = 0.1
                         ...     AND default_time_to_live = 0
                         ...     AND gc_grace_seconds = 864000
                         ...     AND max_index_interval = 2048
                         ...     AND memtable_flush_period_in_ms = 0
                         ...     AND min_index_interval = 128
                         ...     AND read_repair_chance = 0.0
                         ...     AND speculative_retry = '99PERCENTILE';


If we will try to rename any non Primary key column, we will get error. Let's give a try to Rename Age to EmpAge.

CQLSH:techbrotherstutorials>ALTER TABLE employee rename age TO empage;

 Error output 

InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot rename non PRIMARY KEY part age"

1 comment: