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.

Create Database

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

sql

CREATE DATABASE test;

sql

Query OK, 1 row affected (0.05 sec)

You can list all the existing databases.

sql

SHOW DATABASES;

sql

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

Using like syntax:

sql

SHOW DATABASES LIKE 'p%';

sql

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

Then change the database:

sql

USE test;

Change back to public database:

sql

USE public;

Create 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.

sql

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

sql

Query OK, 0 row affected (0.03 sec)

NOTE

GreptimeDB does not currently support changing the data model of existing columns after a table has been created. Therefore, it is important to carefully design your data model before creating tables.

CREATE TABLE syntax

• Timestamp column: GreptimeDB is a time-series database system, a time-series column must be explicitly specified by TIME INDEX keyword when creating tables. The data type of time-series column can be both BIGINT or TIMESTAMP. If BIGINT is chosen as the data type for the time-series column, the inserted value of that column will be automatically converted to a timestamp in milliseconds.
• Primary key: primary key is used to uniquely define a series of data, which is similar to tags in other time-series systems like InfluxDB.
• Table options: when creating a table, you can specify a set of table options, click here for more details.

Describe Table

Show table information in detail:

sql

DESC TABLE monitor;

sql

+--------+----------------------+------+------+---------------------+---------------+
| 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.

List Existing Tables

You can use show tables statement to list existing tables

sql

SHOW TABLES;

sql

+------------+
| 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.

sql

SHOW TABLES LIKE monitor;

sql

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

List tables in other databases:

sql

SHOW TABLES FROM test;

sql

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

Alter Table

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

sql

ALTER TABLE monitor ADD COLUMN label VARCHAR;

sql

Query OK, 0 rows affected (0.03 sec)

sql

ALTER TABLE monitor DROP COLUMN label;

sql

Query OK, 0 rows affected (0.03 sec)

Notice: currently only adding/dropping columns is allowed, altering column definition will soon be supported.

Drop Table

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:

sql

DROP TABLE monitor;

sql

Query OK, 1 row affected (0.01 sec)

HTTP API

Using the following code to create a table through POST method:

shell

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 DOUBLE DEFAULT 0, memory DOUBLE, TIME INDEX (ts), PRIMARY KEY(host)) ENGINE=mito WITH(regions=1)' \
http://localhost:4000/v1/sql?db=public

json

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

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