TSQL应用开发步骤示例_TSQL 开发指南_时序数据库 TSDB_时序时空数据库TSDB_阿里云技术文档

这里简要介绍如何在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"
    },
...
]

TSQL应用开发步骤示例_TSQL 开发指南_时序数据库 TSDB_时序时空数据库TSDB

TSQL应用开发步骤示例

第一步：申请TSDB实例，并写入时序数据

第二步：参考下面的demo程序，作相应的改动

Python

Java

版权声明

评论