You can use the ALTER TABLE syntax to change the schema of a table. For example, you can use this syntax to add a column to a table, delete a column from a table, modify the data type of a column, and add attributes for a table. This topic describes how to use the ALTER TABLE syntax in LindormTable and LindormTSDB.
Applicable engines and versions
The ALTER TABLE syntax is applicable to all versions of LindormTable and LindormTSDB.
Usage notes
When you perform the ALTER TABLE operation on a table, the table can be read or written. However, timeout errors or slight latency may occur due to the following reasons:
When a DDL operation is performed to modify data, each data shard is disabled first and then enabled to load metadata. The process takes a short period of time within a time from 10 ms to hundreds of milliseconds. If a request is sent to a shard that is being enabled, the response to the request may be slightly delayed. We recommend that you perform DDL operations to modify data during off-peak hours.
The results of all DDL operations are not returned until the data shards are modified. In addition, the timeout period configured for clients is short. Therefore, timeout errors may occur when you perform a DDL operation on a large table. In fact, the DDL operation continues to be executed on the server until it is completed.
Syntax
alter_table_statement ::= ALTER TABLE table_name alter_table_instruction
alter_table_instruction ::= ADD [COLUMN] column_name type [TAG]
| DROP COLUMN [ IF EXISTS ] column_name
| SET table_options
| COMPACT (idcName)?
Parameters supported by different engines
LindormTable and LindormTSDB supports different parameters of ALTER TABLE. The following table describes the parameters supported by LindormTable and LindormTSDB.
Parameter | LindormTable | LindormTSDB |
ADD COLUMN | 〇 | 〇 |
COMPACT | 〇 Note This parameter is supported by LindormTable 2.3.4 and later versions. | ✖️ |
DROP COLUMN | 〇 Note This parameter is supported by LindormTable 2.6.2.1 and later versions. | ✖️ |
SET table_options | 〇 | ✖️ |
Parameters
ADD COLUMN
The ADD COLUMN parameter is supported LindormTable and LindormTSDB.
The following limits are imposed when you specify ADD COLUMN to add a column:
The column that is added by specifying ADD COLUMN cannot be used as the primary key.
In LindormTSDB, only tag columns or field columns can be added. Timestamp columns cannot be added.
DROP COLUMN
The DROP COLUMN parameter is supported only by LindormTable 2.6.2.1 and later versions.
The DROP COLUMN parameter is in the invitational preview. If you want to delete an existing column from a table, contact the technical support (DingTalk ID: s0s3eg3).
SET table_options
The SET table_options parameter is supported only by LindormTable.
For more information about table attributes, see Table attributes.
Note the following items when you modify the MUTABILITY and CONSISTENCY attributes:
Before you create an index, you can modify the MUTABILITY and CONSISTENCY attributes.
After you create an index:
You cannot modify the MUTABILITY attribute.
You can change the value of CONSISTENCY only from eventual to strong, but not from strong to eventual.
For more information, see Enable dynamic columns.
COMPACT
The COMPACT parameter is supported only by LindormTable 2.3.4 and later versions.
The major compaction operation merges data files and clears deleted or expired data. The operation can be used to release disk spaces and improve read and write performance. However, major compaction operations consume system resources.
If you do not specify idcName:
A single-zone instance has only one IDC. Therefore, you can leave idcName unspecified if your instance is deployed in a single zone.
A multi-zone instance has two IDCs. If you leave idcName unspecified, the major compaction operation is performed based on the following rules:
If the version of LindormTable is earlier than 2.6.4.2, the major compaction operation is performed only on idc1.
If the version of LindormTable is 2.6.4.2 or later, the major compaction operation is performed on all IDCs by default.
If you specify idcName, the major compaction operation is performed on the specified IDC. You can specify multiple values for idcName. Separate multiple values with commas (,).
For more information, see Execute major compaction operations.
Examples
In the following examples, the sample table named sensor is created by executing the following CREATE TABLE statement:
CREATE TABLE sensor (
device_id VARCHAR NOT NULL,
region VARCHAR NOT NULL,
time TIMESTAMP NOT NULL,
temperature DOUBLE,
humidity BIGINT,
PRIMARY KEY(device_id, region, time)
);
Add a column
Add the column n1 to the sensor table.
ALTER TABLE sensor ADD COLUMN n1 DOUBLE;
Delete a column
Delete the column n1 from the sensor table.
ALTER TABLE sensor DROP COLUMN IF EXISTS n1;
Verify the result
You can execute 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';
For more information about dynamic columns, see Dynamic columns.
Configure data TTL
Configure a data TTL in seconds for the sensor table.
ALTER TABLE sensor SET 'TTL' = '2592000';
Cancer the data TTL of the sensor table.
ALTER TABLE sensor SET 'TTL' = '';
Verify the result
You can perform the following steps to verify the result: 1. Log on to the cluster management system. 2. On the Overview tab, click the name of the table. 3. In the Current table details section, 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.
Configure 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
Go to the Overview page in the cluster management system, and then click the name of the table in the database. In the Current table details section, click View table properties and view the value of the COMPRESSION parameter.
Configure hot and cold data separation
Configure a hot and cold data boundary for the sensor table.
ALTER TABLE sensor SET 'CHS' = '86400', 'CHS_L2' = 'storagetype=COLD';
Cancel the hot and cold data boundary of the sensor table.
ALTER TABLE sensor SET 'CHS' = '', 'CHS_L2' = '';
NoteFor more information about hot and cold data separation, see Overview.
Verify the result
Go to the Overview page in the cluster management system, and then click the name of the table in the database. In the Current table details section, click View table properties and view the values of the CHS and CHS_L2 parameters.
Perform major compaction operations
Perform the major compaction operation on the sensor table.
ALTER TABLE sensor COMPACT;
NoteIf the instance is deployed across multiple zones and you do not specify an IDC, the major compaction operation is performed based on the following rules:
If the version of LindormTable is earlier than 2.6.4.2, the major compaction operation is performed only on idc1 by default.
If the version of LindormTable is 2.6.4.2 or later, the major compaction operation is performed on all IDCs by default.
Perform the major compaction operation on idc2.
ALTER TABLE sensor COMPACT 'idc2';
Perform the major compaction operation on idc1 and idc2.
ALTER TABLE sensor COMPACT 'idc1,idc2';