ALTER TABLE

Add new columns, drop existing columns, renames columns, and modify table or graph properties. The command returns no results.

Restriction:

Can only rename clustering columns in the primary key.
Cannot change the data type of a column.
For a table that has a materialized view, cannot drop a column from the table even if the column is not used in the materialized view.
Cannot rename or drop columns that have dependent secondary indexes or Datastax Enterprise Search indexes.
Do not add a column with the same name as an existing column but with a different data type. It will prevent commit log replays and corrupt existing SSTables with old data.

ALTER COLUMNFAMILY is deprecated.

Synopsis

ALTER TABLE [<keyspace_name>.]<table_name>
  [ ADD ( <column_definition> | <column_definition_list> ) [ , ... ] ]
  [ DROP <column_name> [ , ... ] ]
  [ [ RENAME <column_name> TO <column_name> ] |
    [ RENAME ( VERTEX LABEL | EDGE LABEL ) TO <new_name> ] ]
  [ WITH <table_properties> [ , ... ] ]
  [ WITH ( VERTEX LABEL | EDGE LABEL ) <current_name> ]
  [ WITHOUT ( VERTEX LABEL | EDGE LABEL ) <current_name> ];

Syntax legend

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

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. The column value is automatically set to null. To add multiple columns, use a comma separated list of columns placed inside parentheses.

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

Adding columns to a primary key is not supported after a table has been created.

DROP ( <column> | <column_list> )

Drop one or more columns. The values contained in the row are also dropped and not recoverable. To drop multiple columns, use a comma separated list of columns placed inside parentheses.

DROP COMPACT STORAGE (DSE 5.1 only)

Available in DSE 5.1.6 and later, use this option only to migrate tables to a DataStax Enterprise (DSE) version that does not support COMPACT STORAGE. Removes Thrift compatibility mode from the table, which exposes the underlying structure of the Thrift table.

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 primary key column and preserves the existing values.

Not supported on materialized view base-tables, or tables with secondary indexes or Datastax Enterprise Search indexes.

RENAME ( VERTEX LABEL | EDGE LABEL ) TO <new_name>

Changes the name of a vertex label or edge label associated with the table.

Not supported on materialized view base-tables, or tables with secondary indexes or Datastax Enterprise Search indexes.

WITH ( VERTEX LABEL | EDGE LABEL ) <current_name>

Alter a table and associate a vertex label or edge label it.

Not supported on materialized view base-tables, or tables with secondary indexes or Datastax Enterprise Search indexes.

WITHOUT ( VERTEX LABEL | EDGE LABEL ) <current_name>

Removes a vertex label or edge label associated with the table.

Not supported on materialized view base-tables, or tables with secondary indexes or Datastax Enterprise Search indexes.

table_properties

You can modify an existing table’s properties. Some properties are single options that are set to a value:

<option_name> = <value> [ AND ... ]

For example, speculative_retry = '10ms'. Enclose the value for a string property in single quotation marks.

Other table properties are set using a JSON map: option_name = { <subproperty_name> : <value> [ , ... ] }

See table_options for more details.

Examples

#This section uses the cyclist_races table.

Adding a column

To add a column, use the ADD instruction:

ALTER TABLE cycling.cyclist_races 
  ADD manager UUID;

To add a column of a collection type:

ALTER TABLE cycling.cyclist_races 
ADD completed 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.

Dropping a column

To remove a column from the table, use the DROP instruction:

ALTER TABLE cycling.cyclist_races 
DROP manager;

DROP removes the column from the table definition. The column becomes unavailable for queries immediately after it is dropped. The database drops the column data during the next compaction.

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.

Renaming a column

ALTER TABLE cycling.race_times RENAME race_date TO date;

The following restrictions apply to RENAME:

You can only rename clustering columns, which are part of the primary key.
You cannot rename the partition key because the partition key determines the data storage location on a node. If a different partition name is required, the table must be recreated and the data migrated.

There are many restrictions when using RENAME because SSTables are immutable. To change the state of the data on disk, everything must be rewritten.

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.

Alter a table to associate a vertex label or an edge label with it

To associate a vertex label with the person table:

ALTER TABLE food_cql_conversion.person WITH VERTEX LABEL "person";

To associate an edge label with the person_authored_book table:

ALTER TABLE food_cql_conversion.person_authored_book 
  WITH EDGE LABEL "authored"
  FROM person(person_name, person_id)
  TO book(book_name, book_id);

Renaming a vertex label or an edge label

To rename a vertex label in the person table:

ALTER TABLE food.person RENAME VERTEX LABEL TO "personX";

To rename an edge label in the person_authored_book table:

ALTER TABLE food."person_authored_book" RENAME EDGE LABEL TO "authoredX";

Removing a vertex label or an edge label

To remove a vertex label in the person table:

ALTER TABLE food.person WITHOUT VERTEX LABEL "personX";

To remove an edge label in the person_authored_book table:

ALTER TABLE food."person_authored_book" WITHOUT EDGE LABEL "authoredX";

Modifying table properties

To change an existing table’s properties, use ALTER TABLE and WITH. You can specify a:

Single property name and value.
Property map to set the names and values, as shown in the next section on compression and compaction.

For example, to add a comment to a table using WITH:

ALTER TABLE cycling.cyclist_base
  WITH comment = 'basic cyclist information';

Enclose a text property value in single quotation marks.

Modifying compression and compaction

Use a property map to alter a table’s compression or compaction setting:

ALTER TABLE cycling.cyclist_base
  WITH comment = 'basic cyclist information';

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

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

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

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

Enabling and disabling background compaction

The following example sets the enabled property to false to disable background compaction:

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

Disabling background compaction can be harmful: without it, the database does not regain disk space, and could allow zombies to propagate. Although compaction uses I/O, it is better to leave it enabled in most cases.

Reading extended compaction logs

Set the log_all subproperty to true to collect in-depth information about compaction activity on a node in a dedicated log file.

If you enable extended compaction logging for any table on any node, it is enabled for all tables on all nodes in the cluster.

When extended compaction is enabled, the database creates a file named compaction-%d.log (where %d is a sequential number) in home/logs.

The compaction logging service logs detailed information about the following types of compaction events:

type:enable

Lists SSTables that have been flushed previously.

{"type":"enable","keyspace":"test","table":"t","time":1470071098866,"strategies":
  [    {"strategyId":"0","type":"LeveledCompactionStrategy","tables":[],"repaired":true,"folders":
      ["/home/carl/oss/cassandra/bin/../data/data"]},
    {"strategyId":"1","type":"LeveledCompactionStrategy","tables":[],"repaired":false,"folders":
      ["/home/carl/oss/cassandra/bin/../data/data"]
    }
 ]
}

type: flush

Logs a flush event from a memtable to an SSTable on disk, including the CompactionStrategy for each table.

{"type":"flush","keyspace":"test","table":"t","time":1470083335639,"tables":
  [    {"strategyId":"1","table":
      {"generation":1,"version":"mb","size":106846362,"details":
        {"level":0,"min_token":"-9221834874718566760","max_token":"9221396997139245178"}
      }
    }
 ]
}

type: compaction

Logs a compaction event.

{"type":"compaction","keyspace":"test","table":"t","time":1470083660267,
 "start":"1470083660188","end":"1470083660267","input":
  [    {"strategyId":"1","table":
      {"generation":1372,"version":"mb","size":1064979,"details":
        {"level":1,"min_token":"7199305267944662291","max_token":"7323434447996777057"}
      }
    }
 ],"output":
  [    {"strategyId":"1","table":
      {"generation":1404,"version":"mb","size":1064306,"details":
        {"level":2,"min_token":"7199305267944662291","max_token":"7323434447996777057"}
      }
    }
 ]
}

type: pending

Lists the number of pending tasks for a compaction strategy.

{"type":"pending","keyspace":"test","table":"t",
 "time":1470083447967,"strategyId":"1","pending":200}

Reviewing the table definition

Use DESCRIBE or DESC to view the table definition.

DESCRIBE TABLE cycling.comments;

Results

The table details including the column names are returned.

CREATE TABLE cycling.comments (
    id uuid,
    created_at timestamp,
    comment text,
    commenter text,
    record_id timeuuid,
    PRIMARY KEY (id, created_at)
) WITH CLUSTERING ORDER BY (created_at DESC)
    AND additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    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 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 nodesync = {'enabled': 'true', 'incremental': 'true'}
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

Migrating from legacy COMPACT STORAGE tables (5.1 only)

Before upgrading to a newer CQL version that does not support COMPACT STORAGE, remove Thrift compatibility mode from the table. Migrate to the CQL-compatible format using the ALTER TABLE DROP COMPACT STORAGE option.

DROP COMPACT STORAGE only works if the cluster is at least DSE 5.0.12 or 5.1.6. Also, do not migrate system.* tables, DSE automatically removes COMPACT STORAGE from these tables.

Migration changes the CQL table schema to expose any Thrift written data that was previously hidden from CQL according to the following rules:

COMPACT STORAGE table that has no clustering columns:
- Two new columns column1 text and value blob are added. These columns contain any data written outside the CQL table schema to the Thrift table.
- column1 becomes a clustering column.
- All regular columns become static columns.
COMPACT STORAGE table with one or more clustering columns that has no regular columns:
- Column named value with type empty is added.
Thrift-created SuperColumn table exposes a compact value map with an empty name.
Thrift-created Compact Tables column data types correspond to the Thrift definition.

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

Use the following syntax to change the table storage type:

ALTER TABLE keyspace_name.table_name
DROP COMPACT STORAGE;

ALTER TABLE

Synopsis

Examples

Adding a column

Dropping a column

Renaming a column

Alter a table to associate a vertex label or an edge label with it

Renaming a vertex label or an edge label

Removing a vertex label or an edge label

Modifying table properties

Modifying compression and compaction

Changing caching

Change the speculative retries

Enabling and disabling background compaction

Reading extended compaction logs

Reviewing the table definition

Migrating from legacy COMPACT STORAGE tables (5.1 only)

Was this helpful?

Give Feedback