English

简体中文

English

简体中文

CREATE ​

CREATE is used to create new databases or tables.

CREATE DATABASE ​

Syntax ​

Creates a new database:

sql
CREATE DATABASE [IF NOT EXISTS] db_name
CREATE DATABASE [IF NOT EXISTS] db_name

If the db_name database already exists, then GreptimeDB has the following behaviors:

  • Doesn't create a new database.
  • Doesn't return an error when the clause IF NOT EXISTS is presented.
  • Otherwise, returns an error.

Examples ​

Creates a test database:

sql
CREATE DATABASE test;
CREATE DATABASE test;
sql
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.05 sec)

Creates it again with IF NOT EXISTS:

sql
CREATE DATABASE IF NOT EXISTS test;
CREATE DATABASE IF NOT EXISTS test;

CREATE TABLE ​

Syntax ​

Creates a new table in the db database or the current database in use:

sql
CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT expr1] [TIME INDEX] [PRIMARY KEY] COMMENT comment,
    name2 [type2] [NULL|NOT NULL] [DEFAULT expr2] [TIME INDEX] [PRIMARY KEY] COMMENT comment,
    ...,
    [TIME INDEX (name)],
    [PRIMARY KEY(name1, name2,...)]
) ENGINE = engine WITH([ttl | regions] = expr, ...)
[
  PARTITION BY RANGE COLUMNS(name1, name2, ...) (
    PARTITION r0 VALUES LESS THAN (expr1),
    PARTITION r1 VALUES LESS THAN (expr2),
    ...
  )
]
CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT expr1] [TIME INDEX] [PRIMARY KEY] COMMENT comment,
    name2 [type2] [NULL|NOT NULL] [DEFAULT expr2] [TIME INDEX] [PRIMARY KEY] COMMENT comment,
    ...,
    [TIME INDEX (name)],
    [PRIMARY KEY(name1, name2,...)]
) ENGINE = engine WITH([ttl | regions] = expr, ...)
[
  PARTITION BY RANGE COLUMNS(name1, name2, ...) (
    PARTITION r0 VALUES LESS THAN (expr1),
    PARTITION r1 VALUES LESS THAN (expr2),
    ...
  )
]

The table schema is specified by the brackets before the ENGINE. The table schema is a list of column definitions and table constraints. A column definition includes the column name, type, and options such as nullable or default values, etc. Please see below.

Table constraints ​

The table constraints contain the following:

  • TIME INDEX specifies the time index column, which always has one and only one column. It indicates the Timestamp type in the data model of GreptimeDB.
  • PRIMARY KEY specifies the table's primary key column, which indicates the Tag type in the data model of GreptimeDB. It cannot include the time index column, but it always implicitly adds the time index column to the end of keys.
  • The Other columns are Field columns in the data model of GreptimeDB.

NOTE

The PRIMARY KEY specified in the CREATE statement is not the primary key in traditional relational databases. Actually, The PRIMARY KEY in traditional relational databases is equivalent to the combination of PRIMARY KEY and TIME INDEX in GreptimeDB. In other words, the PRIMARY KEY and TIME INDEX together constitute the unique identifier of a row in GreptimeDB.

The statement won't do anything if the table already exists and IF NOT EXISTS is presented; otherwise returns an error.

Table options ​

Users can add table options by using WITH. The valid options contain the following:

OptionDescriptionValue
ttlThe storage time of the table dataString value, such as '60m', '1h' for one hour, '14d' for 14 days etc. Supported time units are: s / m / h / d
regionsThe region number of the tableInteger value, such as 1, 5, 10 etc.
write_buffer_sizeMemtable size of the tableString value representing a valid size, such as 32MB, 128MB, etc. The default value of this option is 32MB. Supported units are: MB / GB.

For example, to create a table with the storage data TTL(Time-To-Live) is seven days and region number is 10:

sql
CREATE TABLE IF NOT EXISTS temperatures(
  ts TIMESTAMP TIME INDEX,
  temperature DOUBLE DEFAULT 10,
) engine=mito with(ttl='7d', regions=10);
CREATE TABLE IF NOT EXISTS temperatures(
  ts TIMESTAMP TIME INDEX,
  temperature DOUBLE DEFAULT 10,
) engine=mito with(ttl='7d', regions=10);

Column options ​

GreptimeDB supports the following column options:

OptionDescription
NULLThe column value can be null.
NOT NULLThe column value can't be null.
DEFAULT exprThe column's default value is expr, which its result type must be the column's type
COMMENT commentThe column comment. It must be a string value

The table constraints TIME INDEX and PRIMARY KEY can also be set by column option, but they can only be specified once in column definitions. So you can't specify PRIMARY KEY for more than one column except by the table constraint PRIMARY KEY :

sql
CREATE TABLE system_metrics (
    host STRING PRIMARY KEY,
    idc STRING PRIMARY KEY,
    cpu_util DOUBLE,
    memory_util DOUBLE,
    disk_util DOUBLE,
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    TIME INDEX(ts)
);
CREATE TABLE system_metrics (
    host STRING PRIMARY KEY,
    idc STRING PRIMARY KEY,
    cpu_util DOUBLE,
    memory_util DOUBLE,
    disk_util DOUBLE,
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    TIME INDEX(ts)
);

Goes wrong:

sql
 Illegal primary keys definition: not allowed to inline multiple primary keys in columns options
 Illegal primary keys definition: not allowed to inline multiple primary keys in columns options
sql
CREATE TABLE system_metrics (
    host STRING,
    idc STRING,
    cpu_util DOUBLE,
    memory_util DOUBLE,
    disk_util DOUBLE,
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP TIME INDEX,
    PRIMARY KEY(host, idc),
);
CREATE TABLE system_metrics (
    host STRING,
    idc STRING,
    cpu_util DOUBLE,
    memory_util DOUBLE,
    disk_util DOUBLE,
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP TIME INDEX,
    PRIMARY KEY(host, idc),
);
sql
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

Region partition rules ​

TODO by MichaelScofield

CREATE EXTERNAL TABLE ​

Syntax ​

Creates a new file external table in the db database or the current database in use:

sql
CREATE EXTERNAL TABLE [IF NOT EXISTS] [<database>.]<table_name>
[
 (
    <col_name> <col_type> [NULL | NOT NULL] [COMMENT "<comment>"]
 )
]
[ WITH
 (
   LOCATION = 'url'
   [,FORMAT =  { csv | json | parquet } ]
   [,PATTERN = '<regex_pattern>' ]
   [,ENDPOINT = '<uri>' ]
   [,ACCESS_KEY_ID = '<key_id>' ]
   [,SECRET_ACCESS_KEY = '<access_key>' ]
   [,SESSION_TOKEN = '<token>' ]
   [,REGION = '<region>' ]
   [,ENABLE_VIRTUAL_HOST_STYLE = '<boolean>']
   ..
 )
]
CREATE EXTERNAL TABLE [IF NOT EXISTS] [<database>.]<table_name>
[
 (
    <col_name> <col_type> [NULL | NOT NULL] [COMMENT "<comment>"]
 )
]
[ WITH
 (
   LOCATION = 'url'
   [,FORMAT =  { csv | json | parquet } ]
   [,PATTERN = '<regex_pattern>' ]
   [,ENDPOINT = '<uri>' ]
   [,ACCESS_KEY_ID = '<key_id>' ]
   [,SECRET_ACCESS_KEY = '<access_key>' ]
   [,SESSION_TOKEN = '<token>' ]
   [,REGION = '<region>' ]
   [,ENABLE_VIRTUAL_HOST_STYLE = '<boolean>']
   ..
 )
]

Table options ​

OptionDescriptionRequired
LOCATIONExternal files locations, e.g., s3://<bucket>[<path>], /<path>/[<filename>]Required
FORMATTarget file(s) format, e.g., JSON, CSV, ParquetRequired
PATTERNUse regex to match files. e.g., *_today.parquetOptional

S3 ​

OptionDescriptionRequired
REGIONAWS region name. e.g., us-east-1.Required
ENDPOINTThe bucket endpointOptional
ACCESS_KEY_IDACCESS_KEY_ID Your access key ID for connecting the AWS S3 compatible object storage.Optional
SECRET_ACCESS_KEYYour secret access key for connecting the AWS S3 compatible object storage.Optional
ENABLE_VIRTUAL_HOST_STYLEIf you use virtual hosting to address the bucket, set it to "true".Optional
SESSION_TOKENYour temporary credential for connecting the AWS S3 service.Optional

Examples ​

You can create an external table without any columns definitions:

sql
CREATE EXTERNAL TABLE IF NOT EXISTS city WITH (location='/var/data/city.csv',format='csv');
CREATE EXTERNAL TABLE IF NOT EXISTS city WITH (location='/var/data/city.csv',format='csv');

Or

sql
CREATE EXTERNAL TABLE city (
            host string,
            ts int64,
            cpu float64 default 0,
            memory float64,
            TIME INDEX (ts),
            PRIMARY KEY(ts, host)
) WITH (location='/var/data/city.csv', format='csv');
CREATE EXTERNAL TABLE city (
            host string,
            ts int64,
            cpu float64 default 0,
            memory float64,
            TIME INDEX (ts),
            PRIMARY KEY(ts, host)
) WITH (location='/var/data/city.csv', format='csv');
©Copyright 2024 Greptime Inc. All Rights Reserved