ALTER TABLE

Modifies the columns and properties of a table.

Changes the data type of a column, add new columns, drop existing columns, renames columns, and change table properties. The command returns no results.

Restriction: Altering PRIMARY KEY columns is not supported. Altering columns in a table that has a materialized view is not supported.
Note: ALTER COLUMNFAMILY is deprecated.

Synopsis

ALTER TABLE [keyspace_name.]table_name 
[ALTER column_name TYPE cql_type]
[ADD (column_definition_list)]
[DROP (column_list)]
[RENAME column_name TO column_name]
[WITH table_properties];
Legend
Syntax conventions Description
UPPERCASE Literal keyword.
Lowercase Not literal.
Italics Variable value. Replace with a user-defined value.
[] Optional. Square brackets ( [] ) surround optional command arguments. Do not type the square brackets.
( ) Group. Parentheses ( ( ) ) identify a group to choose from. Do not type the parentheses.
| Or. A vertical bar ( | ) separates alternative elements. Type any one of the elements. Do not type the vertical bar.
... Repeatable. An ellipsis ( ... ) indicates that you can repeat the syntax element as often as required.
'Literal string' Single quotation ( ' ) marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.
{ key : value } Map collection. Braces ( { } ) enclose map collections or key value pairs. A colon separates the key and the value.
<datatype1,datatype2> Set, list, map, or tuple. Angle brackets ( < > ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.
cql_statement; End CQL statement. A semicolon ( ; ) terminates all CQL statements.
[--] Separate the command line options from the command arguments with two hyphens ( -- ). This syntax is useful when arguments might be mistaken for command line options.
' <schema> ... </schema> ' Search CQL only: Single quotation marks ( ' ) surround an entire XML schema declaration.
@xml_entity='xml_entity_type' Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.
ALTER column_name TYPE cql_type

column_name: Name of column to alter.

cql_type: Change column data type to a compatible type.

Note: Changing the type of collections (lists, sets and maps) and counters is not supported.
CQL data types have strict requirements for conversion compatibility. The following table shows the allowed alterations for data types:
Data type compatibility
From type To type
ascii, bigint, boolean, decimal, double, float, inet, int, timestamp, timeuuid, uuid, varchar, varint blob
int varint
text varchar
timeuuid uuid
varchar text
Clustering columns have even stricter requirements, because clustering columns mandate the order in which data is written to disk. The following table shows the allow alterations for data types used in clustering columns:
Cluster column type compatibility
From type To type
varint int
varchar text
text varchar
ADD (column_definition_list)
Add one or more columns and set the data type, enter the name followed by the data types. The value is automatically set to null. To add multiple columns, use a comma separated list.
column_name cql_type [,]
[column_name cql_type [, ...]
Restriction: Adding PRIMARY KEYs is not supported once a table has been created.
DROP (column_list)
Comma-separated list of columns to drop. The values contained in the row are also dropped and not recoverable.
RENAME column_name TO column_name
Changes the name of a column and preserves the existing values.
table_properties
After a table has been created, you can modify the properties. There are two types of properties, a single option that is set equal to a value:
option_name = value [AND ...]
For example, speculative_retry = '10ms'. Enclose the value for a string property in single quotation marks.

Some table properties are defined as a map in simple JSON format:option_name = { subproperty_name : value [, ...]}

See table_options for more details.

Examples

Specifying the table and keyspace

You can qualify the table name by prepending the name of 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

Change the column data type to a compatible type.

Change the birthday timestamp column to type blob.

ALTER TABLE cycling.cyclist_alt_stats 
ALTER birthday TYPE  blob;

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

Warning:

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 the ADD instruction:

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 instruction to add:

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

Dropping a column

To remove a column from the table, use the DROP instruction:
ALTER TABLE cycling.basic_info 
DROP birth_year;

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. The database drops the column data during the next compaction. To force the removal of dropped columns before compaction occurs, use ALTER TABLE to update the metadata, and then run nodetool upgradesstables to put the drop into effect.

Restriction:
  • If you drop a column then re-add it, DataStax Enterprise does not restore the values written before the column was dropped.
  • Do not re-add a dropped column that contained timestamps generated by a client; you can re-add columns with timestamps generated by the 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:

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

Enclose the name of each key in single quotes. If the value is a string, enclose this in quotes as well.

CAUTION:
If you change the compaction strategy of a table with existing data, the database rewrites all existing SSTables using 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 the compaction strategy on a production cluster and Impact of Changing Compaction Strategy.

Changing caching

Create and change the caching options using a property map to optimize queries that return 10.

ALTER TABLE cycling.events 
   WITH caching = {
    'keys': 'NONE', 
    'rows_per_partition': 10 };

Reviewing the table definition

Use DESCRIBE to view the table definition.
DESC TABLE cycling.events;

The details including the column names is returned.

CREATE TABLE cycling.events (
    month int,
    end timestamp,
    class text,
    title text,
    location text,
    start timestamp,
    type text,
    PRIMARY KEY (month, end, class, title)
) WITH CLUSTERING ORDER BY (end ASC, class ASC, title ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'NONE', 'rows_per_partition': '10'}
    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': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    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';