All Products
Search
Document Center

Application development procedure

Last Updated: May 24, 2020

This topic describes how to use TSQL to query data from a Time Series Database (TSDB) database for Python and Java applications.

Step 1: Create a TSDB instance and write time series data

Step 2: Modify the sample application

You must make the following modifications:

  • Replace the values of the host and port parameters in the sample application based on the host and the port of your TSDB instance.
  • Create an SQL query statement based on the time series data that you have written in step 1. You must replace the relevant variables in the sample application.

Python

  1. import json
  2. import requests
  3. ## Replace the current host and port variable values based on the host and the port of your TSDB instance.
  4. host="your_tsdb_instance_host"
  5. port=3242
  6. ## The HTTP request URL is http://host:port/api/sqlquery.
  7. endpoint_sqlquery = 'http://{0}:{1}/api/sqlquery'.format(host,port)
  8. ## The execSqlQuery function uses an SQL SELECT statement (sqlquery) as an input. The sqlquery element specifies a string. You can submit the SELECT statement and obtain the response.
  9. def execSqlQuery(sqlquery):
  10. ## Create a JSON string in the HTTP request.
  11. query = {
  12. "sql": sqlquery
  13. }
  14. ## Send the HTTP request by using the GET method.
  15. response = requests.get(endpoint_sqlquery, data=json.dumps(query), headers={'Content-Type':'application/json'})
  16. if response.status_code != 200:
  17. ## An error code and an error message are displayed if the query fails.
  18. print ('Failed to execute query. POST /api/sqlquery {}, {}'.format(response.status_code, response.content))
  19. else:
  20. print ("Query is successful.")
  21. ## Deserialize the response into a JSON object in Python.
  22. parsed = json.loads(response.content)
  23. ## You can process the JSON-formatted query result. In this example, the result is only printed out.
  24. print (json.dumps(parsed, indent=2, sort_keys=True))
  25. ## A sample SQL statement is provided. You can create valid and suitable SQL statements based on the instructions that are provided in the related TSQL query topics.
  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)

Run the preceding Python application.

  1. python demoSql.py

The following code shows a part of the response for a successful query.

  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

You must add HttpClient and Gson libraries. The HttpClient library is used to submit HTTP requests, and the Gson library is used for JSON serialization and deserialization. If you use Apache Maven to manage Java projects, you must add dependencies to the pom.xml files of the projects.

  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>

Sample application

  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. // Replace the current variable values based on the host and the port of your TSDB instance.
  15. String host = "ts-uf6w8b6s6fuse9fua.hitsdb.rds.aliyuncs.com";
  16. int port = 3242;
  17. // A sample SQL statement is provided. You can create valid and suitable SQL statements based on the instructions that are provided in the related TSQL query topics.
  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. // Submit the SQL query and obtain the query result.
  21. Result result = execSqlQuery(host, port, sql);
  22. System.out.println("Submitted query:" + sql);
  23. // Print the JSON-formatted query result.
  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. // The HTTP request URL is http://host:port/api/sqlquery.
  29. String url = String.format("http://%s:%d/api/sqlquery", host, port);
  30. // Create the query object. The query object is a plain old Java object (POJO). The query object is serialized into a JSON string.
  31. Query query = new Query(sql);
  32. Gson gson = new Gson();
  33. HttpClient httpClient = HttpClientBuilder.create().build();
  34. HttpPost post = new HttpPost(url);
  35. // The postingString element specifies the JSON string into which the query object is serialized.
  36. StringEntity postingString = new StringEntity(gson.toJson(query));
  37. post.setEntity(postingString);
  38. post.setHeader("Content-type", "application/json");
  39. // Submit the request to the URL by using the POST method.
  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. // The response for the /api/sqlquery API endpoint contains a JSON string. The Result Java POJO class is used to convert the JSON string into the result java object.
  46. Result result = gson.fromJson(resStr, Result.class);
  47. return result;
  48. }
  49. }
  50. // Create a query by using the Java POJO class.
  51. public static class Query {
  52. String sql;
  53. public Query(String sql) {
  54. this.sql = sql;
  55. }
  56. }
  57. // Deserialize the query result by using the Java POJO class.
  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. }

Part of the query result

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