TSQL应用开发步骤示例
这里简要介绍如何在Python, Java应用程序里,通过TSQL查询并获取TSDB数据库的数据。
第一步:申请TSDB实例,并写入时序数据
第二步:参考下面的demo程序,作相应的改动
需要的改动
- 修改程序中host和port,以第一步申请的TSDB实例的host和port取代
- 针对写入的时序数据,构造一个SQL查询语句,替换程序中的相应变量。
Python
import json
import requests
## 需要修改:根据你申请的TSDB实例的host和port,修改下面两个变量
host="your_tsdb_instance_host"
port=3242
## http service的URL: http://host:port/api/sqlquery
endpoint_sqlquery = 'http://{0}:{1}/api/sqlquery'.format(host,port)
## 函数execSqlQuery接受一个SQL Select 语句,sqlquery是一个String。提交到TSDB,并获取结果
def execSqlQuery(sqlquery):
## 构造http request中的JSON string
query = {
"sql": sqlquery
}
## 提交http request, 以GET的方式
response = requests.get(endpoint_sqlquery, data=json.dumps(query), headers={'Content-Type':'application/json'})
if response.status_code != 200:
## 查询执行失败,显示错误代码和错误信息。
print ('Failed to execute query. POST /api/sqlquery {}, {}'.format(response.status_code, response.content))
else:
print ("Query is successful.")
## 把response反序列化成一个python的JSON object
parsed = json.loads(response.content)
## 你可以对这个查询结果的JSON作进一步的处理,这里,我们仅仅是打印出来。
print (json.dumps(parsed, indent=2, sort_keys=True))
## 这是一个 demo sql语句。根据TSDB SQL查询的文档构造合适的SQL查询语句
sqlquery = "select * from tsdb.`cpu.usage_system` where `timestamp` between '2019-03-01 00:00:00' and '2019-03-01 00:00:10'"
execSqlQuery(sqlquery)
执行上面的python程序:
python demoSql.py
下面是一个成功执行后的部分查询结果
Query is successful.
{
"columns": [
"hostname",
"rack",
"service_environment",
"os",
"service",
"datacenter",
"arch",
"service_version",
"team",
"region",
"timestamp",
"value"
],
"metadata": [
"VARCHAR",
"VARCHAR",
"VARCHAR",
"VARCHAR",
"VARCHAR",
"VARCHAR",
"VARCHAR",
"VARCHAR",
"VARCHAR",
"VARCHAR",
"TIMESTAMP",
"FLOAT8"
],
"rows": [
{
"arch": "x86",
"datacenter": "us-east-1b",
"hostname": "host_9",
"os": "Ubuntu16.10",
"rack": "7",
"region": "us-east-1",
"service": "14",
"service_environment": "production",
"service_version": "0",
"team": "LON",
"timestamp": "2019-03-01T00:00",
"value": "90.49879988870993"
},
{
"arch": "x86",
"datacenter": "us-east-1b",
"hostname": "host_9",
"os": "Ubuntu16.10",
"rack": "7",
"region": "us-east-1",
"service": "14",
"service_environment": "production",
"service_version": "0",
"team": "LON",
"timestamp": "2019-03-01T00:00:10",
"value": "91.55436144098181"
},
...
]
Java
首先,需要加入httpclient和gson. 我们使用httpclient来提交http的request, 而gson库用来JSON的序列化和反序列化。假设用的是maven来管理java 项目,在项目的pom.xml加入依赖
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpclient</artifactId>
<version>4.5.3</version>
</dependency>
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>2.8.1</version>
</dependency>
样本程序
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import org.apache.http.HttpResponse;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.HttpClientBuilder;
import org.apache.http.util.EntityUtils;
import java.util.Collection;
import java.util.List;
import java.util.Map;
public class TsqlDemo {
public static void main(String[] args) throws Exception {
// 需要修改:根据你申请的TSDB实例的host和port,修改下面两个变量
String host = "ts-uf6w8b6s6fuse9fua.hitsdb.rds.aliyuncs.com";
int port = 3242;
// 这是一个 demo sql语句。根据TSDB SQL查询的文档构造合适的SQL查询语句
String sql = "select * from tsdb.`cpu.usage_system` " +
"where `timestamp` between '2019-03-01 00:00:00' and '2019-03-01 00:00:10'";
// 提交SQL查询,并获得查询结果
Result result = execSqlQuery(host, port, sql);
System.out.println("Submitted query:" + sql);
// 把查询结果以JSON的形式打印出来
Gson gson = new GsonBuilder().setPrettyPrinting().create();
System.out.println("Query Result: \n" + gson.toJson(result));
}
private static Result execSqlQuery(String host, int port, String sql) throws Exception {
// http service的URL: http://host:port/api/sqlquery
String url = String.format("http://%s:%d/api/sqlquery", host, port);
// 构造一个Java POJO Query instance。JSON序列化后成为一个JSON格式的字符串
Query query = new Query(sql);
Gson gson = new Gson();
HttpClient httpClient = HttpClientBuilder.create().build();
HttpPost post = new HttpPost(url);
// postingString 就是query被JSON序列化后的JSON格式的字符串
StringEntity postingString = new StringEntity(gson.toJson(query));
post.setEntity(postingString);
post.setHeader("Content-type", "application/json");
// 以POST的形式,提交到url,
HttpResponse resp = httpClient.execute(post);
if (resp.getStatusLine().getStatusCode() != 200) {
throw new RuntimeException("Failed to execute query. Error:" + EntityUtils.toString(resp.getEntity()));
} else {
String resStr = EntityUtils.toString(resp.getEntity());
// /api/sqlquery的response包含的是一个JSON格式的字符串,我们通过Result这个Java POJO来反序列化成一个Result的instance.
Result result = gson.fromJson(resStr, Result.class);
return result;
}
}
// JAVA POJO, 用于构造一个Query
public static class Query {
String sql;
public Query(String sql) {
this.sql = sql;
}
}
// JAVA POJO, 用于查询结果的反序列化
public static class Result {
public Collection<String> columns;
public List<String> metadata;
public List<Map<String, String>> rows;
public Result(Collection<String> columns, List<String> metadata, List<Map<String, String>> rows) {
this.columns = columns;
this.metadata = metadata;
this.rows = rows;
}
}
}
执行的结果(部分)
Submitted query:select * from tsdb.`cpu.usage_system` where `timestamp` between '2019-03-01 00:00:00' and '2019-03-01 00:00:10'
Query Result:
{
"columns": [
"hostname",
"rack",
"service_environment",
"os",
"service",
"datacenter",
"arch",
"service_version",
"team",
"region",
"timestamp",
"value"
],
"metadata": [
"VARCHAR",
"VARCHAR",
"VARCHAR",
"VARCHAR",
"VARCHAR",
"VARCHAR",
"VARCHAR",
"VARCHAR",
"VARCHAR",
"VARCHAR",
"TIMESTAMP",
"FLOAT8"
],
"rows": [
{
"hostname": "host_9",
"rack": "7",
"service_environment": "production",
"os": "Ubuntu16.10",
"service": "14",
"datacenter": "us-east-1b",
"arch": "x86",
"service_version": "0",
"team": "LON",
"region": "us-east-1",
"timestamp": "2019-03-01T00:00",
"value": "90.49879988870993"
},
{
"hostname": "host_9",
"rack": "7",
"service_environment": "production",
"os": "Ubuntu16.10",
"service": "14",
"datacenter": "us-east-1b",
"arch": "x86",
"service_version": "0",
"team": "LON",
"region": "us-east-1",
"timestamp": "2019-03-01T00:00:10",
"value": "91.55436144098181"
},
...
]
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论