Feedback
3.3
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 [ ... ] ]
| OPEN
| CLOSE
| RENAME TO table_ident
| REROUTE reroute_option
}
where column_constraint
is:
{ PRIMARY KEY |
NOT NULL |
INDEX { OFF | USING { PLAIN |
FULLTEXT [ WITH ( analyzer = analyzer_name ) ] }
}
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 the 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 this clause can be used to alter only a single partition.
Note
BLOB tables cannot be partitioned and hence this clause cannot be used.
This clause identifies a single partition. It takes one or more partition columns with a value each to identify the partition to alter.
[ PARTITION ( partition_column = value [ , ... ] ) ]
- partition_column
The name of the column by which the table is partitioned.
All partition columns that were part of the PARTITIONED BY of the CREATE TABLE statement must be specified.
- value
The columns value.
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 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.
- data_type
Data type of the column which should be added.
- column_name
Name of the column which should be added.
OPEN/CLOSE
¶
Can be used to open or close the table, respectively. Closing a table prevents
all operations, except ALTER TABLE ... OPEN
, to fail. Operations on closed
partitions will not produce an exception, but will have no effect. Similarly,
like SELECT
and INSERT
on partitioned will exclude closed partitions and
continue working.
RENAME TO
¶
Can be used to rename a table, while maintaining its schema and data. During this operation the shards of the table will 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 rowcount 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_id TO node_id
| ALLOCATE REPLICA SHARD shard_id ON node_id
| CANCEL SHARD shard_id ON node_id [ 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_id
The node ID within the cluster.
See Nodes how to gain the unique ID.
REROUTE
suports 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_id
for the node to move the shard from andTO node_id
to move the shard to.- ALLOCATE REPLICA
Allows to force allocation of an unassigned replica shard on a specific node.
- CANCEL
This cancels the allocation/recovery of a
shard_id
of atable_ident
on a givennode_id
. Theallow_primary
flag indicates if it is allowed to cancel the allocation of a primary shard.