ALTER TABLE

Modifies columns and table properties. Returns no results.

Limitations and warnings

Category Limitations Warnings Recommended approach

Primary key

Cannot add columns to the primary key.

To change the primary key definition, create a new table.

Column names

Cannot rename columns except clustering columns in the primary key.

Cannot rename any columns in materialized view base tables.

Cannot rename any columns in tables with indexes.

Cannot rename STATIC columns.

Renaming columns is severely restricted because SSTables are immutable. Changes to column names would require rewriting all existing SSTables.

Don’t add a column with the same name as a previous column with a different data type. This condition prevents commit log replays and corrupts existing SSTables.

Recycled column names with the same data type are acceptable. However, the database doesn’t restore values written before dropping the column.

For non-primary key columns, drop and re-create the column with the desired name. You must drop the column first because a table cannot have multiple columns with the same name.

If blocked by the presence of another database object, drop the database object, drop and re-create the column, and then re-create the database object.

To change the primary key definition, create a new table.

Data types

Cannot change a column’s data type.

Don’t add a column with the same name as a previous column with a different data type. This condition prevents commit log replays and corrupts existing SSTables.

Drop and re-create the column with a unique name and the desired data type.

Don’t reuse the previous column name.

Materialized views

Cannot drop a column from a table that has a materialized view, even if the column isn’t used in any materialized views.

Drop materialized views, drop the column, and then re-create the materialized views.

Indexes

Cannot rename or drop indexed columns. This includes DSE Search indexes.

Drop the index, rename or drop the column, and then re-create the index.

Case insensitivity

CQL enforces lowercase by default.

Prefer lowercase names when possible. Use double quotes if you need to preserve casing in the names of keyspaces, tables, columns, and specialized data types.

Client-generated timestamps

Don’t drop-and-re-create a column that contains client-generated timestamps.

Don’t recycle names of columns that contained client-generated timestamps, even if the new column also stores client-generated timestamps.

These warnings don’t apply to columns with timestamps generated by the write time facility. It is safe to drop-and-re-create these columns if the new column has the same data type.

Synopsis

ALTER TABLE [<keyspace_name>.]<table_name>
  [ ADD ( <column_definition> | <column_definition_list> ) [ , ... ] ]
  [ DROP <column_name> [ , ... ] ]
  [ RENAME <column_name> TO <column_name> ]
  [ WITH <table_properties> [ , ... ] ];

Legend
Syntax conventions	Description
UPPERCASE	Literal keyword.
Lowercase	Not literal.
`< >`	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.

Options

See Limitations and warnings for information about restricted and problematic ALTER TABLE operations.

ADD ( <column_definition> | <column_definition_list> )

Add one or more columns and set the column data types.

Specify the column names followed by the data types. To add multiple columns, use a comma separated list of columns placed inside parentheses.

<column_name> <cql_type> [ , ]
[ <column_name> <cql_type> [ , ... ] ]

For all existing rows, a new column’s value is automatically set to null.

Column names must be unique within the table.

You cannot add STATIC columns to a table with no clustering columns.

DROP ( <column> | <column_list> )

Drop one or more columns and the data in those columns. To drop multiple columns, use a comma separated list of columns enclosed in parentheses.

DROP COMPACT STORAGE (DSE 5.1 only)

Removes Thrift compatibility mode from a COMPACT STORAGE table, exposing the underlying structure of the Thrift table. Required before upgrading to a DSE version that doesn’t support COMPACT STORAGE. See Migrate from COMPACT STORAGE.

RENAME <column_name> TO <column_name>

Changes the name of a clustering column in the primary key. Preserves the existing values.

WITH table_properties

Modify table properties using the same syntax as the CREATE TABLE command:

Use the appropriate mapping style (plain key-value pair or simple JSON object) for each property.
Enclose the name of each key in single quotes.
If the value is a string, enclose the string in quotes as well.
Use AND to chain multiple table property modifications in a single ALTER TABLE statement.

Some modifications can impact performance temporarily or permanently. For example, changing the compaction strategy requires rewriting all existing SSTAbles.

For more information about available table properties, including subproperties and performance impacts, see CREATE TABLE.

Examples

The following examples illustrate various ALTER TABLE operations.

After you run ALTER TABLE, verify the changes with the CQL shell DESCRIBE TABLE command.

Some modifications can cause errors, such as SSTable corruption. ALTER TABLE doesn’t block all dangerous modifications. See Limitations and warnings for information about restricted and problematic ALTER TABLE operations.

Add a column

Use the ADD instruction to add one or more columns to a table. The ALTER TABLE command creates the column metadata, adds the column to the table schema, and sets the value to null for all existing rows. This operation does not validate the existing data.

Add one non-collection column

ALTER TABLE cycling.cyclist_races
  ADD manager UUID;

ALTER TABLE cycling.cyclist_alt_stats
  ADD cyclist_age int;

Add one collection column

ALTER TABLE cycling.cyclist_races
  ADD completed list<text>;

ALTER TABLE cycling.upcoming_calendar
  ADD description map<text,text>;

Drop a column

The DROP instruction removes the specified column from the table definition. The column becomes unavailable for queries immediately after it is dropped. Data stored in the column is marked with tombstones and dropped during the next compaction.

The column and all of it’s data are deleted. If you need to preserve the data, take action to extract the data before you drop the column.

ALTER TABLE cycling.cyclist_alt_stats
  DROP cyclist_age;

ALTER TABLE cycling.cyclist_races
  DROP manager;

Rename a primary key clustering column

Only primary key clustering columns can be renamed. All other columns cannot be renamed. See Limitations and warnings for more information.

ALTER TABLE cycling.race_times
  RENAME race_date TO date;

ALTER TABLE cycling.cyclist_alt_stats
  RENAME id TO cyclist_id;

Modify compaction

Changing the compaction strategy of a table that contains data causes the database to rewrite all existing SSTables using the new strategy. For large tables, this process can take hours and temporarily increase CPU usage, disk I/O, and disk space consumption, which may affect application performance. Consider making this change during a maintenance window or another period of low activity.

Use a property map to alter a table’s compaction settings.

Set the compaction strategy

In the WITH COMPACTION mapping, the class property specifies the compaction strategy to use.

enabled must be true to use compaction.

ALTER TABLE cycling.comments WITH COMPACTION = {
  'class' : 'SizeTieredCompactionStrategy',
  'enabled' : 'true'
};

Disable background compaction

Disabling background compaction can be harmful. Without compaction, the database cannot reclaim disk space, and zombies can be replicated. Although compaction consumes I/O resources, leave it enabled in almost all cases.

In the WITH COMPACTION mapping, setting the enabled property to false disables background compaction:

ALTER TABLE cycling.comments WITH COMPACTION = {
  'class' : 'SizeTieredCompactionStrategy',
  'enabled' : 'false'
};

Modify compression

Use a property map to alter a table’s compression settings:

ALTER TABLE cycling.comments WITH compression = {
  'sstable_compression' : 'DeflateCompressor',
  'chunk_length_kb' : 64
};

Modify caching

Set the number of rows per partition to store in the row cache for a table to 10 rows:

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

Modify speculative retries

Modify the cyclist_base table to 95th percentile for speculative retry:

ALTER TABLE cycling.cyclist_base
  WITH speculative_retry = '95percentile';

Modify the cyclist_base table to use 10 milliseconds for speculative retry:

ALTER TABLE cycling.cyclist_base
  WITH speculative_retry = '10ms';

Migrate from COMPACT STORAGE

Before upgrading to a newer CQL version that doesn’t support COMPACT STORAGE, remove Thrift compatibility mode from the table using the DROP COMPACT STORAGE option. This option requires at least DSE 5.0.12 or 5.1.6.

ALTER TABLE keyspace_name.table_name
  DROP COMPACT STORAGE;

Don’t alter system.* tables. DSE automatically removes COMPACT STORAGE from these tables when upgrading to a version that doesn’t support COMPACT STORAGE.
Removing Thrift compatibility from a table that has a search index will disable HTTP writes and deletes-by-ID on the search index.

DROP COMPACT STORAGE exposes Thrift-written data that was previously hidden from CQL by changing the table schema according to the following rules:

If the COMPACT STORAGE table has no clustering columns: Add two columns column1 text , value blob that store any data written outside the CQL table schema to the Thrift table. column1 becomes a clustering column. All regular columns become STATIC columns.
If the COMPACT STORAGE table has clustering columns and no regular columns: Add one column value empty.
If the table is a Thrift-created SuperColumn table: Expose a compact value map with an empty name.
If the table is a Thrift-created Compact Table: Migrated column data types correspond to the Thrift definition.

The following examples compare a table schema before and after COMPACT STORAGE migration. Based on the original schema, this migration applies the rules for a COMPACT STORAGE table with no clustering columns, as described in the previous list.

Original COMPACT STORAGE schema Migrated CQL schema after DROP COMPACT STORAGE

Original `COMPACT STORAGE` schema	Migrated CQL schema after `DROP COMPACT STORAGE`
`CREATE TABLE cycling.cyclist_alt_stats ( id UUID PRIMARY KEY, lastname text, birthday date, nationality text, weight float, w_units text, height float, first_race date, last_race date) WITH COMPACT STORAGE;`	`CREATE TABLE cycling.cyclist_alt_stats ( id uuid, column1 text, birthday date static, first_race date static, height float static, last_race date static, lastname text static, nationality text static, value blob, w_units text static, weight float static, PRIMARY KEY (id, column1) ) WITH CLUSTERING ORDER BY (column1 ASC)`

CREATE TABLE cycling.cyclist_alt_stats (
    id UUID PRIMARY KEY,
    lastname text,
    birthday date,
    nationality text,
    weight float,
    w_units text,
    height float,
    first_race date,
    last_race date)
  WITH COMPACT STORAGE;

CREATE TABLE cycling.cyclist_alt_stats (
    id uuid,
    column1 text,
    birthday date static,
    first_race date static,
    height float static,
    last_race date static,
    lastname text static,
    nationality text static,
    value blob,
    w_units text static,
    weight float static,
    PRIMARY KEY (id, column1)
) WITH CLUSTERING ORDER BY (column1 ASC)

ALTER TABLE

Limitations and warnings

Synopsis

Options

Examples

Add a column

Drop a column

Rename a primary key clustering column

Modify compaction

Modify compression

Modify caching

Modify speculative retries

Migrate from COMPACT STORAGE

See also

Was this helpful?

Give Feedback