ALTER TABLE - Lindorm - Alibaba Cloud Documentation Center

The ALTER TABLE statement modifies the schema of a table. For example, you can use this statement to add or delete columns, or set table attributes. This topic describes how to use the ALTER TABLE statement in LindormTable and LindormTSDB.

Engines and versions

You can use the ALTER TABLE syntax with LindormTable and LindormTSDB across all versions.

Usage notes

The ALTER TABLE operation does not lock the table. The table remains readable and writable during the Data Definition Language (DDL) operation. However, you may experience timeout errors, minor jitter, or glitches for the following reasons:

During a DDL operation, each shard is shut down and then reopened to reload metadata. This process is short and typically lasts from 10 ms to several hundred milliseconds. If a request is sent to a shard while it is reopening, the request may experience minor jitter or glitches. We recommend that you perform DDL operations during off-peak hours.
A DDL operation returns a result only after all shards are modified. If you run a DDL operation on a large table, you may encounter a timeout error if the client timeout period is too short. However, the DDL operation continues to run on the server until it is complete.

Syntax

alter_table_statement   ::= ALTER TABLE table_name alter_table_instruction

alter_table_instruction ::=   
  add_column_list
  | DROP COLUMN [ IF EXISTS ] column_name
  | SET table_options
  | COMPACT (idcName)?
   
add_column_list ::= ADD [COLUMN] column_def ( ',' ADD [COLUMN] column_def )*
column_def ::= column_name type [TAG]

Differences

The following table compares the significant differences in the ALTER TABLE syntax for LindormTable and LindormTSDB.

Syntax element	LindormTable	LindormTSDB
ADD COLUMN	〇	〇
COMPACT	〇 Note Supported by LindormTable 2.3.4 and later.	✖️
DROP COLUMN	〇 Note Supported by LindormTable 2.6.6 and later.	✖️
SET table_options	〇	✖️

Usage

ADD COLUMN (Add columns)

LindormTable and LindormTSDB both support the ADD COLUMN statement.

Note the following rules when you add a column:

The new column cannot be a primary key.
For LindormTSDB, you can add only tag columns (TAG) and field columns (FIELD). You cannot add timestamp columns.

DROP COLUMN (Delete a column)

Only LindormTable 2.6.6 and later supports DROP COLUMN.

Note

If you cannot upgrade your instance to LindormTable 2.6.6 or later in the console, contact Lindorm technical support (DingTalk ID: s0s3eg3).

The DROP COLUMN operation is asynchronous. After the ALTER TABLE statement is successfully executed, the system immediately deletes the target column. However, the data in the column is cleared only after the system completes a COMPACT operation. To accelerate the data cleanup process, you can manually run the FLUSH and COMPACT operations. You cannot add a column with the same name until the data is cleared. For more information, see FAQ.

SET table_options (Set table attributes)

Only LindormTable supports SET table_options.

For a list of table attributes, see Table attributes. You can run the SHOW TABLE VARIABLES statement to verify that the table attributes are set. For more information, see SHOW TABLE VARIABLES.

Important

Note the following when you modify the MUTABILITY and CONSISTENCY attributes:

Before you create an index, you can modify both MUTABILITY and CONSISTENCY.
After you create an index:
- You cannot modify MUTABILITY.
- You can change the value of CONSISTENCY only from eventual to strong. You cannot change it from strong to eventual.

For an example, see Enable dynamic columns.

COMPACT (Force a major compaction)

Only LindormTable 2.3.4 and later supports major compaction.

A major compaction operation merges data files, clears deleted or expired data, releases disk space, and improves read and write performance. However, this operation consumes system resources.

If you do not specify idcName:
- A single-zone instance has only one IDC. Therefore, specifying idcName has no effect.
- A multi-zone instance has two IDCs. If you do not specify idcName, the major compaction operation runs based on the following rules:
  - If the LindormTable version is earlier than 2.6.4.2, major compaction runs only on idc1.
  - If the LindormTable version is 2.6.4.2 or later, major compaction runs on all IDCs by default.
If you specify idcName, major compaction runs on the specified IDC. You can specify multiple IDCs. Separate them with commas (,).

For an example, see Run a major compaction.

Examples

The examples in this topic are based on the sample table that is created using the statement in the CREATE TABLE topic.

CREATE TABLE sensor (
    device_id VARCHAR NOT NULL,
    region VARCHAR NOT NULL,
    time BIGINT NOT NULL,
    temperature DOUBLE,
    humidity BIGINT,
    PRIMARY KEY(device_id, region, time)
);

Add columns

Add a column named n1 to the sensor table.

ALTER TABLE sensor ADD COLUMN n1 DOUBLE;

Add a spatio-temporal column to the roads spatio-temporal data table.

Add a single spatio-temporal column.

ALTER TABLE roads ADD COLUMN g2 GEOMETRY(LINESTRING);

Add multiple spatio-temporal columns.

ALTER TABLE roads ADD COLUMN g2 GEOMETRY(LINESTRING), g3 GEOMETRY(POLYGON);

Delete a column

Delete the n1 column from the sensor table.

ALTER TABLE sensor DROP COLUMN IF EXISTS n1;

Verifying the result

Run the following statement to verify the result.

DESCRIBE table sensor;

Enable dynamic columns

Enable dynamic columns for the sensor table.

ALTER TABLE sensor SET DYNAMIC_COLUMNS = 'true';

Note

For more information about dynamic columns, see Dynamic columns.

Set table consistency

Set the consistency for the roads spatio-temporal data table.

ALTER TABLE roads SET 'CONSISTENCY' = 'strong';

Set a data TTL

Set a data TTL for the sensor table. The unit is seconds (s).
```
ALTER TABLE sensor SET TTL = '2592000';
```
Cancel the data TTL for the sensor table.
```
ALTER TABLE sensor SET TTL = '';
```
Verify the results
On the Overview page of the cluster management system, click the name of the target database and then click the target table. In the Current table details area, view the value of the ttl parameter. For more information about how to log on to the cluster management system, see Log on to the cluster management system.

Set a compression algorithm

Set the compression algorithm for the sensor table to ZSTD.
```
ALTER TABLE sensor SET COMPRESSION = 'ZSTD';
```
Set the compression algorithm for the sensor table to NONE.
```
ALTER TABLE sensor SET COMPRESSION = 'NONE';
```
Verify the result
On the Overview page of the cluster management system, click the name of the target database and then click the target table. In the Current table details area, click View table properties and view the value of the COMPRESSION parameter.

Configure hot and cold data separation

Note

For more information about hot and cold data separation, see Introduction to hot and cold data separation.

Set a boundary for hot and cold data

Separate hot and cold data based on timestamps

Set a boundary for hot and cold data for the sensor table. LindormTable archives data to storage-optimized cloud storage based on the timestamp when the data is written to the database (ingestion timestamp).

-- Enable hot and cold data separation for the sensor table based on timestamps and set the boundary to one day.
ALTER TABLE sensor SET CHS = '86400', CHS_L2 = 'storagetype=COLD';

Result Verification

You can run the SHOW TABLE VARIABLES FROM sensor LIKE 'CHS%'; statement to view the values of the CHS and CHS_L2 parameters. For more information about the SHOW VARIABLES statement, see SHOW VARIABLES.

Separate hot and cold data based on a custom time column

Important

Separating hot and cold data based on a custom time column has specific limits and requirements. Before you use this feature, review the related notes. For more information, see Usage notes.

Set the custom time column time to define the boundary between hot and cold data for the sensor table. LindormTable determines whether to archive a row of data to storage-optimized cloud storage based on the business timestamp in the time column.

-- Enable hot and cold data separation for the sensor table based on a custom column. Set the boundary to one day and use the time column for separation.
ALTER TABLE sensor SET CHS ='86400', CHS_L2 = 'storagetype=COLD', CHS_COLUMN = 'COLUMN=time';

Verify the result

You can run the SHOW TABLE VARIABLES FROM sensor LIKE 'CHS%'; statement to view the values of the CHS, CHS_L2, and CHS_COLUMN parameters. For more information about the SHOW VARIABLES statement, see SHOW VARIABLES.

Cancel the boundary for hot and cold data

Cancel the boundary for hot and cold data for the sensor table.

ALTER TABLE sensor SET CHS = '', CHS_L2 = '';

Verify the result

Run a major compaction

Run a major compaction on the sensor table.
```
ALTER TABLE sensor COMPACT;
```
Note
If the instance is a multi-zone instance, note the following rules:
- If the LindormTable version is earlier than 2.6.4.2 and you do not specify an IDC, the system runs a major compaction on idc1 by default.
- If the LindormTable version is 2.6.4.2 or later, the system runs a major compaction on all IDCs by default.
Run a major compaction on idc2.
```
ALTER TABLE sensor COMPACT 'idc2';
```
Run a major compaction on idc1 and idc2.
```
ALTER TABLE sensor COMPACT 'idc1,idc2';
```