Help Center > > Developer Guide> Time Series Engine> Usage> Time Series Syntax> ALTER TABLE

ALTER TABLE

Updated at: Sep 17, 2021 GMT+08:00

Function

Currently, the DDL statement for modifying a time series table provides the capabilities of adding and deleting columns based on the key-value storage and modifying the TTL and PERIOD attributes to modify partition management tasks. PERIOD indicates the time range of the added partition, and TTL indicates the partition to be deleted.

Precautions

  • orientation of storage_parameter cannot be modified. Currently, columns can only be added or deleted.
  • The column to be added must have the kv_type attribute, and the attribute must be set to tstag or tsfiled.
  • The TTL and PERIOD attributes must be created or modified in the partitioned table.
  • The column to be deleted cannot be of the tstime type that indicates a partition column.

Syntax Format

  • The syntax of the DDL statement for adding columns is as follows:
    ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) }
    action [, ... ];
    The action clause is as follows:
    ADD [ COLUMN ] column_name data_type [ kv_type ] [ compress_mode ] [ COLLATE
    collation ] [ column_constraint [ ... ] ]
    The value range of kv_type is as follows:
    { TSTAG | TSFIELD | TSTIME }
  • The syntax of the DDL statement for deleting columns is as follows:
    ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) }
    action [, ... ];
    The action clause is as follows:
    DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
  • The syntax of the DDL statement for modifying TTL, PERIOD, and Delta table is as follows:
    ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) }
    action [, ... ];
    The action clause is as follows:
    SET ([ttl = value] [,period = value] [, ... ] )

    The clause for modifying parameters related to the Delta table is as follows:

    SET ([enable_delta = on/off] [,tsdb_deltainsert_threshold = value][,tsdb_deltamerge_threshold = value][,tsdb_deltamerge_interval = value][, ... ] )
    • If the Delta table function is enabled, a Delta table and a scheduled automatic write-back task are created. If the Delta table function is disabled, the Delta table is forcibly written back independent of the tsdb_deltamerge_threshold parameter. Then the Delta table and the scheduled automatic write-back task are deleted.
    • Modifying tsdb_deltainsert_threshold and tsdb_deltamerge_threshold will change the data insertion and automatic write-back policies.
    • If the value of tsdb_deltamerge_interval is changed, the interval of the scheduled automatic write-back task is updated.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Create a simple time series table.
CREATE TABLE CPU(
idle numeric TSField,
IO numeric TSField,
scope text TSTag,
IP text TSTag,
time timestamp TSTime
) with (TTL='7 days', PERIOD = '1 day', orientation=TIMESERIES);

-- Add a column to the time series table.
ALTER TABLE CPU ADD COLUMN memory numeric TSField;
;
-- Delete a column from the time series table.
ALTER TABLE CPU DROP COLUMN idle;

-- Modify the TTL attribute of the time series table.
ALTER TABLE CPU SET (TTL = '7 day');

-- Modify the PERIOD attribute of the time series table.
ALTER TABLE CPU SET (PERIOD = '1 day');

-- Modify parameters related to the Delta table of the time series table.
ALTER TABLE CPU SET (enable_delta = on, tsdb_deltamerge_threshold = 3000);

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel