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

CREATE TABLE

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

Function

The time series engine provides DDL statements for creating a time series table. 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 time series database (TSDB) allows you to specify the time to live (TTL) of data and the period for creating partitions (PERIOD) to automatically create or evict partitions. In addition, orientation needs to be set to timeseries in the table creation statement. You can specify the distribution columns to be displayed. If no distribution column is explicitly specified, all columns of the tag type are set to 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 of the tag type can be set to distribution columns, an error is reported. Deleting tags will not be supported. However, you can use DISTRIBUTE BY HIDETAG to create a time series table in which any tag column can be deleted. 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 data insertion scenario (fewer than 100 data records are inserted at a time), enabling the time series Delta table helps improve the performance of the time series engine.

Precautions

  • The real-time data warehouse is in the open beta test (OBT) and cannot be used in the standard data warehouse. To use the real-time data warehouse, submit a service ticket to purchase a real-time data warehouse cluster.
  • 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 does not have TTL and PERIOD defaults to one day. 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 Format

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 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 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 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.
  • orientation of storage_parameter 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 Delta table:
    1. enable_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 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 executing the deltamerge command to perform write-back on each DN. If the data volume in the Delta table is lower than the threshold, the write-back will not be performed. The default value is 60000. The value ranges from 0 to 1000000.
    4. tsdb_deltamerge_interval: specifies the interval of the scheduled automatic write-back 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_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