All Products
Search
Document Center

Time Series Database:TSQL query API

Last Updated:Nov 19, 2021

This topic describes how to use the Transact-SQL (TSQL) query API.

Request path and method

TSQL supports only HTTP access. You can use the /api/sqlquery API endpoint to request data. The following table describes the request path and method for the TSQL query API.

Request path

Request method

Description

/api/sqlquery

GET/POST

Executes SQL statements to query time series data.

Request parameters

Parameters in requests in the JSON format

Parameter

Type

Required

Description

Default value

Example

sql

string

Yes

An SQL statement.

N/A

select min( value) as minV, max(value) as maxVfrom tsdb.cpu.usage_system`<br />where `timestamp`` between '2019-01-01' and '2019-01-02'

Response parameters

Parameters in responses in the JSON format

Parameter

Type

Description

Example

columns

An array of the STRING type

The name of each column in the query result.

["hostname", "minV", "maxV"]

metadata

An array of the STRING type

The type of each column in the query result.

["VARCHAR", "FLOAT8", "FLOAT8"]

rows

An array of the map type

The query result rows. Each row is a map. In each row, the column name is key and the column value is value.

[{ "hostname": "host_9", "minV": "90.49879988870993", "maxV": "93.8549962369351"},{ "hostname": "host_8", "minV": "0.9174383579092883", "maxV": "5.997678861502513"},…]

Sample requests

Execute the following SQL statement to query the minimum and maximum values of the cpu.usage_system measurement from each server. The values are generated within 1 minute from 2019-03-01 00:00:00 to 2019-03-01 00:01:00.

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

You can use the following two methods to submit this SQL statement to Time Series Database (TSDB).

Use command line parameters in curl to specify the SQL statement.

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
Notice
  • In the preceding command line, the JSON strings are enclosed in single quotation marks ('). In this case, backlashes (\) are used to escape the single quotation marks (') in the JSON strings.

  • If you add the JSON strings to a file, you do not need to use the escape character.

Add the SQL statement to a JSON file

//sql.json
{"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"}

Use curl to submit the SQL statement.

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

Query result

{
  "columns": [
    "hostname",
    "minV",
    "maxV"
  ],
  "metadata": [
    "VARCHAR",
    "FLOAT8",
    "FLOAT8"
  ],
  "rows": [
    {
      "hostname": "host_9",
      "minV": "90.49879988870993",
      "maxV": "93.8549962369351"
    },
    {
      "hostname": "host_8",
      "minV": "0.9174383579092883",
      "maxV": "5.997678861502513"
    },
    {
      "hostname": "host_0",
      "minV": "0.12036918007593445",
      "maxV": "2.6224297271376256"
    },
   ....
  ]
}

Execute SQL statements to write data

TSQL is updated in TSDB High-availability Edition. You can execute SELECT statements to query data. You can also execute INSERT statements to write data to TSDB instances. This section describes how to write data in a TSDB instance.

Syntax

INSERT [INTO] tbl_name
    [(`col_name` [, `col_name`] ... `timestamp`)]
    { {VALUES | VALUE} (value_list) [, (value_list)] ...
    }

Format requirements

The INSERT syntax of TSQL is similar to that of standard SQL. The INSERT syntax must meet some special requirements based on the characteristics of TSDB databases. The col_name field in the INSERT syntax must meet the following requirements:

  • The timestamp field is a reserved field. A column named timestamp must be specified in the INSERT statement and the column type must be timestamp.

  • The columns that exclude the timestamp column must contain the time series prefixes. For example, tags and fields must contain the time series prefixes. The delimiter (:) is used to combine a prefix and a column name. The prefixes are not part of column names.

  • INSERT statements support only simple syntax. Complex SQL statements are not supported, such as the statements that include function expressions and INSERT from SELECT statements.

  • INSERT statements are executed to write multi-value data. When you use the RESTful API to query data that is written by TSQL statements, you must use the /api/mquery API endpoint to query the multi-value data.

Write examples

SQL sample

insert into tsdb.`sys.cpu.load` ('tag:hostname', 'tag:dc', 'timestamp', 'field:load1min', 'field:load5min', 'field:load15min') values ('host1', 'datacenter1', '2020-06-01 18:56:57.368', '1.0', '0.0', '0.0'), ('host1', 'datacenter1', '2020-06-01 18:57:57.368', '2.0', '0.0', '0.0')

Specify the SQL statement in a JSON file

//sql_insert.json
{"sql": "insert into tsdb.`sys.cpu.load` ('tag:hostname', 'tag:dc', 'timestamp', 'field:load1min', 'field:load5min', 'field:load15min') values ('host1', 'datacenter1', '2020-06-01 18:56:57.368', '1.0', '0.0', '0.0'), ('host1', 'datacenter1', '2020-06-01 18:57:57.368', '2.0', '0.0', '0.0') "}
//sql_query.json
{"sql": "select * from tsdb.`sys.cpu.load` limit 1"}

Execute SQL statements to write and query data by specifying command line parameters in curl

  • Submit the write.

    curl tsdb_host:tsdb_port/api/sqlquery -d @sql_insert.json
  • Submit the query.

    curl tsdb_host:tsdb_port/api/sqlquery -d @sql_query.json

    Query result

{
  "columns": [
    "dc",
    "hostname",
    "timestamp",
    "load1min",
    "load15min",
    "load5min"
  ],
  "metadata": [
    "VARCHAR",
    "VARCHAR",
    "TIMESTAMP",
    "VARCHAR",
    "VARCHAR",
    "VARCHAR"
  ],
  "rows": [
    {
      "dc": "datacenter1",
      "hostname": "host1",
      "timestamp": "2020-06-01 17:56:57.368",
      "load1min": "1.0",
      "load15min": "0.0",
      "load5min": "0.0"
    }
  ]
}

Other limits

  • TSQL supports only the /api/sqlquery API endpoint and INSERT statements. TSQL statements that delete data and create tables are not supported.

  • You can use TSQL in the future version of TSDB High-availability Edition to write data.