Version: latest

DDL

Used to define the structure and organization of the database.

`CREATE DATABASE`

CREATE DATABASE [IF NOT EXISTS] db_name [WITH db_options];

db_options:
    db_option ...

db_option: {
      TTL duration
    | SHARD value
    | VNODE_DURATION duration
    | REPLICA value
    | PRECISION {'ms' | 'us' | 'ns'}
    | MAX_MEMCACHE_SIZE bytesnum
    | MEMCACHE_PARTITIONS value
    | WAL_MAX_FILE_SIZE bytesnum
    | WAL_SYNC bool
    | STRICT_WRITE bool 
    | MAX_CACHE_READERS value
}

Options	Description
`TTL`	Duration。Data expiration time, default is infinite `INF`.
`SHARD`	Indicates the number of shards, default: `1`.
`VNODE_DURATION`	Duration。The time window length of the data in SHARD, default `'1y'`.
`REPLICA`	The number of replicas of the data in the cluster, default is `1`.(Note: The number of copies must be less than or equal to the data nodes of `tskv` in the cluster).
`PRECISION`	The timestamp precision of the database, default is `ns`.Supported units: `ms`, `us`, `ns`
`MAX_MEMCACHE_SIZE`	The maximum cache size of the database, default is `'512MiB'`, you can use the configuration file to specify the default value each time it is created.
`MEMCACHE_PARTITIONS`	The cache partition number of the database, default is `1`, you can use the configuration file to specify the default value each time it is created.
`WAL_MAX_FILE_SIZE`	The maximum size of a single WAL file, default is `'1GiB'`, you can use the configuration file to specify the default value each time it is created.
`WAL_SYNC`	Whether WAL is synchronized every time it is written, default is `'false'`, you can use the configuration file to specify the default value each time it is created.
`STRICT_WRITE`	Whether to enable strict writing, that is, whether writing requires pre-creation of the table by default is `'false'`, you can use the configuration file to specify the default value each time it is created.
`MAX_CACHE_READERS`	Maximum buffer TSM reader for vnode, default value is `32`, can specify default value each time it is created using configuration file.

Duration format

'inf'为无限大的 Duration, 例如 create database oceanic_station with ttl 'inf'
'nanos' | 'nsec' | 'ns' 为纳秒, 例如 with ttl '10ns'
'usec' | 'us' 为微秒, 例如 with ttl '300us'
'millis' | 'msec' | 'ms' 为毫秒, 例如 with ttl '90ms'
'seconds' | 'second' | 'secs' | 'sec' | 's' 为秒, 例如 with ttl '30s'
'minutes' | 'minute' | 'min' | 'mins' | 'm' 为分钟, 例如 with ttl '7000m'
'hours' | 'hour' | 'hr' | 'hrs' | 'h' 为小时, 例如 with ttl '5h'
'days' | 'day' | 'd' 为天, 例如 with ttl '365d'
'weeks' | 'week' | 'w' 为周, 例如 with ttl '52w'
'months' | 'month' | 'M' 为月, 例如 with ttl '12M'
'years' | 'year' | 'y' 为年, 例如 with ttl '1y'

BytesNum

'512MiB'、'1GiB'、'1KiB'、'1TiB'、'1PiB'、'1EiB'、'1ZiB'、'1YiB', 为二进制单位, 1M = 1024K
'512MB'、'1GB'、'1KB'、'1TB'、'1PB'、'1EB'、'1ZB'、'1YB'，为十进制单位, 1M = 1000K

View the CREATE DATABASE example

Create a database, and retain the policy as unlimited length.

CREATE DATABASE oceanic_station;

Create a database, specifying parameters.

create database oceanic_station with ttl 'inf' shard 6 vnode_duration '2y1M' replica 1 precision 'us' max_memcache_size '128MiB' memcache_partitions 10 wal_max_file_size '300M' wal_sync 'true' strict_write 'true' max_cache_readers 100;

Create a database, set the expiration time to 180 days, and each time window to 7 days.

For data expiration policy, please refer to Sharding Rules

CREATE DATABASE oceanic_station WITH TTL '180d' SHARD 1 VNODE_DURATION '7d';

Set the Vnode replication factor quantity.

CREATE DATABASE oceanic_station WITH SHARD 2;

Set the timestamp precision.

The time precision can only be specified when creating the database and cannot be changed afterwards.

CREATE DATABASE oceanic_station WITH PRECISION 'ms';

`CREATE TABLE`

CREATE TABLE uses CnosDB TSKV to store data, supporting storage of some SQL data types as follows:

BIGINT
BIGINT UNSIGNED
BOOLEAN
TIMESTAMP
STRING
DOUBLE

In addition, the structure of the CnosDB table needs to follow certain specifications, please refer to Basic Concepts.

CREATE TABLE [IF NOT EXISTS] tb_name
(field_definition [, field_definition ] ... [, TAGS(tg_name [, tg_name] ...)]);

field_definition:
    column_name data_type [field_codec_type]
    
field_codec_type:
    CODEC(code_type)

View the CREATE TABLE example

Create a table.

The time field can be omitted when creating a table.

CREATE TABLE air(
    visibility DOUBLE,
    temperature DOUBLE,
    pressure DOUBLE,
    TAGS(station)
);

Create a table and specify the compression algorithm.

Specify the compression algorithm of visibility as QUANTILE, do not compress temperature, and use the default compression algorithm for pressure.

Different data types can specify different compression algorithms, for supported list please refer to Compression Algorithm.

CREATE TABLE air(
    visibility DOUBLE CODEC(QUANTILE),
    temperature DOUBLE CODEC(NULL),
    pressure DOUBLE,
    TAGS(station)
);

If you need to perform other operations on the table created, please refer to INSERT, ALTER TABLE, DROP TABLE.

`CREATE EXTERNAL TABLE`

The external table is read-only and cannot perform DML operations.

-- Column definitions can not be specified for PARQUET files

CREATE EXTERNAL TABLE [ IF NOT EXISTS ] tb_name 
    ( field_definition [, field_definition] ... ) tb_option;

field_definition:
    column_name data_type [ NULL ]

tb_option: {
      STORED AS { PARQUET | NDJSON | CSV | AVRO }
    | [ WITH HEADER ROW ]
    | [ DELIMITER 'a_single_char' ]
    | [ PARTITIONED BY ( column_name, [, ... ] ) ]
    | LOCATION '<file>'
}

Options	Description
`STORED AS`	Specify the format of the file, supporting `PARQUET`, `NDJSON`, `CSV`, `AVRO`.
`WITH HEADER ROW`	Only effective when `STORED AS CSV`, used to specify the `Header` in the `CSV` file.
`DELIMITER`	Only effective when `STORED AS CSV`, used to specify the delimiter in the `CSV` file.
`PARTITIONED BY`	Partitioning by the columns specified when creating the table.
`LOCATION`	Location of the associated file, supports directories.

View the CREATE EXTERNAL TABLE example

Create an external table and specify a local CSV file.

External tables can use all data types (excluding INTERVAL and some other special types) and are not constrained by the CnosDB native model.

CREATE EXTERNAL TABLE cpu (
     cpu_hz  DECIMAL(10,6) NOT NULL,
     temp  DOUBLE NOT NULL,
     version_num  BIGINT NOT NULL,
     is_old  BOOLEAN NOT NULL,
     weight  DECIMAL(12,7) NOT NULL
)
STORED AS CSV
WITH HEADER ROW
LOCATION 'tests/data/csv/cpu.csv';

`CREATE STREAM TABLE`

tip

Need a source table, STREAM table does not support ALTER

CREATE STREAM TABLE [IF NOT EXISTS] table_name[(field_definition [, field_definition] ...)]
    WITH (db = 'db_name', table = 'table_name', event_time_column = 'time_column')
    engine = tskv;

field_definition: 
    column_name data_type

Syntax:DROP TABLE

`ALTER DATABASE`

ALTER DATABASE can modify all parameters set in the database (excluding PRECISION), you can query the current database parameter settings using the DESCRIBE DATABASE command.

ALTER DATABASE db_name [alter_db_options]

alter_db_options:
    SET db_option

db_option: {
      TTL value
    | SHARD value
    | VNODE_DURATION value
    | REPLICA value
}

View the ALTER DATABASE example

Modify TTL.

ALTER DATABASE oceanic_station SET TTL '30d';

**Modify VNODE_DURATION **.

Modifying VNODE_DURATION will not affect existing Vnodes.

ALTER DATABASE oceanic_station SET VNODE_DURATION '7d';

`ALTER TABLE`

tip

Support modifying/deleting columns of TAG and FIELD types, column types can be queried using the DESCRIBE TABLE syntax.

Do not support modifying the column name time.

Avoid performing write operations when renaming columns of type TAG, as it may cause conflicts in series.

ALTER TABLE tb_name alter_table_option;

alter_table_option: {
      ADD TAG col_name
    | ADD FIELD col_name [data_type] [CODEC(code_type)]
    | ALTER col_name SET CODEC(code_type)
    | DROP col_name
    | RENAME COLUMN col_name TO new_col_name
}

View the ALTER TABLE example

Add a column of type TAG.

ALTER TABLE air ADD TAG height;

Add a column of type FIELD and specify the compression algorithm.

ALTER TABLE air ADD FIELD humidity DOUBLE CODEC(DEFAULT);

Modify the compression algorithm of humidity to QUANTILE.

ALTER TABLE air ALTER humidity SET CODEC(QUANTILE);

Delete humidity.

ALTER TABLE air DROP humidity;

Rename the column name.

ALTER TABLE air RENAME COLUMN height to height_v2;

`DROP DATABASE`

DROP DATABASE [IF EXISTS] db_name [AFTER <duration>];

Options	Description
`AFTER`	Set the delay deletion time. When set to `AFTER`, the database will enter the disabled phase until the duration ends. The database will be removed from the disk. Before the duration ends, recovery can be done using the `RECOVER DATABASE` syntax.The default unit is `d`, supporting `d`, `h`, `m`

View the DROP DATABASE example

Delete the database, and the database will be immediately deleted.

DROP DATABASE oceanic_station;

Delete the database and set it to be deleted after 3 days.

DROP DATABASE oceanic_station AFTER '3';

Revoke the deletion of the database

The deletion operation can be revoked before the database is actually deleted, i.e., before the AFTER duration ends.

RECOVER DATABASE oceanic_station;

`DROP TABLE`

DROP TABLE [ IF EXISTS ] tb_name;

View the DROP TABLE example

DROP TABLE air;

DDL

CREATE DATABASE​

Duration format​

BytesNum​

CREATE TABLE​

CREATE EXTERNAL TABLE​

CREATE STREAM TABLE​

ALTER DATABASE​

ALTER TABLE​

DROP DATABASE​

DROP TABLE​