Table Management
Introduction
GreptimeDB provides table management functionalities via SQL. The following guide uses MySQL Command-Line Client to demonstrate it.
MySQL
You can use standard MySQL client to connect to a running GreptimeDB instance.
$ mysql -h 127.0.0.1 -P 4002
mysql>
PostgreSQL client is supported too.
Creating a database
The default database is public
, you can create a database:
CREATE DATABASE test;
Query OK, 1 row affected (0.05 sec)
List 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)
Creating a Table
In this example, we are going to create a table named monitor
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);
Query OK, 1 row affected (0.03 sec)
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 bothBIGINT
orTIMESTAMP
. IfBIGINT
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.
Creating a table in other database
GreptimeDB doesn't support USE [DATABASE]
statement at the moment, so you must use [database].[table]
as the table name to create or manipulate a table in other databases:
CREATE TABLE test.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);
Query OK, 1 row affected (0.03 sec)
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: script
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)
Describe Table
Show table information in detail:
DESC TABLE monitor;
+--------+-----------+------+---------------------+---------------+
| Field | Type | Null | Default | Semantic Type |
+--------+-----------+------+---------------------+---------------+
| host | String | NO | | PRIMARY KEY |
| ts | Timestamp | NO | current_timestamp() | TIME INDEX |
| cpu | Float64 | NO | 0 | VALUE |
| memory | Float64 | NO | | VALUE |
+--------+-----------+------+---------------------+---------------+
4 rows in set (0.01 sec)
Alter Table
You can alter the schema of existing tables just like in MySQL database
alter table monitor add label varchar;
Query OK, 0 rows affected (0.03 sec)
alter table monitor drop column label;
Query OK, 0 rows affected (0.03 sec)
Notice: currently only adding/dropping columns is allowed, altering column definition will soon be supported.