All Products
Search
Document Center

API endpoint for TSQL queries

Last Updated: May 24, 2020

Request path and methods

Currently, you can access data only over HTTP in TSQL. To query data, you can use the /api/sqlquery API endpoint. The following table describes the request path and methods.

Request path Method Description
/api/sqlquery GET or POST You can use SQL statements to query time series data.

Request parameters

JSON-formatted request

Parameter Type Required Description Default value Example
sql String Yes The SQL query statement N/A select min( value) as minV, max(value) as maxV
from tsdb.cpu.usage_system`<br />where `timestamp`` between ‘2019-01-01’ and ‘2019-01-02’

SQL response parameters

JSON-formatted response

Parameter Type Description Example
columns An array of the string type The name of each column in the response. [“hostname”, “minV”, “maxV”]
metadata An array of the string type The data type of each column in the response. [“VARCHAR”, “FLOAT8”, “FLOAT8”]
rows An array of the map type The rows in the response. Each row is a map. The column names are tag keys, and the column values are tag values. [
{
“hostname”: “host_9”, “minV”: “90.49879988870993”, “maxV”: “93.8549962369351”},{
“hostname”: “host_8”, “minV”: “0.9174383579092883”, “maxV”: “5.997678861502513”},

]

Query examples

Assume that you want to retrieve the maximum and minimum values of the cpu.usage_system metric for each machine between 00:00:00 on March 01, 2019 and 00:01:00 on March 01, 2019. In this example, you can execute the following SQL statement:

  1. select hostname, min(`value`) as minV, max(`value`) as maxV
  2. from tsdb.`cpu.usage_system`
  3. where `timestamp` between '2019-03-01 00:00:00' and '2019-03-01 00:01:00'
  4. group by hostname

You can use the following two methods to submit the SQL statement to TSDB.

Specify the SQL query statement as an input parameter in the curl command

  1. curl -H "Content-Type: application/json" -d $'{"sql":"select hostname, min(`value`) as minV, max(`value`) as maxV from tsdb.`cpu.usage_system` where `timestamp` between \'2019-03-01 00:00:00\' and \'2019-03-01 00:01:00\' group by hostname"}' http://tsdb_host:tsdb_port/api/sqlquery

Notes:

  • In the preceding command line, the JSON string is enclosed in single quotation marks (‘). Therefore, if single quotation marks (‘) exist in the JSON string, you must use backslash () escape characters.
  • If you put the JSON string into a file, you do not need to use backslash () escape characters.

Put the SQL query statement in a JSON file

  1. //sql.json
  2. {"sql": "select hostname, min(`value`) as minV, max(`value`) as maxV from tsdb.`cpu.usage_system` where `timestamp` between '2019-03-01 00:00:00' and '2019-03-01 00:01:00' group by hostname"}

Run the curl command to submit the query.

  1. curl -H "Content-Type: application/json" -d @sql.json http://tsdb_host:tsdb_port/api/sqlquery

Query result

  1. {
  2. "columns": [
  3. "hostname",
  4. "minV",
  5. "maxV"
  6. ],
  7. "metadata": [
  8. "VARCHAR",
  9. "FLOAT8",
  10. "FLOAT8"
  11. ],
  12. "rows": [
  13. {
  14. "hostname": "host_9",
  15. "minV": "90.49879988870993",
  16. "maxV": "93.8549962369351"
  17. },
  18. {
  19. "hostname": "host_8",
  20. "minV": "0.9174383579092883",
  21. "maxV": "5.997678861502513"
  22. },
  23. {
  24. "hostname": "host_0",
  25. "minV": "0.12036918007593445",
  26. "maxV": "2.6224297271376256"
  27. },
  28. ....
  29. ]
  30. }