全部产品
云市场

TSQL应用开发步骤示例

更新时间:2019-04-15 10:29:22

这里简要介绍如何在Python, Java应用程序里,通过TSQL查询并获取TSDB数据库的数据。

第一步:申请TSDB实例,并写入时序数据

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

需要的改动

  • 修改程序中host和port,以第一步申请的TSDB实例的host和port取代
  • 针对写入的时序数据,构造一个SQL查询语句,替换程序中的相应变量。

Python

  1. import json
  2. import requests
  3. ## 需要修改:根据你申请的TSDB实例的host和port,修改下面两个变量
  4. host="your_tsdb_instance_host"
  5. port=3242
  6. ## http service的URL: http://host:port/api/sqlquery
  7. endpoint_sqlquery = 'http://{0}:{1}/api/sqlquery'.format(host,port)
  8. ## 函数execSqlQuery接受一个SQL Select 语句,sqlquery是一个String。提交到TSDB,并获取结果
  9. def execSqlQuery(sqlquery):
  10. ## 构造http request中的JSON string
  11. query = {
  12. "sql": sqlquery
  13. }
  14. ## 提交http request, 以GET的方式
  15. response = requests.get(endpoint_sqlquery, data=json.dumps(query), headers={'Content-Type':'application/json'})
  16. if response.status_code != 200:
  17. ## 查询执行失败,显示错误代码和错误信息。
  18. print ('Failed to execute query. POST /api/sqlquery {}, {}'.format(response.status_code, response.content))
  19. else:
  20. print ("Query is successful.")
  21. ## 把response反序列化成一个python的JSON object
  22. parsed = json.loads(response.content)
  23. ## 你可以对这个查询结果的JSON作进一步的处理,这里,我们仅仅是打印出来。
  24. print (json.dumps(parsed, indent=2, sort_keys=True))
  25. ## 这是一个 demo sql语句。根据TSDB SQL查询的文档构造合适的SQL查询语句
  26. sqlquery = "select * from tsdb.`cpu.usage_system` where `timestamp` between '2019-03-01 00:00:00' and '2019-03-01 00:00:10'"
  27. execSqlQuery(sqlquery)

执行上面的python程序:

  1. python demoSql.py

下面是一个成功执行后的部分查询结果

  1. Query is successful.
  2. {
  3. "columns": [
  4. "hostname",
  5. "rack",
  6. "service_environment",
  7. "os",
  8. "service",
  9. "datacenter",
  10. "arch",
  11. "service_version",
  12. "team",
  13. "region",
  14. "timestamp",
  15. "value"
  16. ],
  17. "metadata": [
  18. "VARCHAR",
  19. "VARCHAR",
  20. "VARCHAR",
  21. "VARCHAR",
  22. "VARCHAR",
  23. "VARCHAR",
  24. "VARCHAR",
  25. "VARCHAR",
  26. "VARCHAR",
  27. "VARCHAR",
  28. "TIMESTAMP",
  29. "FLOAT8"
  30. ],
  31. "rows": [
  32. {
  33. "arch": "x86",
  34. "datacenter": "us-east-1b",
  35. "hostname": "host_9",
  36. "os": "Ubuntu16.10",
  37. "rack": "7",
  38. "region": "us-east-1",
  39. "service": "14",
  40. "service_environment": "production",
  41. "service_version": "0",
  42. "team": "LON",
  43. "timestamp": "2019-03-01T00:00",
  44. "value": "90.49879988870993"
  45. },
  46. {
  47. "arch": "x86",
  48. "datacenter": "us-east-1b",
  49. "hostname": "host_9",
  50. "os": "Ubuntu16.10",
  51. "rack": "7",
  52. "region": "us-east-1",
  53. "service": "14",
  54. "service_environment": "production",
  55. "service_version": "0",
  56. "team": "LON",
  57. "timestamp": "2019-03-01T00:00:10",
  58. "value": "91.55436144098181"
  59. },
  60. ...
  61. ]

Java

首先,需要加入httpclient和gson. 我们使用httpclient来提交http的request, 而gson库用来JSON的序列化和反序列化。假设用的是maven来管理java 项目,在项目的pom.xml加入依赖

  1. <dependency>
  2. <groupId>org.apache.httpcomponents</groupId>
  3. <artifactId>httpclient</artifactId>
  4. <version>4.5.3</version>
  5. </dependency>
  6. <dependency>
  7. <groupId>com.google.code.gson</groupId>
  8. <artifactId>gson</artifactId>
  9. <version>2.8.1</version>
  10. </dependency>

样本程序

  1. import com.google.gson.Gson;
  2. import com.google.gson.GsonBuilder;
  3. import org.apache.http.HttpResponse;
  4. import org.apache.http.client.HttpClient;
  5. import org.apache.http.client.methods.HttpPost;
  6. import org.apache.http.entity.StringEntity;
  7. import org.apache.http.impl.client.HttpClientBuilder;
  8. import org.apache.http.util.EntityUtils;
  9. import java.util.Collection;
  10. import java.util.List;
  11. import java.util.Map;
  12. public class TsqlDemo {
  13. public static void main(String[] args) throws Exception {
  14. // 需要修改:根据你申请的TSDB实例的host和port,修改下面两个变量
  15. String host = "ts-uf6w8b6s6fuse9fua.hitsdb.rds.aliyuncs.com";
  16. int port = 3242;
  17. // 这是一个 demo sql语句。根据TSDB SQL查询的文档构造合适的SQL查询语句
  18. String sql = "select * from tsdb.`cpu.usage_system` " +
  19. "where `timestamp` between '2019-03-01 00:00:00' and '2019-03-01 00:00:10'";
  20. // 提交SQL查询,并获得查询结果
  21. Result result = execSqlQuery(host, port, sql);
  22. System.out.println("Submitted query:" + sql);
  23. // 把查询结果以JSON的形式打印出来
  24. Gson gson = new GsonBuilder().setPrettyPrinting().create();
  25. System.out.println("Query Result: \n" + gson.toJson(result));
  26. }
  27. private static Result execSqlQuery(String host, int port, String sql) throws Exception {
  28. // http service的URL: http://host:port/api/sqlquery
  29. String url = String.format("http://%s:%d/api/sqlquery", host, port);
  30. // 构造一个Java POJO Query instance。JSON序列化后成为一个JSON格式的字符串
  31. Query query = new Query(sql);
  32. Gson gson = new Gson();
  33. HttpClient httpClient = HttpClientBuilder.create().build();
  34. HttpPost post = new HttpPost(url);
  35. // postingString 就是query被JSON序列化后的JSON格式的字符串
  36. StringEntity postingString = new StringEntity(gson.toJson(query));
  37. post.setEntity(postingString);
  38. post.setHeader("Content-type", "application/json");
  39. // 以POST的形式,提交到url,
  40. HttpResponse resp = httpClient.execute(post);
  41. if (resp.getStatusLine().getStatusCode() != 200) {
  42. throw new RuntimeException("Failed to execute query. Error:" + EntityUtils.toString(resp.getEntity()));
  43. } else {
  44. String resStr = EntityUtils.toString(resp.getEntity());
  45. // /api/sqlquery的response包含的是一个JSON格式的字符串,我们通过Result这个Java POJO来反序列化成一个Result的instance.
  46. Result result = gson.fromJson(resStr, Result.class);
  47. return result;
  48. }
  49. }
  50. // JAVA POJO, 用于构造一个Query
  51. public static class Query {
  52. String sql;
  53. public Query(String sql) {
  54. this.sql = sql;
  55. }
  56. }
  57. // JAVA POJO, 用于查询结果的反序列化
  58. public static class Result {
  59. public Collection<String> columns;
  60. public List<String> metadata;
  61. public List<Map<String, String>> rows;
  62. public Result(Collection<String> columns, List<String> metadata, List<Map<String, String>> rows) {
  63. this.columns = columns;
  64. this.metadata = metadata;
  65. this.rows = rows;
  66. }
  67. }
  68. }

执行的结果(部分)

  1. Submitted query:select * from tsdb.`cpu.usage_system` where `timestamp` between '2019-03-01 00:00:00' and '2019-03-01 00:00:10'
  2. Query Result:
  3. {
  4. "columns": [
  5. "hostname",
  6. "rack",
  7. "service_environment",
  8. "os",
  9. "service",
  10. "datacenter",
  11. "arch",
  12. "service_version",
  13. "team",
  14. "region",
  15. "timestamp",
  16. "value"
  17. ],
  18. "metadata": [
  19. "VARCHAR",
  20. "VARCHAR",
  21. "VARCHAR",
  22. "VARCHAR",
  23. "VARCHAR",
  24. "VARCHAR",
  25. "VARCHAR",
  26. "VARCHAR",
  27. "VARCHAR",
  28. "VARCHAR",
  29. "TIMESTAMP",
  30. "FLOAT8"
  31. ],
  32. "rows": [
  33. {
  34. "hostname": "host_9",
  35. "rack": "7",
  36. "service_environment": "production",
  37. "os": "Ubuntu16.10",
  38. "service": "14",
  39. "datacenter": "us-east-1b",
  40. "arch": "x86",
  41. "service_version": "0",
  42. "team": "LON",
  43. "region": "us-east-1",
  44. "timestamp": "2019-03-01T00:00",
  45. "value": "90.49879988870993"
  46. },
  47. {
  48. "hostname": "host_9",
  49. "rack": "7",
  50. "service_environment": "production",
  51. "os": "Ubuntu16.10",
  52. "service": "14",
  53. "datacenter": "us-east-1b",
  54. "arch": "x86",
  55. "service_version": "0",
  56. "team": "LON",
  57. "region": "us-east-1",
  58. "timestamp": "2019-03-01T00:00:10",
  59. "value": "91.55436144098181"
  60. },
  61. ...
  62. ]