Updated at: 2022-08-03 GMT+08:00

CREATE TABLE

Function

CREATE TABLE creates a time series table in the current database. The table will be owned by the user who created it.

The IoT data warehouse 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.

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.
  • If PARTITION BY is specified explicitly, only tstime can be used as the partition key.
  • If an index column is deleted using DROP COLUMN, the remaining index columns will be used to rebuild the index. If all the index columns are deleted, the first 10 tag columns will be used to rebuild the index.
  • Time series tables do not support UPDATE, UPSERT, primary keys, or PCKs.
  • Each time series table is bound to a tag table. The OIDs and index OIDs of the tag table are recorded in the reltoastrelid and reltoastidxid columns of the pg_class table, respectively.
  • By default, the first 10 tag columns of a tag table are used to create an index.
  • Tag tables cannot be queried on CNs. The table size returned in a query contains the tag table size.

Syntax Format

CREATE TABLE [ IF NOT EXISTS ] table_name
({ column_name data_type [ kv_type ] 
    | LIKE source_table [like_option [...] ] }
}
    [, ... ])
[ WITH ( {storage_parameter = value}  [, ... ] ) ]

[ TABLESPACE tablespace_name ]
[ DISTRIBUTE BY  HASH ( column_name [,...])]
[ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ]
[ table_partitioning_clauses ];
The options for LIKE are as follows:
{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | DISTRIBUTION | ALL }

Parameter description

  • IF NOT EXISTS

    Does not throw an error if a table with the same name exists. A notice is issued in this case.

  • table_name

    Specifies the name of the table to be created.

  • column_name

    Specifies the name of a column to be created in the new table.

  • data_type

    Specifies the data type of the column.

  • kv_type

    kv_type attributes of columns, including a dimension attribute (TSTAG), an indicator attribute (TSFIELD), and a time attribute (TSTIME).

    One and only one TSTIME attribute must be specified. Columns of the TSTIME type cannot be deleted. At least one of the TSTAG and TSFIELD columns must be specified, or an error will be reported during table creation.

    The TSTAG column supports the text, char, bool, int, and big int types.

    The TSTIME column supports the timestamp with time zone, timestamp without time zone, and date types.

    For details about the data types supported by the TSFIELD column, see Data Types Supported by TSFIELD.

  • LIKE source_table [like_option...]

    Specifies a table from which the new table automatically copies all column names and their data types.

    The new table and the original table are decoupled after creation is complete. Changes to the original table will not be applied to the new table, and scans on the original table will not be performed on the data of the new table.

    Columns copied by LIKE are not merged with the same name. If the same name is specified explicitly or in another LIKE clause, an error will be reported.

    A time series table only inherits from another time series table.

  • WITH( { storage_parameter = value } [, ...] )

    Specifies an optional storage parameter for a table.

    • ORIENTATION

      Specifies the storage mode (time series, row-store, or column-store) of table data. This parameter cannot be modified once it is set.

      Options:
      • TIMESERIES indicates that the data is stored in time series.
      • COLUMN indicates that the data is stored in columns.
      • ROW indicates that table data is stored in rows.
    Default value: ROW
    • COMPRESSION

      Specifies the compression level of the table data. It determines the compression ratio and time. Generally, a higher compression level indicates a higher compression ratio and a longer compression time, and vice versa. The actual compression ratio depends on the distribution characteristics of loading table data.

      Options:
      • The valid values for time series tables and column-store tables are YES/NO and LOW/MIDDLE/HIGH, and the default is LOW.
      • The valid values for row-store tables are YES and NO, and the default is NO.
    • COMPRESSLEVEL

      Specifies table data compression rate and duration at the same compression level. This divides a compression level into sub-levels, providing you with more choices for compression ratio and duration. As the value becomes greater, the compression rate becomes higher and duration longer at the same compression level. The parameter is only valid for time series tables and column-store tables.

      Value range: 0 to 3. The default value is 0.

    • MAX_BATCHROW

      Specifies the maximum number of rows in a storage unit during data loading. The parameter is only valid for column-store tables.

      Value range: 10000 to 60000

      Default value: 60000

    • PARTIAL_CLUSTER_ROWS

      Specifies the number of records to be partially clustered for storage during data loading. The parameter is only valid for column-store tables.

      Value range: 600000 to 2147483647

    • enable_delta

      Specifies whether to enable delta tables in time series tables. The parameter is only valid for time series tables and column-store tables.

      Default value: on

    • SUB_PARTITION_COUNT

      Specifies the number of level-2 partitions in a time series table. This parameter cannot be modified once it is set.

      Value range: 1 to 1024. The default value is 32.

    • DELTAROW_THRESHOLD

      Specifies the maximum number of rows (SUB_PARTITION_COUNT * DELTAROW_THRESHOLD) to be imported to the delta table when a time series table is imported. This parameter is valid only if enable_delta has been enabled. The parameter is only valid for time series tables and column-store tables.

      Value range: 0 to 60000

      Default value: 10000

    • COLVERSION

      Specifies the version of a storage format. The parameter is only valid for time series tables and column-store tables. You cannot switch between different storage formats in time series tables. The time series table supports only version 2.0.

      Options:

      1.0: Each column in a column-store table is stored in a separate file. The file name is relfilenode.C1.0, relfilenode.C2.0, relfilenode.C3.0, or similar.

      2.0: All the columns of a time series or column-store table are combined and stored in a file. The file is named relfilenode.C1.0.

      Default value: 2.0

    • TABLESPACE tablespace_name

      Specifies the tablespace where the new table is created. If not specified, default tablespace is used.

    • DISTRIBUTE BY

      Specifies how the table is distributed or replicated between DNs.

      Options:
      • HASH (column_name): Performs Hash on the specified column and distributes data to the specified DN through mapping. The time series table supports data distribution only by the tag column.

      By default, data is distributed based on all the tag columns. In a time series table, only tag columns can be used as distribution columns.

    • TO { GROUP groupname | NODE ( nodename [, ... ] ) }

      TO GROUP specifies the Node Group in which the table is created. Currently, it cannot be used for HDFS tables. TO NODE is used for internal scale-out tools.

    • TTL

      Schedules the partition deletion jobs in a time series table. By default, partition jobs are not deleted.

      Value range:

      1 hour – 100 year

    • PERIOD

      Schedules the addition of partition jobs in a time series table. If TTL has been configured, PERIOD cannot be greater than TTL.

      Value range:

      1 hour to 1 year. The default value is 1 day.

  • 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 the tag columns, and a partitioned table is automatically created.

Examples

Create a simple time series table.

CREATE TABLE IF NOT EXISTS CPU( scope_name text TSTag, server_ip text TSTag, group_path text TSTag, time timestamptz TSTime, idle numeric TSField, system numeric TSField, util numeric TSField, vcpu_num numeric TSField, guest numeric TSField, iowait numeric TSField, users numeric TSField) with (orientation=TIMESERIES) distribute by hash(scope_name); CREATE TABLE CPU1( 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 TABLE CPU2 (LIKE CPU INCLUDING ALL);
close