ALTER TABLE command, you can change the composition of columns and additional table parameters. You can specify several actions in one command. In general, the
ALTER TABLE command looks like this:
ALTER TABLE table_name action1, action2, ..., actionN;
action: Any action to change the table described below.
Changing the composition of columns
YDB lets you add columns to a table and delete non-key columns from it.
ADD COLUMN: Adds a column with the specified name and type. The code below adds the
is_deleted column with the
Bool data type to the
ALTER TABLE episodes ADD COLUMN is_deleted Bool;
DROP COLUMN: Deletes the column with the specified name. The code below removes the
is_deleted column from the
ALTER TABLE episodes DROP column is_deleted;
Adding or removing a secondary index
ADD INDEX: Adds an index with the specified name and type for a given set of columns. The code below adds a global index named
title_index for the
ALTER TABLE `series` ADD INDEX `title_index` GLOBAL ON (`title`);
You can specify any index parameters from the
CREATE TABLE command.
Deleting an index:
DROP INDEX: Deletes the index with the specified name. The code below deletes the index named
ALTER TABLE `series` DROP INDEX `title_index`;
You can also add or remove a secondary index using the YDB CLI table index command.
Adding and deleting a changefeed
ADD CHANGEFEED <name> WITH (option = value[, ...]) adds a changefeed with the specified name and parameters.
MODE: Operation mode. Specifies what exactly is to be written to a changefeed each time the table data is altered.
KEYS_ONLY: Only the primary key components and change flag are written.
UPDATES: Updated column values that result from updates are written.
NEW_IMAGE: Any column values resulting from updates are written.
OLD_IMAGE: Any column values before updates are written.
NEW_AND_OLD_IMAGES: A combination of
OLD_IMAGEmodes. Any column values prior to and resulting from updates are written.
FORMAT: Data write format.
JSON: The record structure is given on the changefeed description page.
The code below adds a changefeed named
updates_feed where the values of updated table columns will be exported in JSON format:
ALTER TABLE `series` ADD CHANGEFEED `updates_feed` WITH ( FORMAT = 'JSON', MODE = 'UPDATES' );
DROP CHANGEFEED: Deletes the changefeed with the specified name. The code below deletes the
ALTER TABLE `series` DROP CHANGEFEED `updates_feed`;
Renaming a table
ALTER TABLE old_table_name RENAME TO new_table_name;
If a table with a new name exists, an error is returned. The possibility of transactional table substitution under load is supported by ad-hoc CLI and SDK methods.
If a YQL query contains multiple
ALTER TABLE ... RENAME TO ... commands, each of them will be executed in autocommit mode as a separate transaction. From the external process viewpoint, the tables will be renamed sequentially one by one. To rename multiple tables within a single transaction, use ad-hoc methods available in the CLI and SDK.
Renaming can be used to move a table from one directory inside the database to another, for example:
ALTER TABLE `table1` RENAME TO `/backup/table1`;
Changing column groups
ADD FAMILY: Creates a new group of columns in the table. The code below creates the
family_small column group in the
ALTER TABLE series_with_families ADD FAMILY family_small ( DATA = "ssd", COMPRESSION = "off" );
ALTER COLUMN command, you can change a column group for the specified column. The code below for the
release_date column in the
series_with_families table changes the column group to
ALTER TABLE series_with_families ALTER COLUMN release_date SET FAMILY family_small;
The two previous commands from listings 8 and 9 can be combined into one
ALTER TABLE call. The code below creates the
family_small column group and sets it for the
release_date column in the
ALTER TABLE series_with_families ADD FAMILY family_small ( DATA = "ssd", COMPRESSION = "off" ), ALTER COLUMN release_date SET FAMILY family_small;
ALTER FAMILY command, you can change the parameters of the column group. The code below changes the storage type to
hdd for the
default column group in the
ALTER TABLE series_with_families ALTER FAMILY default SET DATA "hdd";
Available types of storage devices depend on the YDB cluster configuration.
You can specify any parameters of a group of columns from the
CREATE TABLE command.
Changing additional table parameters
Most of the table parameters in YDB specified on the table description page can be changed with the
In general, the command to change any table parameter looks like this:
ALTER TABLE table_name SET (key = value);
key is a parameter name and
value is its new value.
For example, this command disables automatic partitioning of the table:
ALTER TABLE series SET (AUTO_PARTITIONING_BY_SIZE = DISABLED);
Resetting additional table parameters
Some table parameters in YDB listed on the table description page can be reset with the
The command to reset the table parameter looks like this:
ALTER TABLE table_name RESET (key);
key: Name of the parameter.
For example, this command resets (deletes) TTL settings for the table:
ALTER TABLE series RESET (TTL);