SQL

You can execute SQL statements using MySQL or PostgreSQL clients, and access GreptimeDB using the MySQL or PostgreSQL protocol with any programming language of your preference, such as Java JDBC. We will use the monitor table as an example to show how to write data.

Create a table

Before inserting data, you need to create a table. For example, create a table named monitor:

  1. CREATE TABLE monitor (
  2.   host STRING,
  3.   ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP() TIME INDEX,
  4.   cpu FLOAT64 DEFAULT 0,
  5.   memory FLOAT64,
  6.   PRIMARY KEY(host));

The above statement will create a table with the following schema:

  1. +--------+----------------------+------+------+---------------------+---------------+
  2. | Column | Type                 | Key  | Null | Default             | Semantic Type |
  3. +--------+----------------------+------+------+---------------------+---------------+
  4. | host   | String               | PRI  | YES  |                     | TAG           |
  5. | ts     | TimestampMillisecond | PRI  | NO   | current_timestamp() | TIMESTAMP     |
  6. | cpu    | Float64              |      | YES  | 0                   | FIELD         |
  7. | memory | Float64              |      | YES  |                     | FIELD         |
  8. +--------+----------------------+------+------+---------------------+---------------+
  9. 4 rows in set (0.01 sec)

For more information about the CREATE TABLE statement, please refer to table management.

Insert data

Let’s insert some testing data to the monitor table. You can use the INSERT INTO SQL statements:

  1. INSERT INTO monitor
  2. VALUES
  3.     ("127.0.0.1", 1702433141000, 0.5, 0.2),
  4.     ("127.0.0.2", 1702433141000, 0.3, 0.1),
  5.     ("127.0.0.1", 1702433146000, 0.3, 0.2),
  6.     ("127.0.0.2", 1702433146000, 0.2, 0.4),
  7.     ("127.0.0.1", 1702433151000, 0.4, 0.3),
  8.     ("127.0.0.2", 1702433151000, 0.2, 0.4);
  1. Query OK, 6 rows affected (0.01 sec)

You can also insert data by specifying the column names:

  1. INSERT INTO monitor (host, ts, cpu, memory)
  2. VALUES
  3.     ("127.0.0.1", 1702433141000, 0.5, 0.2),
  4.     ("127.0.0.2", 1702433141000, 0.3, 0.1),
  5.     ("127.0.0.1", 1702433146000, 0.3, 0.2),
  6.     ("127.0.0.2", 1702433146000, 0.2, 0.4),
  7.     ("127.0.0.1", 1702433151000, 0.4, 0.3),
  8.     ("127.0.0.2", 1702433151000, 0.2, 0.4);

Through the above statement, we have inserted six rows into the monitor table.

For more information about the INSERT statement, please refer to INSERT.

Time zone

The time zone specified in the SQL client will affect the timestamp with a string format that does not have time zone information. The timestamp value will automatically have the client’s time zone information added.

For example, the following SQL set the time zone to +8:00:

  1. SET time_zone = '+8:00';

Then insert values into the monitor table:

  1. INSERT INTO monitor (host, ts, cpu, memory)
  2. VALUES
  3.     ("127.0.0.1", "2024-01-01 00:00:00", 0.4, 0.1),
  4.     ("127.0.0.2", "2024-01-01 00:00:00+08:00", 0.5, 0.1);

The first timestamp value 2024-01-01 00:00:00 does not have time zone information, so it will automatically have the client’s time zone information added. After inserting, it will be equivalent to the second value 2024-01-01 00:00:00+08:00.

The result in the +8:00 time zone client is as follows:

  1. +-----------+---------------------+------+--------+
  2. | host      | ts                  | cpu  | memory |
  3. +-----------+---------------------+------+--------+
  4. | 127.0.0.1 | 2024-01-01 00:00:00 |  0.4 |    0.1 |
  5. | 127.0.0.2 | 2024-01-01 00:00:00 |  0.5 |    0.1 |
  6. +-----------+---------------------+------+--------+

The result in the UTC time zone client is as follows:

  1. +-----------+---------------------+------+--------+
  2. | host      | ts                  | cpu  | memory |
  3. +-----------+---------------------+------+--------+
  4. | 127.0.0.1 | 2023-12-31 16:00:00 |  0.4 |    0.1 |
  5. | 127.0.0.2 | 2023-12-31 16:00:00 |  0.5 |    0.1 |
  6. +-----------+---------------------+------+--------+