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 | COMPACT STORAGE ]
[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	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.

DROP COMPACT STORAGE

Use this option only to migrate tables to a DataStax Enterprise version that does not support COMPACT STORAGE. Removes Thrift compatibility mode from the table, which exposes the underlying structure of the Thrift table. See .

Note: Removing Thrift compatibility from a table that also has a search index disables HTTP writes and deletes-by-ID on the search index.

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

ALTER TABLE cycling.teams ALTER ID TYPE uuid;

ALTER TABLE cycling.cyclist_alt_stats 
ALTER birthday TYPE  blob;

ALTER TABLE cycling.cyclist_races 
ADD firstname text;

ALTER TABLE cycling.upcoming_calendar 
ADD events list<text>;

ALTER TABLE cycling.basic_info 
DROP birth_year;

ALTER TABLE cyclist_mv
  WITH comment = 'ID, name, birthdate and country'
     AND read_repair_chance = 0.2;

ALTER TABLE cycling_comments 
WITH compression = { 
   'sstable_compression' : 'DeflateCompressor', 
   'chunk_length_kb' : 64 };

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

cqlsh:cycling> desc table cycling.events ;

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