更新时间:2020-06-03 11:28
TSQL当前仅支持通过http 协议方式访问,通过/api/sqlquery 接口进行数据请求。接口请求参数和协议介绍如下:
请求路径 | 请求方法 | 描述 |
---|---|---|
/api/sqlquery | GET/POST | 以SQL语句查询时序数据 |
名字 | 类型 | 是否必需 | 描述 | 默认值 | 举例 |
---|---|---|---|---|---|
sql | string | 是 | 一个SQL查询语句 | 无 | select min( value ) as minV, max(value ) as maxVfrom tsdb. cpu.usage_system`<br /> where `timestamp`` between ‘2019-01-01’ and ‘2019-01-02’ |
名字 | 类型 | 描述 | 举例 |
---|---|---|---|
columns | string 类型的数组 | 查询结果每个column的列名 | [“hostname”, “minV”, “maxV”] |
metadata | string 类型的数组 | 查询结果每个column的列类型 | [“VARCHAR”, “FLOAT8”, “FLOAT8”] |
rows | map 类型的数组 | 查询结果行,每行是一个map, 列名是key, 列的值是value | [ { “hostname”: “host_9”, “minV”: “90.49879988870993”, “maxV”: “93.8549962369351”},{ “hostname”: “host_8”, “minV”: “0.9174383579092883”, “maxV”: “5.997678861502513”}, … ] |
假设我们要执行下面的查询,获得cpu.usage_system这个度量在’2019-03-01 00:00:00’ 到 ‘2019-03-01 00:01:00’ 这1分钟之内的在每台机器上的最小值和最大值,可以用下面的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
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
注意:
//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"}
用curl提交查询
curl -H "Content-Type: application/json" -d @sql.json http://tsdb_host:tsdb_port/api/sqlquery
{
"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"
},
....
]
}
TSDB 高可用版本对TSQL 进行了升级。 除了SELECT 查询SQL,还支持通过insert SQL 写入数据到TSDB。详细内如如下
INSERT [INTO] tbl_name
[(`col_name` [, `col_name`] ... `timestamp`)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ...
}
insert 语法和标准SQL大体相同,但是根据时序数据库的特点有某些特殊要求。主要体现在col_name列名的命名上:
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 语句放在json 文件上
//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"}
curl tsdb_host:tsdb_port/api/sqlquery -d @sql_insert.json
curl tsdb_host:tsdb_port/api/sqlquery -d @sql_query.json
{
"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"
}
]
}
在文档使用中是否遇到以下问题
更多建议
匿名提交