ALTER TABLE

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

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

ALTER column_name TYPE cql_type

column_name

Name of column to alter.

cql_type

Change column data type to a compatible type.

Note: Cassandra does not support changing the type of collections (lists, sets and maps) and counters.

Table 2. CQL Data Types

CQL Type	Constants supported	Description
ascii	strings	US-ASCII character string
bigint	integers	64-bit signed long
blob	blobs	Arbitrary bytes (no validation), expressed as hexadecimal
boolean	booleans	true or false
counter	integers	Distributed counter value (64-bit long)
date	strings	Value is a date with no corresponding time value; Cassandra encodes date as a 32-bit integer representing days since epoch (January 1, 1970). Dates can be represented in queries and inserts as a string, such as 2015-05-03 (yyyy-mm-dd)
decimal	integers, floats	Variable-precision decimal Java type Note: When dealing with currency, it is a best practice to have a currency class that serializes to and from an int or use the Decimal form.
double	integers, floats	64-bit IEEE-754 floating point Java type
float	integers, floats	32-bit IEEE-754 floating point Java type
frozen	user-defined types, collections, tuples	A frozen value serializes multiple components into a single value. Non-frozen types allow updates to individual fields. Cassandra treats the value of a frozen type as a blob. The entire value must be overwritten. Note: Cassandra no longer requires the use of frozen for tuples: frozen <tuple <int, tuple<text, double>>>
inet	strings	IP address string in IPv4 or IPv6 format, used by the python-cql driver and CQL native protocols
int	integers	32-bit signed integer
list	n/a	A collection of one or more ordered elements: [literal, literal, literal]. CAUTION: Lists have limitations and specific performance considerations. Use a frozen list to decrease impact. In general, use a set instead of list.
map	n/a	A JSON-style array of literals: { literal : literal, literal : literal ... }
set	n/a	A collection of one or more elements: { literal, literal, literal }
smallint	integers	2 byte integer
text	strings	UTF-8 encoded string
time	strings	Value is encoded as a 64-bit signed integer representing the number of nanoseconds since midnight. Values can be represented as strings, such as 13:30:54.234.
timestamp	integers, strings	Date and time with millisecond precision, encoded as 8 bytes since epoch. Can be represented as a string, such as 2015-05-03 13:30:54.234.
timeuuid	uuids	Version 1 UUID only
tinyint	integers	1 byte integer
tuple	n/a	Cassandra 2.1 and later. A group of 2-3 fields.
uuid	uuids	A UUID in standard UUID format
varchar	strings	UTF-8 encoded string
varint	integers	Arbitrary-precision integer Java type

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 datatype of a column when the column already exists. 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, the CQL driver or interface returns errors.

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

Dropping a column

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. Cassandra 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.

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

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

cqlsh:cycling> 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';