ALTER TABLE - Lindorm - Alibaba Cloud Documentation Center

The ALTER TABLE statement modifies the schema of a table, such as adding or dropping columns and setting table attributes. Supported clauses differ between LindormTable and LindormTSDB.

Engines and versions

The ALTER TABLE statement applies to LindormTable and LindormTSDB. No version limits apply.

Usage notes

The ALTER TABLE operation is non-locking. The table remains readable and writable throughout the operation. However, you might encounter timeout errors or brief performance fluctuations for the following reasons:

During a DDL operation, each shard is briefly closed and reopened to reload metadata. This typically takes 10 to several hundred milliseconds. If a request targets a shard that is reopening, you may experience a minor delay or brief performance fluctuation. We recommend that you perform DDL operations during off-peak hours.
The result of a DDL operation is returned only after the change is applied to all shards. For large tables, this may cause a client-side timeout if the client timeout is short. The operation continues to run on the server until it completes.

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]

Engine differences

The ALTER TABLE statement differs between LindormTable and LindormTSDB. The following table compares the supported clauses.

Clause	LindormTable	LindormTSDB
ADD COLUMN	Supported	Supported
COMPACT	Supported Note Supported in LindormTable 2.3.4 and later.	Not supported
DROP COLUMN	Supported Note Supported in LindormTable 2.6.6 and later.	Not supported
SET table_options	Supported	Not supported

Clauses

ADD COLUMN

Both LindormTable and LindormTSDB support the ADD COLUMN clause.

The following rules apply when you add columns:

You cannot add a column as a primary key.
For LindormTSDB, you can add only a tag column or a field column. You cannot add a timestamp column.

DROP COLUMN

The DROP COLUMN clause is available only in LindormTable 2.6.6 and later.

Note

If you cannot upgrade your instance to 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 succeeds, the system immediately deletes the target column, but the column data is not cleaned up until a COMPACT operation completes. To accelerate data cleanup, you can manually run the FLUSH and COMPACT operations. You cannot add a column with the same name until the data is completely cleaned up. For more information, see FAQ.

SET table_options

Only LindormTable supports the SET table_options clause.

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

Important

When modifying the MUTABILITY and CONSISTENCY attributes, note the following:

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 from eventual to strong, but you cannot change it from strong to eventual.

For an example, see Enable dynamic columns.

COMPACT

Only LindormTable 2.3.4 and later supports the COMPACT clause, which triggers a major compaction.

Running a major compaction merges data files, clears deleted or expired data, releases disk space, and improves read/write performance. This operation is resource-intensive.

If you do not specify idcName:
- For a single-zone instance, which has only one IDC, specifying idcName has no effect.
- For a multi-zone instance, which has two IDCs, the following rules apply if you do not specify idcName:
  - If your LindormTable version is earlier than 2.6.4.2, a major compaction runs only on idc1.
  - If your LindormTable version is 2.6.4.2 or later, a major compaction runs on all IDCs by default.
If you specify idcName, the major compaction runs on the specified IDC. You can specify multiple IDC names, separated by commas (,).

For an example, see Run a major compaction.

Examples

The examples in this section use the sensor table created with the following statement from 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 new column n1 to the sensor table.

ALTER TABLE sensor ADD COLUMN n1 DOUBLE;

Add a spatiotemporal column to the spatiotemporal data table roads.

Add a single spatial column.

ALTER TABLE roads ADD COLUMN g2 GEOMETRY(LINESTRING);

Add multiple spatial columns.

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

Drop a column

Drop the column n1 from the table sensor.

ALTER TABLE sensor DROP COLUMN IF EXISTS n1;

Verify the result

Run the following statement to verify the result:

DESCRIBE table sensor;

Enable dynamic columns

Enable the dynamic columns feature 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 spatiotemporal data table roads.

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

Set the TTL

Set the TTL for the sensor table in seconds (s).
```
ALTER TABLE sensor SET TTL = '2592000';
```
Remove the TTL from the sensor table.
```
ALTER TABLE sensor SET TTL = '';
```
Verify the result

In the cluster management system, go to the Overview page. Click the target table name under the target database. In the Current table details section, view the value of the TTL parameter. For information about how to access the cluster management system, see Log on to the cluster management system.

Set the compression algorithm

Set the sensor table's compression algorithm 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

In the cluster management system, go to the Overview page and click the target table name under the target database. In the Current table details section, click View table properties to 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 the hot and cold data boundary

Ingestion timestamp

Set the hot and cold data boundary for the sensor table. LindormTable determines whether to archive data to Capacity storage based on the ingestion timestamp of each row.

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

Verify the result

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 syntax, see SHOW VARIABLES.

Custom time column

Important

Separating data based on a custom time column has limitations. Before you use this feature, review the Usage notes.

Set the custom time column time as the hot and cold data boundary for the sensor table. LindormTable determines whether to archive data to Capacity storage based on the event timestamp of the time column in each row.

-- 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.
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 syntax, see SHOW VARIABLES.

Remove the hot and cold data boundary

Remove the hot and cold data boundary 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
For a multi-zone instance, note the following rules:
- If your LindormTable version is earlier than 2.6.4.2, the system runs a major compaction on idc1 by default if you do not specify an IDC.
- If your 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';
```