CQL for Cassandra 2.0

ALTER TABLE

Modify the column metadata of a table.

Synopsis

ALTER TABLE keyspace_name.table_name instruction
instruction is:
   ALTER column_name 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, other than a collection or counter. Exceptions: ADD supports a collection type and also, if the table is a counter, a counter type.

property is a CQL table property and value, such as caching = 'all'. Enclose a string property in single quotation marks.

Synopsis legend

  • Uppercase means literal
  • Lowercase means not literal
  • Italics mean optional
  • The pipe (|) symbol means OR or AND/OR
  • Ellipsis (...) means repeatable
  • Orange ( and ) means not literal, indicates scope

A semicolon that terminates CQL statements is not included in the synopsis.

Description

ALTER TABLE manipulates the table metadata. You can change the data storage type of columns, add new columns, drop existing columns, and change table properties. No results are returned. You can also use the alias ALTER COLUMNFAMILY.

First, specify the name of the table to be changed after the ALTER TABLE keywords, followed by the type of change: ALTER, ADD, DROP, RENAME, or WITH. Next, provide the rest of the needed information, as explained in the following sections.

You can qualify table names by keyspace. For example, to alter the addamsFamily table in the monsters keyspace:

ALTER TABLE monsters.addamsFamily ALTER lastKnownLocation TYPE uuid;

Changing the type of a column

To change the storage type for a column, the type you are changing to and from must be compatible. For example, change the type of the bio column in the users table from ascii to text, and then from text to blob.

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

Altering the type of a column after inserting data can confuse CQL drivers/tools if the new type is incompatible with the data. The bytes stored in values for that column remain unchanged, and if existing data cannot be deserialized according to the new type, your CQL driver or interface might report errors.

These changes to a column type are not allowed:

The column, in this example bio, must already exist in current rows.

Adding a column

To add a column, other than a column of a collection type, to a table, use ALTER TABLE and the ADD keyword in the following way:

ALTER TABLE addamsFamily ADD gravesite varchar;

To add a column of the collection type:

ALTER TABLE users ADD top_places list<text>;

No validation of existing data occurs.

These additions to a table are not allowed:

  • Adding a column having the same name as an existing column.
  • Adding columns to tables defined with COMPACT STORAGE.
  • A static column

Dropping a column

To drop a column from the table, use ALTER TABLE and the DROP keyword. Dropping a column removes the column from the table.
ALTER TABLE addamsFamily DROP gender;

ALTER DROP removes the column from the table definition, removes data pertaining to that column, and eventually reclaims the space formerly used by the column. The column is unavailable for querying immediately after being dropped. The actual data removal occurs during compaction; data is not included in SSTables in the future. To force the removal of dropped columns before compaction occurs, use the upgradesstables utility followed by an ALTER TABLE statement, which updates the table metadata to register the drop.

After re-adding a dropped column, a query does not return values written before the column was last dropped. Do not re-add dropped columns to tables using client-supplied timestamps, which are not Cassandra-generated write time.

You cannot drop columns from tables defined with the @COMPACT STORAGE@ option.

Renaming a column

The main purpose of the RENAME clause is to change the names of CQL-generated primary key and column names that are missing from a legacy table. Primary key columns can be renamed. You cannot rename an indexed column or a static column.

Modifying table properties

To change the table storage properties established during creation of the table, use ALTER TABLE and a WITH directive specifying the property and value.

In Cassandra 2.0 and later, you can modify the index_interval, which determines the sample size used in the partition summary, using ALTER TABLE.

ALTER TABLE addamsFamily
WITH index_interval = 512;

You can probably increase the index_interval to 512 without seeing degradation. Subsequently written SStables written use the new index_interval.

To change multiple properties, use AND as shown in this example:

ALTER TABLE addamsFamily
  WITH comment = 'A most excellent and useful table'
  AND read_repair_chance = 0.2;

The CQL table properties list the properties you can define. Enclose a property in single quotation marks. You cannot modify properties of a table having compact storage.

Modifying the compression or compaction setting

Changing any compaction or compression option erases all previous compaction or compression settings.
ALTER TABLE addamsFamily
  WITH compression =
  { 'sstable_compression' : 'DeflateCompressor', 'chunk_length_kb' : 64 };

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