ALTER TABLE

Modify the column metadata of a 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

instruction is:
 
( 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.

Table 1. Legend
  • Uppercase means literal
  • Lowercase means not literal
  • Italics mean optional
  • The pipe (|) symbol means OR or AND/OR
  • Ellipsis (...) means repeatable

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

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

You can only change the datatype of a column when the original datatype of the column is compatible with the type you are changing to. Examples:
  • You can change a column of type ascii to type text
  • You cannot change a textor varchar column to type ascii because not every UTF8 string is type ascii.
  • You can convert a text column to type blob
  • You cannot change a blob column to type text because not every blob is a UTF string.
More examples: change this 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 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:

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

To remove (drop) a column from the table, use ALTER TABLE and the DROP instruction.
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.

More tips about dropping columns:
  • If you drop a column then re-add it, Cassandra does not restore the values written before the column was dropped. A subsequent SELECT on this column does not return the dropped data.

  • Do not re-add a dropped column to a table if it contains timestamps that are generated by the client, not by Cassandra's write time facility.

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

Renaming a column

The main purpose of RENAME is to change the names of CQL-generated primary key and column names that are missing from a legacy table. The following restrictions apply to the RENAME operation:
  • You can only rename clustering columns, which are part of the primary key.
  • You cannot rename the partition key.
  • You can index a renamed column.
  • You cannot rename a column if an index has been created on it.
  • You cannot rename a static column (since you cannot use a static column in the table's primary key).

Modifying table properties

To change the table storage properties set when the table was created, use one of the following formats:
  • Use ALTER TABLE and a WITH instruction that introduces the property name and value
  • Use ALTER TABLE WITH and a property map, as shown in the next section on compression and compaction.

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.

CAUTION: If you change the compaction strategy of a table with existing data, Cassandra rewrites all existing SSTables according to the new strategy. This can take hours, which can be a major problem for a production system. For strategies to minimize this disruption, see How to change Cassandra compaction strategy on a production cluster and Impact of Changing Compaction Strategy.

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