ALTER TABLE

Modify the column metadata of a table.

You can also use the alias ALTER COLUMNFAMILY.

Synopsis 

ALTER TABLE keyspace_name. table_name instruction;

Parameters

 
( TYPE   cql_type
| ADD column_name cql_type 
| DROP column_name 
|  RENAME column_name TO column_name
|  WITH property [ AND property ] . . . } ) 

cql_type is compatible with the original type and is a CQL type, not a collection or counter. Exception: You cannot change the type of an existing column to a map or collection, but when you ADD a new column, you can define it as a map or collection, If the table is a counter, you can ADD a column of type counter.

property is a CQL table property and value, such as speculative_retry = '10ms'. Enclose the value for a string property in single quotation marks.

Description 

You can use ALTER TABLE to manipulate the table metadata. Do this to change the datatype of a columns, add new columns, drop existing columns, and change table properties. The command returns no results.

Start the command with the keywords ALTER TABLE, followed by the table name, followed by the instruction: ALTER. ADD, DROP, RENAME, or WITH. See the following sections for the information each instruction requires.

You can qualify table name by prepending its keyspace. For example, to specify the teams table in the cycling keyspace:

ALTER TABLE cycling.teams ALTER ID TYPE uuid;

Changing the type of a column 

CREATE TABLE users (
  user_name varchar PRIMARY KEY, 
  bio ascii,
  );
ALTER TABLE users ALTER bio TYPE text;
ALTER TABLE users ALTER bio TYPE blob;

You can only change the datatype of a column if the column already exists in current rows. When a column's datatype changes, the bytes stored in values for that column remain unchanged. If existing data cannot be deserialized to conform to the new datatype, your CQL driver or interface returns errors.

The following datatype changes are not supported:

• Changing the type of a clustering column
• Changing the type of a column on which an index is defined

Altering the type of a column after inserting data can confuse CQL drivers/tools if the new type is incompatible with the data just inserted.

Adding a column 

To add a column (other than a column of a collection type) to a table, use ALTER TABLE and the ADD instruction as follows:

ALTER TABLE cycling.cyclist_races ADD firstname text;

To add a column of a collection type:

ALTER TABLE cycling.upcoming_calendar ADD events list<text>;

This operation does not validate the existing data.

You cannot use the ADD command to add:

• A column with the same name as an existing column
• A static column if the table has no clustering columns or uses COMPACT STORAGE.

Dropping a column 

ALTER TABLE cycling.basic_info DROP birth_year;

ALTER DROP removes the column from the table definition and marks the column values with tombstones. The column becomes unavailable for queries immediately after it is dropped. Apache Cassandra™ drops the column data during the next compaction. The column data is not included in SSTables in the future. To force the removal of dropped columns before compaction occurs, use ALTER TABLE to update the metadata, then run nodetool upgradesstables to put the drop into effect.

Renaming a column 

Modifying table properties 

This example uses the WITH instruction to modify the read_repair_chance property, which configures read repair for tables that use for a non-quorum consistency.

This example also shows how to change multiple properties using AND:

ALTER TABLE cyclist_mv
  WITH comment = 'ID, name, birthdate and country'
  AND read_repair_chance = 0.2;

Enclose a text property value in single quotation marks. You cannot modify properties of a table that uses compact storage.

Modifying compression and compaction 

Use a property map to alter a table's compression or compaction setting.

ALTER TABLE cycling_comments WITH compression =
  { 'sstable_compression' : 'DeflateCompressor', 'chunk_length_kb' : 64 };

ALTER TABLE mykeyspace.mytable 
  WITH compaction = {'class': 'SizeTieredCompactionStrategy', 'cold_reads_to_omit': 0.05};

To change the values of the caching property: for example, change the keys option from ALL, the default, to NONE and change the rows_per_partition to 15.

Changing caching 

Create and change the caching options using a property map.

ALTER TABLE users WITH caching = { 'keys' : 'NONE', 'rows_per_partition' : '15' };

Next, change just the rows_per_partition to 25.

//Cassandra 2.1 only
        
ALTER TABLE users WITH caching = { 'rows_per_partition' : '25' };

Finally, use DESCRIBE to view the table definition.

//Cassandra 2.1 only

DESCRIBE TABLE users;
      
CREATE TABLE mykeyspace.users (
    user_name text PRIMARY KEY,
    bio blob
) WITH bloom_filter_fp_chance = 0.01
    AND caching = '{"keys":"NONE", "rows_per_partition":"25"}'
    AND comment = ''
    AND compaction = {'min_threshold': '4', 'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32'}
    AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    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.1
    AND speculative_retry = '99.0PERCENTILE';