Skip to main content
Version: nightly

ALTER

ALTER can be used to modify any database options, table options or metadata of the table, including:

  • Modify database options
  • Add/Drop/Modify a column
  • Rename a table
  • Modify table options

ALTER DATABASE​

ALTER DATABASE statements can be used to modify the options of databases.

Syntax​

ALTER DATABASE db
   [SET <option_name>=<option_value> [, ...]
    | UNSET <option_name> [, ...]
   ]

Currently following options are supported:

  • ttl: the default retention time of data in database.

Examples​

Modify default retention time of data in database​

Change the default retention time of data in the database to 1 day:

ALTER DATABASE db SET 'ttl'='1d';

Remove the default retention time of data in the database:

ALTER DATABASE db UNSET 'ttl';

ALTER TABLE​

Syntax​

ALTER TABLE [db.]table
   [ADD COLUMN name type [options]
    | DROP COLUMN name
    | MODIFY COLUMN name type
    | MODIFY COLUMN name SET FULLTEXT [WITH <options>]
    | MODIFY COLUMN name UNSET FULLTEXT
    | RENAME name
    | SET <option_name>=<option_value> [, ...]
    | UNSET <option_name>[, ...]
   ]

Examples​

Add column​

Adds a new column to the table:

ALTER TABLE monitor ADD COLUMN load_15 double;

Definition of column is the same as in CREATE.

We can set the new column's location. In first position for example:

ALTER TABLE monitor ADD COLUMN load_15 double FIRST;

After an existing column:

ALTER TABLE monitor ADD COLUMN load_15 double AFTER memory;

Adds a new column as a tag(primary key) with a default value:

ALTER TABLE monitor ADD COLUMN app STRING DEFAULT 'shop' PRIMARY KEY;

Remove column​

Removes a column from the table:

ALTER TABLE monitor DROP COLUMN load_15;

The removed column can't be retrieved immediately by all subsequent queries.

Modify column type​

Modify the date type of a column

ALTER TABLE monitor MODIFY COLUMN load_15 STRING;

The modified column cannot be a tag (primary key) or time index, and it must be nullable to ensure that the data can be safely converted (returns NULL on cast failures).

Alter table options​

ALTER TABLE statements can also be used to change the options of tables.

Currently following options are supported:

  • ttl: the retention time of data in table.
  • compaction.twcs.time_window: the time window parameter of TWCS compaction strategy.
  • compaction.twcs.max_output_file_size: the maximum allowed output file size of TWCS compaction strategy.
  • compaction.twcs.max_active_window_runs: the maximum allowed sorted runs in the active window of TWCS compaction strategy.
  • compaction.twcs.max_inactive_window_runs: the maximum allowed sorted runs in the inactive windows of TWCS compaction strategy.
  • compaction.twcs.max_active_window_files: the maximum allowed number of files in the active window of TWCS compaction strategy.
  • compaction.twcs.max_inactive_window_files: the maximum allowed number of files in the inactive windows of TWCS compaction strategy.
ALTER TABLE monitor SET 'ttl'='1d';

ALTER TABLE monitor SET 'compaction.twcs.time_window'='2h';

ALTER TABLE monitor SET 'compaction.twcs.max_output_file_size'='500MB';

ALTER TABLE monitor SET 'compaction.twcs.max_inactive_window_files'='2';

ALTER TABLE monitor SET 'compaction.twcs.max_active_window_files'='2';

ALTER TABLE monitor SET 'compaction.twcs.max_active_window_runs'='6';

ALTER TABLE monitor SET 'compaction.twcs.max_inactive_window_runs'='6';

Unset options:​

ALTER TABLE monitor UNSET 'ttl';

Modify column fulltext index options​

Enable fulltext index on a column:

ALTER TABLE monitor MODIFY COLUMN load_15 SET FULLTEXT WITH (analyzer = 'Chinese', case_sensitive = 'false');

You can specify the following options using FULLTEXT WITH when enabling fulltext options:

  • analyzer: Sets the language analyzer for the full-text index. Supported values are English and Chinese. Default is English.
  • case_sensitive: Determines whether the full-text index is case-sensitive. Supported values are true and false. Default is false.

If WITH <options> is not specified, FULLTEXT will use the default values.

Disable fulltext index on a column​

ALTER TABLE monitor MODIFY COLUMN load_15 UNSET FULLTEXT;

The column must be a string type to alter the fulltext index.

If the fulltext index has never been enabled, you can enable it and specify the analyzer and case_sensitive options. When the fulltext index is already enabled on a column, you can disable it but cannot modify the options.

Rename table​

Renames the table:

ALTER TABLE monitor RENAME monitor_new;

This command only renames the table; it doesn't modify the data within the table.