Apache Cassandra 1.2 Documentation

ALTER TABLE

Modifies the column metadata of a table.

Synopsis

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

property is a CQL table storage property and value, such as caching = 'all'

Synopsis legend

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.

See CQL data types for the available data types and CQL 3 table storage properties for column properties and their default values.

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, use ALTER TABLE and the ALTER and TYPE keywords in the following way:

ALTER TABLE addamsFamily ALTER plot_number TYPE uuid;

The column must already exist in current rows. The bytes stored in values for that column remain unchanged, and if existing data cannot be deserialed according to the new type, your CQL driver or interface might report errors.

These changes to a column type are not allowed:

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>;

The column may or may not already exist in current rows. 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.

Dropping a column

This feature is not ready in Cassandra 1.2 but will be available in a subsequent version.

To drop a column from the table, use ALTER TABLE and the DROP keyword in the following way:

ALTER TABLE addamsFamily DROP gender;

Dropping a column removes the column from current rows.

Renaming a column

The main purpose of the RENAME clause is to change the names of CQL 3-generated row key and column names that are missing from a legacy tables.

Modifying table options

To change the table storage options established during creation of the table, use ALTER TABLE and the WITH keyword. 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;

See CQL 3 table storage properties for the table options you can define.

You cannot modify table options 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 users
  WITH compaction =
  { 'class' : 'SizeTieredCompactionStrategy', 'min_threshold' : 6 };