Alter a table
There are a number of alterations that can be made to tables after they have been created. Any non-partition key columns can be added, renamed, or deleted, using any CQL data type. In addition, the table properties can be modified.
Alter a table column
Use the ALTER TABLE command to add new columns, drop non-primary key columns, or rename a primary key column.
|
To change the table settings, see Altering table properties. |
Add a column
Add an age column of type int to the table cycling.cyclist_alt_stats.
ALTER TABLE cycling.cyclist_alt_stats ADD cyclist_age int;The ALTER TABLE command creates the column metadata, adds the column to the table schema, and sets the value to null for all rows.
Verify that a column is added
Verify that the column was added with null values.
SELECT id, cyclist_age AS age FROM cycling.cyclist_alt_stats LIMIT 3;Results
id | age
--------------------------------------+------
e0953617-07eb-4c82-8f91-3b2757981625 | null
1ba0417d-62da-4103-b710-de6fb222db6f | 34
4ceb495c-55ab-4f71-83b9-81117252bf13 | 34
(3 rows)
id | age
--------------------------------------+------
e0953617-07eb-4c82-8f91-3b2757981625 | null
1ba0417d-62da-4103-b710-de6fb222db6f | 34
4ceb495c-55ab-4f71-83b9-81117252bf13 | 34
(3 rows)The result set shows the first three rows.
Rename a column
Rename the id primary key column to cyclist_id.
ALTER TABLE cycling.cyclist_alt_stats RENAME id TO cyclist_id;Restriction: Only primary key columns can be renamed.
Add a collection column
Alter the table cycling.upcoming_calendar to add a map named description to store a name and description for each race.
ALTER TABLE cycling.upcoming_calendar
ADD description map<text,text>;Verify the collection column
Verify the results:
SELECT * FROM cycling.upcoming_calendar WHERE year = 2015 AND month = 6;Results
Map type columns display in a JSON format.
year | month | description | events
------+-------+------------------------------------------------------------------------------+----------------------------------------------------------------------------
2015 | 6 | {'Criterium du Dauphine': 'Easy race', 'Tour du Suisse': 'Hard uphill race'} | ['Tour de France', 'Criterium du Dauphine', 'Vuelta Ciclista a Venezuela']
(1 rows)
year | month | description | events
------+-------+------------------------------------------------------------------------------+----------------------------------------------------------------------------
2015 | 6 | {'Criterium du Dauphine': 'Easy race', 'Tour du Suisse': 'Hard uphill race'} | ['Tour de France', 'Criterium du Dauphine', 'Vuelta Ciclista a Venezuela']
(1 rows)
year | month | description | events
------+-------+------------------------------------------------------------------------------+----------------------------------------------------------------------------
2015 | 6 | {'Criterium du Dauphine': 'Easy race', 'Tour du Suisse': 'Hard uphill race'} | ['Tour de France', 'Criterium du Dauphine', 'Vuelta Ciclista a Venezuela']
(1 rows)See also:
Altering table properties
Use ALTER TABLE to add or change table properties.
Alter the caching property
Alter a table to change the caching property.
See Enabling caching globally for more details.
ALTER TABLE cycling.comments WITH caching = {
'keys' : 'NONE',
'rows_per_partition' : 10
};Results
See also:
Migrating from compact storage (for 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.
Restriction: 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 textandvalue blobare added. These columns contain any data written outside the CQL table schema to the Thrift table. -
column1becomes 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
emptyis 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.
Restriction: Removing Thrift compatibility from a table that also has a search index disables HTTP writes and deletes-by-ID on the search index.
|
Do not migrate system.* tables, DSE automatically removes COMPACT STORAGE from these tables. |
Use the following syntax to change the table storage type:
ALTER TABLE keyspace_name.table_name
DROP COMPACT STORAGE;Example
Simple partition key table
The following 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)See also:
