SQL

GreptimeDB supports full SQL for you to query data from a database. Here are some query examples for the monitor so you can get familiar with using SQL alongside GreptimeDB functions.

For examples on how to create the monitor table and insert data into it, please refer to Table Management and Write Data.

SELECT

To select all the data from the monitor table, use the SELECT statement:

sql

  1. SELECT * FROM monitor;

The query result looks like the following:

sql

  1. +-----------+---------------------+------+--------+
  2. | host      | ts                  | cpu  | memory |
  3. +-----------+---------------------+------+--------+
  4. | 127.0.0.1 | 2022-11-03 03:39:57 |  0.1 |    0.4 |
  5. | 127.0.0.1 | 2022-11-03 03:39:58 |  0.5 |    0.2 |
  6. | 127.0.0.2 | 2022-11-03 03:39:58 |  0.2 |    0.3 |
  7. +-----------+---------------------+------+--------+
  8. 3 rows in set (0.00 sec)

Please refer to SELECT for more information.

Use Functions

You can use the count() function to get the number of all rows in the table:

sql

  1. SELECT count(*) FROM monitor;

sql

  1. +-----------------+
  2. | COUNT(UInt8(1)) |
  3. +-----------------+
  4. |               3 |
  5. +-----------------+

The avg() function returns the average value of a certain field:

sql

  1. SELECT avg(cpu) FROM monitor;

sql

  1. +---------------------+
  2. | AVG(monitor.cpu)    |
  3. +---------------------+
  4. | 0.26666666666666666 |
  5. +---------------------+
  6. 1 row in set (0.00 sec)

Please refer to Functions for more information.

Group By

You can use the GROUP BY clause to group rows that have the same values into summary rows. The average memory usage grouped by idc:

sql

  1. SELECT host, avg(cpu) FROM monitor GROUP BY host;

sql

  1. +-----------+------------------+
  2. | host      | AVG(monitor.cpu) |
  3. +-----------+------------------+
  4. | 127.0.0.2 |              0.2 |
  5. | 127.0.0.1 |              0.3 |
  6. +-----------+------------------+
  7. 2 rows in set (0.00 sec)

Please refer to GROUP BY for more information.

Time and Date Examples

Query Latest 5 Minutes of Data

sql

  1. SELECT * from monitor WHERE ts >= now() - INTERVAL '5 minutes';

Please refer to INTERVAL for more information.

Cast Number Literal to Timestamp

sql

  1. select * from monitor where ts > arrow_cast(1650252336408, 'Timestamp(Millisecond, None)')

This query casts the number literal 1650252336408 (Unix Epoch 2022-04-18 03:25:36.408 in millisecond resolution) to the timestamp type with millisecond precision.

Please refer to arrow_cast for more information.

Cast string literal to timestamp

sql

  1. select * from monitor where ts > '2022-07-25 10:32:16.408'::timestamp

This query uses the :: grammar to cast the string literal to the timestamp type. All the SQL types are valid to be in the position of timestamp.

Please refer to ::timestamp for more information.

Extract the day of the year from timestamp

sql

  1. MySQL [(none)]> SELECT date_part('DOY', '2021-07-01 00:00:00');

Output:

sql

  1. +----------------------------------------------------+
  2. | date_part(Utf8("DOY"),Utf8("2021-07-01 00:00:00")) |
  3. +----------------------------------------------------+
  4. |                                                182 |
  5. +----------------------------------------------------+
  6. 1 row in set (0.003 sec)

The DOY in the SQL statement is the abbreviation of day of the year. Please refer to date_part for more information.

HTTP API

Use POST method to query data:

shell

  1. curl -X POST \
  2.   -H 'authorization: Basic {{authorization if exists}}' \
  3.   -H 'Content-Type: application/x-www-form-urlencoded' \
  4.   -d 'sql=select * from monitor' \
  5. http://localhost:4000/v1/sql?db=public

The result is shown below:

json

  1. {
  2.   "code": 0,
  3.   "output": [
  4.     {
  5.       "records": {
  6.         "schema": {
  7.           "column_schemas": [
  8.             {
  9.               "name": "host",
  10.               "data_type": "String"
  11.             },
  12.             {
  13.               "name": "ts",
  14.               "data_type": "TimestampMillisecond"
  15.             },
  16.             {
  17.               "name": "cpu",
  18.               "data_type": "Float64"
  19.             },
  20.             {
  21.               "name": "memory",
  22.               "data_type": "Float64"
  23.             }
  24.           ]
  25.         },
  26.         "rows": [
  27.           ["127.0.0.1", 1667446797450, 0.1, 0.4],
  28.           ["127.0.0.1", 1667446798450, 0.5, 0.2],
  29.           ["127.0.0.2", 1667446798450, 0.2, 0.3]
  30.         ]
  31.       }
  32.     }
  33.   ],
  34.   "execution_time_ms": 0
  35. }

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