ALTER TABLE
¶
Alter an existing table.
Table of contents
Synopsis¶
ALTER [ BLOB ] TABLE { ONLY table_ident
| table_ident [ PARTITION (partition_column = value [ , ... ]) ] }
{ SET ( parameter = value [ , ... ] )
| RESET ( parameter [ , ... ] )
| { ADD [ COLUMN ] column_name data_type [ column_constraint [ ... ] ] } [, ... ]
| { DROP [ COLUMN ] [ IF EXISTS ] column_name } [, ... ]
| { RENAME [ COLUMN ] column_name TO new_name } [, ... ]
| OPEN
| CLOSE
| RENAME TO table_ident
| REROUTE reroute_option
| DROP CONSTRAINT constraint_name
}
where column_constraint
is:
{ PRIMARY KEY |
NULL |
NOT NULL |
INDEX { OFF | USING { PLAIN |
FULLTEXT [ WITH ( analyzer = analyzer_name ) ] } |
[ CONSTRAINT constraint_name ] CHECK (boolean_expression)
}
Description¶
ALTER TABLE
can be used to modify an existing table definition. It provides
options to add columns, modify constraints, enabling or disabling table
parameters and allows to execute a shard reroute allocation.
Use the BLOB
keyword in order to alter a blob table (see
Blobs). Blob tables cannot have custom columns which means that
the ADD COLUMN
keyword won’t work.
While altering a partitioned table, using ONLY
will apply changes for the
table only and not for any possible existing partitions. So these changes
will only be applied to new partitions. The ONLY
keyword cannot be used
together with a PARTITION clause.
See CREATE TABLE
WITH for a list of available
parameters.
- table_ident:
The name (optionally schema-qualified) of the table to alter.
Clauses¶
PARTITION
¶
If the table is partitioned, the optional
PARTITION
clause can be used to alter one partition exclusively.
[ PARTITION ( partition_column = value [ , ... ] ) ]
- partition_column:
One of the column names used for table partitioning.
- value:
The respective column value.
All partition columns (specified by the
PARTITIONED BY clause) must be listed inside the
parentheses along with their respective values using the partition_column =
value
syntax (separated by commas).
Because each partition corresponds to a unique set of partition column row values, this clause uniquely identifies a single partition to alter.
Tip
The SHOW CREATE TABLE statement will show you the complete list of partition columns specified by the PARTITIONED BY clause.
Note
BLOB tables cannot be partitioned and hence this clause cannot be used.
See also
Arguments¶
SET/RESET
¶
Can be used to change a table parameter to a different value. Using RESET
will reset the parameter to its default value.
- parameter:
The name of the parameter that is set to a new value or its default.
The supported parameters are listed in the CREATE TABLE WITH CLAUSE documentation. In addition to those, for dynamically
changing the number of allocated shards, the
parameter number_of_shards
can be used. For more info on that, see
Changing the number of shards.
ADD COLUMN
¶
Can be used to add an additional column to a table. While columns can be added
at any time, adding a new generated column is only possible if the table is empty.
In addition, adding a base column with Default clause
is not supported. It is possible to define a CHECK
constraint with the
restriction that only the column being added may be used in the boolean
expression.
- data_type:
Data type of the column which should be added.
- column_name:
Name of the column which should be added. This can be a sub-column on an existing OBJECT.
It’s possible to add multiple columns at once.
DROP COLUMN
¶
Can be used to drop a column from a table.
- column_name:
Name of the column which should be dropped. This can be a sub-column of an OBJECT.
It’s possible to drop multiple columns at once.
Note
It’s not allowed to drop a column:
which is a system column
which is part of a PRIMARY KEY
used in CLUSTERED BY column
used in PARTITIONED BY
is a named index column
used in an named index
is referenced in a generated column
is referenced in a table level constraint with other columns
Note
It’s not allowed to drop all columns of a table.
Note
Dropping columns of a table created before version 5.5 is not supported.
RENAME COLUMN
¶
Renames a column of a table
- column_name:
Name of the column to rename. Supports subscript expressions to rename sub-columns of
OBJECT
columns.- new_name:
The new name of the column.
Note
Renaming columns of a table created before version 5.5 is not supported.
OPEN/CLOSE
¶
Can be used to open or close the table.
Closing a table means that all operations, except ALTER TABLE ...
, will
fail. Operations that fail will not return an error, but they will have no
effect. Operations on tables containing closed partitions won’t fail, but those
operations will exclude all closed partitions.
RENAME TO
¶
Can be used to rename a table or view, while maintaining its schema and data. If renaming a table, the shards of it become temporarily unavailable.
REROUTE
¶
The REROUTE
command provides various options to manually control the
allocation of shards. It allows the enforcement
of explicit allocations, cancellations and the moving of shards between nodes
in a cluster. See Reroute shards to get the convenient use-cases.
The row count defines if the reroute or allocation process of a shard was acknowledged or rejected.
Note
Partitioned tables require a PARTITION clause in
order to specify a unique shard_id
.
[ REROUTE reroute_option]
where reroute_option
is:
{ MOVE SHARD shard_id FROM node TO node
| ALLOCATE REPLICA SHARD shard_id ON node
| PROMOTE REPLICA SHARD shard_id ON node [ WITH (accept_data_loss = { TRUE | FALSE }) ]
| CANCEL SHARD shard_id ON node [ WITH (allow_primary = {TRUE|FALSE}) ]
}
- shard_id:
The shard ID. Ranges from 0 up to the specified number of Shards shards of a table.
- node:
The ID or name of a node within the cluster.
See Nodes how to gain the unique ID.
REROUTE
supports the following options to start/stop shard allocation:
- MOVE
A started shard gets moved from one node to another. It requests a
table_ident
and ashard_id
to identify the shard that receives the new allocation. SpecifyFROM node
for the node to move the shard from andTO node
to move the shard to.- ALLOCATE REPLICA
Allows to force allocation of an unassigned replica shard on a specific node.
- PROMOTE REPLICA Force promote a stale replica shard to a primary. In case
a node holding a primary copy of a shard had a failure and the replica shards are out of sync, the system won’t promote the replica to primary automatically, as it would result in a silent data loss.
Ideally the node holding the primary copy of the shard would be brought back into the cluster, but if that is not possible due to a permanent system failure, it is possible to accept the potential data loss and force promote a stale replica using this command.
The parameter
accept_data_loss
needs to be set totrue
in order for this command to work. If it is not provided or set to false, the command will error out.- CANCEL
This cancels the allocation or recovery of a
shard_id
of atable_ident
on a givennode
. Theallow_primary
flag indicates if it is allowed to cancel the allocation of a primary shard.
DROP CONSTRAINT
¶
Removes a CHECK constraint from a table.
ALTER TABLE table_ident DROP CONSTRAINT check_name
- table_ident:
The name (optionally schema-qualified) of the table.
- check_name:
The name of the check constraint to be removed.
Warning
A removed CHECK constraints cannot be re-added to a table once dropped.