Lindorm:ALTER TABLE

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.

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.6 and later versions.

The DROP COLUMN operation is executed asynchronously. If you execute an ALTER TABLE statement in which DROP COLUMN is specified, the specified column is immediately deleted after the statement is executed. However, data in the column is not cleared until the next COMPACT operation is performed. To clear data in a deleted column more quickly, manually perform the FLUSH and COMPACT operations. Before data in a deleted column is cleared, you cannot add a column with the same name as the deleted column. For more information, see FAQ.

SET table_options

The SET table_options parameter is supported only by LindormTable.

For more information about table attributes, see Table attributes. You can execute the SHOW TABLE VARIABLES statement to check whether the table attributes are successfully configured. For more information, see SHOW TABLE VARIABLES.

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, a 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 BIGINT 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';

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

Specify a hot data and cold data boundary for a table

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

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

Cancel the hot and cold data boundary

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

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

Verify the result

You can execute 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.

Perform major compaction operations

• Perform the major compaction operation on the sensor table.

  ALTER TABLE sensor COMPACT;

  Note

  If 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';