Help Center > > Developer Guide

CREATE TABLE

Updated at: Jul 14, 2021 GMT+08:00

Function

The TSDB extension engine provides DDL statements for creating time series tables. To create a time series table that stores data based on key values, the DDL statement needs to include the dimension attribute (tstag), indicator attribute (tsfield), and time attribute (tstime). The TSDB allows you to specify the time to live of data (TTL) and the period for creating partitions (PERIOD), providing automatic partition creation and eviction functions. In addition, orientation needs to be set to timeseries in the table creation statement. Users can specify the distribution columns to be displayed. If no distribution column is explicitly specified, all columns of the tag type are set as distribution columns in a time series table by default. Some column types cannot be specified as distribution columns. In this case, these columns will be excluded from the distribution columns. If none of the columns in the tag table can be used as distribution columns, an error is reported. Deleting tags will not be supported. However, you can use the DISTRIBUTE BY HIDETAG method to create a time series table that supports deleting any tag column without specifying them. This method is recommended only when the join or group operation is not used or the requirements for this type of operation are not high.

In the small-batch insertion scenario (the number of data records inserted at a time is less than 100), enabling the time series delta table helps improve the performance of the time series engine.

Precautions

  • To create a time series table, you must have the USAGE permission on schema cstore.
  • All attributes of a time series table, except the time attribute, must be specified to either a dimension or an indicator.
  • By default, a time series table is automatically created as a partitioned table and distributed based on all dimensions. It can also be distributed based on specified attributes.
  • You must specify only one time attribute (tstime) for a time series table. If all attributes except tstime in the table creation statement are set to tstag, the storage mode of the table is automatically changed to row-store. If all attributes except tstime in the table creation statement are set to tsfield, the storage mode of the table is automatically changed to column-store.
  • If you have specified TTL or PERIOD in the table creation statement, the corresponding time series partition management task is automatically created. If the parameters are not specified, the created table is without TTL and with a one-day period by default. The specified TTL cannot exceed 100 years, and the specified period cannot be less than 1 hour and cannot be greater than the specified TTL.
  • If PARTITION BY is specified explicitly, only tstime can be used as the partition key.

Syntax

The syntax format for creating a time series table is a simplified syntax format for creating a table. The kv_type attribute is added to the attribute column, the TTL and Period parameters are added to storage_parameter, and the timeseries value is added to orientation.

You can use the four parameters of storage_parameter to control the enabling status and related configurations of the timing delta table. For details, see the following description.

CREATE TABLE [ IF NOT EXISTS ] table_name
({ column_name data_type [ kv_type ] }
[, ... ])
[ WITH ( {storage_parameter = value}  [, ... ] ) ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ]
[ TABLESPACE tablespace_name ]
[ DISTRIBUTE BY  HASH ( column_name [,...] | hidetag )]
[ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ]
[ table_partitioning_clauses ];
  • The kv_type attribute column has four values: tstag, tsfield, tstime, and tshide. tstag and tsfield correspond to the dimension attribute and indicator attribute in key-value storage, respectively. tstime corresponds to the time attribute. tshide hides the distribution column attribute, which is for internal use only and is not open to users.
  • TTL indicates the data storage period of a table. Data that exceeds the TTL period will be deleted. PERIOD indicates that data is partitioned by time. The partition size may affect the query performance. In this partitioning mode, a new partition will be created at the interval specified by PERIOD. The values of TTL and PERIOD are of the interval type, for example, 1 hour, 1 day, 1 week, 1 month, 1 year, and 1 month 2 day 3 hour.
  • Storage parameter orientation specifies the storage mode. The key-value storage is supported only when orientation is set to timeseries.
  • You do not need to manually specify distribute by and table_partitioning_clauses for a time series table. By default, data is distributed based on all dimensions, and a partitioned table is automatically created.
  • storage_parameter related to the time series delta table:
    1. enable_tsdb_delta: determines whether to enable the delta table function for the time series table. The default value is off.
    2. tsdb_deltainsert_threshold: specifies the data volume threshold for inserting data to the time series delta table at a time on each DN. The default value is 5000. The value ranges from 100 to 60000.
    3. tsdb_deltamerge_threshold: specifies the data volume threshold for performing delta merge write-back on each DN. If the data volume in the delta table is less than the threshold, the write-back will not occur. The default value is 60000. The value ranges from 0 to 1000000.
    4. tsdb_deltamerge_interval: interval for automatically writing back the scheduled task, in minutes. The default value is 5. The value ranges from 1 to 1440.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 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);

-- Create a time series table with a delta table.
CREATE TABLE CPU(
idle numeric TSField,
IO numeric TSField,
scope text TSTag,
IP text TSTag,
time timestamp TSTime
) with (
enable_tsdb_delta=on,
tsdb_deltainsert_threshold=10000,
tsdb_deltamerge_threshold=1000,
tsdb_deltamerge_interval=3,
TTL='7 days',
PERIOD = '1 day',
orientation=TIMESERIES);

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