SQL

We will use the monitor table as an example to show how to write data. For the SQL example on how to create the monitor table, 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.

HTTP API

Using POST method to insert data:

  1. curl -X POST \
  2.   -H 'authorization: Basic {{authorization if exists}}' \
  3.   -H 'Content-Type: application/x-www-form-urlencoded' \
  4.   -d 'sql=INSERT INTO monitor VALUES ("127.0.0.1", 1667446797450, 0.1, 0.4), ("127.0.0.2", 1667446798450, 0.2, 0.3), ("127.0.0.1", 1667446798450, 0.5, 0.2)' \
  5. http://localhost:4000/v1/sql?db=public

The result is shown below:

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

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

Update data

You can update data by inserting data with the same tag and time index as the existing data. For example, first, we can insert a new row into the monitor table:

  1. INSERT INTO monitor (host, ts, cpu, memory)
  2. VALUES
  3.     ("127.0.0.1", 1702433141000, 0.8, 0.1);

As described in the Create Table section, the host column represents the tag and the ts column represents the time index. To update the data, you can use the same host and ts values as the existing data and set the new cpu value to 0.5:

  1. INSERT INTO monitor (host, ts, cpu, memory)
  2. VALUES
  3.     -- The same tag `127.0.0.1` and the same time index 1702433141000
  4.     ("127.0.0.1", 1702433141000, 0.5, 0.1);

The new data is:

  1. +-----------+---------------------+------+--------+
  2. | host      | ts                  | cpu  | memory |
  3. +-----------+---------------------+------+--------+
  4. | 127.0.0.1 | 2023-12-13 02:05:41 |  0.5 |    0.1 |
  5. +-----------+---------------------+------+--------+

Note that you cannot omit the other columns in the INSERT INTO statement if you only want to update one column. If you omit the other columns, they will be overwritten with the default values. For example:

  1. INSERT INTO monitor (host, ts, cpu)
  2. VALUES
  3.     ("127.0.0.1", 1702433141000, 0.5);

The default value of the memory column in the monitor table is NULL. Therefore, the new data will be:

  1. +-----------+---------------------+------+--------+
  2. | host      | ts                  | cpu  | memory |
  3. +-----------+---------------------+------+--------+
  4. | 127.0.0.1 | 2023-12-13 02:05:41 |  0.5 |   NULL |
  5. +-----------+---------------------+------+--------+

Delete data

To delete a row from it by tag host and timestamp index ts:

  1. DELETE FROM monitor WHERE host='127.0.0.2' and ts=1667446798450;
  1. Query OK, 1 row affected (0.00 sec)

For more information about the DELETE statement, please refer to the SQL DELETE.

HTTP API

Using POST method to delete data:

  1. curl -X POST \
  2.   -H 'authorization: Basic {{authorization if exists}}' \
  3.   -H 'Content-Type: application/x-www-form-urlencoded' \
  4.   -d "sql=DELETE FROM monitor WHERE host = '127.0.0.2' and ts = 1667446798450" \
  5. http://localhost:4000/v1/sql?db=public

The result is shown below:

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

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

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