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