Table Management

Data Model should be read before this guide.

GreptimeDB provides table management functionalities via SQL. The following guide uses MySQL Command-Line Client to demonstrate it.

For more explanations of the SQL syntax, please see the SQL reference.

Create a database

The default database is public. You can create a database manually.

CREATE DATABASE test;

Query OK, 1 row affected (0.05 sec)

Create a database with a TTL (Time-To-Live) of seven days, which means all the tables in this database will inherit this option if they don’t have their own TTL setting:

CREATE DATABASE test with(ttl='7d');

You can list all the existing databases.

SHOW DATABASES;

+---------+
| Schemas |
+---------+
| test    |
| public  |
+---------+
2 rows in set (0.00 sec)

Using like syntax:

SHOW DATABASES LIKE 'p%';

+---------+
| Schemas |
+---------+
| public  |
+---------+
1 row in set (0.00 sec)

Then change the database:

USE test;

Change back to public database:

USE public;

Create a table

NOTE

GreptimeDB offers a schemaless approach to writing data that eliminates the need to manually create tables using additional protocols. See Automatic Schema Generation.

You can still create a table manually via SQL if you have specific requirements. Suppose we want to create a table named monitor with the following data model:

• host is the hostname of the collected standalone machine, which should be a Tag that used to filter data when querying.
• ts is the time when the data is collected, which should be the Timestamp. It can also used as a filter when querying data with a time range.
• cpu and memory are the CPU utilization and memory utilization of the machine, which should be Field columns that contain the actual data and are not indexed.

The SQL code for creating the table is shown below. In SQL, we use the primary key to specify Tags and the TIME INDEX to specify the Timestamp column. The remaining columns are Fields.

CREATE TABLE monitor (
  host STRING,
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() TIME INDEX,
  cpu FLOAT64 DEFAULT 0,
  memory FLOAT64,
  PRIMARY KEY(host));

Query OK, 0 row affected (0.03 sec)

Create the table with a TTL (Time-To-Live) of seven days:

CREATE TABLE monitor (
  host STRING,
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() TIME INDEX,
  cpu FLOAT64 DEFAULT 0,
  memory FLOAT64,
  PRIMARY KEY(host)
) ENGINE=mito WITH (ttl='7d');

NOTE

GreptimeDB does not currently support changing the TIME INDEX after a table has been created. Therefore, it is important to carefully choose your TIME INDEX column before creating tables.

CREATE TABLE syntax

• Timestamp column: GreptimeDB is a time-series database system, a timestamp column must be explicitly specified by TIME INDEX keyword when creating tables. The data type of the timestamp column must be TIMESTAMPtype.
• Primary key: The columns in primary key are similar to tags in other other time-series systems like InfluxDB. The primary key columns with the time index column are used to uniquely define a series of data, which is similar to time series like InfluxDB.
• Table options: when creating a table, you can specify a set of table options, click here for more details.

Describe a table

Show table information in detail:

DESC TABLE monitor;

+--------+----------------------+------+------+---------------------+---------------+
| Column | Type                 | Key  | Null | Default             | Semantic Type |
+--------+----------------------+------+------+---------------------+---------------+
| host   | String               | PRI  | YES  |                     | TAG           |
| ts     | TimestampMillisecond | PRI  | NO   | current_timestamp() | TIMESTAMP     |
| cpu    | Float64              |      | YES  | 0                   | FIELD         |
| memory | Float64              |      | YES  |                     | FIELD         |
+--------+----------------------+------+------+---------------------+---------------+
4 rows in set (0.01 sec)

The Semantic Type column describes the data model of the table. The host is a Tag column, ts is a Timestamp column, and cpu and memory are Field columns.

Show Table Definition and Indexes

Use SHOW CREATE TABLE table_name to get the statement when creating the table:

SHOW CREATE TABLE monitor;

+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                              |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| monitor | CREATE TABLE IF NOT EXISTS `monitor` (
  `host` STRING NULL,
  `ts` TIMESTAMP(3) NOT NULL DEFAULT current_timestamp(),
  `cpu` DOUBLE NULL DEFAULT 0,
  `memory` DOUBLE NULL,
  TIME INDEX (`ts`),
  PRIMARY KEY (`host`)
)
ENGINE=mito
 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

List all indexes in a table:

SHOW INDEXES FROM monitor;

+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+----------------------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type                 | Comment | Index_comment | Visible | Expression |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+----------------------------+---------+---------------+---------+------------+
| monitor |          1 | PRIMARY    |            1 | host        | A         |        NULL |     NULL |   NULL | YES  | greptime-inverted-index-v1 |         |               | YES     |       NULL |
| monitor |          1 | TIME INDEX |            1 | ts          | A         |        NULL |     NULL |   NULL | NO   | greptime-inverted-index-v1 |         |               | YES     |       NULL |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+----------------------------+---------+---------------+---------+------------+

For more info about SHOW statement, please read the SHOW reference.

List Existing Tables

You can use show tables statement to list existing tables

SHOW TABLES;

+------------+
| Tables     |
+------------+
| monitor    |
| scripts    |
+------------+
3 rows in set (0.00 sec)

Notice: scripts table is a built-in table that holds User-Defined Functions (UDFs). Currently only table name filtering is supported. You can filter existing tables by their names.

SHOW TABLES LIKE monitor;

+---------+
| Tables  |
+---------+
| monitor |
+---------+
1 row in set (0.00 sec)

List tables in other databases:

SHOW TABLES FROM test;

+---------+
| Tables  |
+---------+
| monitor |
+---------+
1 row in set (0.01 sec)

Alter a table

You can alter the schema of existing tables just like in MySQL database

ALTER TABLE monitor ADD COLUMN label VARCHAR;

Query OK, 0 rows affected (0.03 sec)

ALTER TABLE monitor DROP COLUMN label;

Query OK, 0 rows affected (0.03 sec)

The ALTER TABLE statement also supports adding, removing, and renaming columns, as well as modifying the column datatype, etc. Please refer to the ALTER reference for more information.

Drop a table

danger

DROP TABLE cannot be undone. Use it with care!

DROP TABLE [db.]table is used to drop the table in db or the current database in-use.Drop the table test in the current database:

DROP TABLE monitor;

Query OK, 1 row affected (0.01 sec)

Drop a database

danger

DROP DATABASE cannot be undone. Use it with care!

You can use DROP DATABASE to drop a database. For example, to drop the test database:

DROP DATABASE test;

Please refer to the DROP document for more details.

HTTP API

You can execute the SQL statements through the HTTP API. For example, using the following code to create a table through POST method:

curl -X POST \
  -H 'authorization: Basic {{authorization if exists}}' \
  -H 'Content-Type: application/x-www-form-urlencoded' \
  -d 'sql=CREATE TABLE monitor (host STRING, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP(), cpu FLOAT64 DEFAULT 0, memory FLOAT64, TIME INDEX (ts), PRIMARY KEY(host)) ENGINE=mito' \
http://localhost:4000/v1/sql?db=public

{ "code": 0, "output": [{ "affectedrows": 1 }], "execution_time_ms": 10 }

For more information about SQL HTTP request, please refer to API document.

Time zone

The specified time zone in the SQL client session will affect the default timestamp value when creating or altering a table. If you set the default value of a timestamp column to a string without a time zone, the client’s time zone information will be automatically added.

For more information about the effect of the client time zone, please refer to the time zone section in the write data document.