Alter a table
Alter table properties
Use ALTER TABLE
to add or change table properties.
This example alters a table by changing the caching
property:
ALTER TABLE cycling.comments WITH caching = {
'keys' : 'NONE',
'rows_per_partition' : 10
};
Migrate from compact storage (DSE 5.1 only)
Before upgrading to a version of DataStax Enterprise 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.
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
andvalue 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.
|
Use the following syntax to change the table storage type:
ALTER TABLE keyspace_name.table_name
DROP COMPACT STORAGE;
The following example demonstrates this migration with a simple partition key table. The table has only a single primary key column:
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;
Migrate to a standard CQL table:
ALTER TABLE cycling.cyclist_alt_stats
DROP COMPACT STORAGE;
Show the updated table schema:
DESC TABLE cycling.cyclist_alt_stats ;
Two columns were added, column1
and value
.
column1
was added to the PRIMARY KEY as a clustering column.
And all the regular columns are changed to static columns.
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)