All Products
Search
Document Center

Application development

Last Updated: May 19, 2022

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

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

Step 2: Modify the sample application

You must make the following modifications:

  • Replace the values of the host and port variables in the sample application based on the host and port of your TSDB instance.

  • Create an SQL query statement based on the time series data that is written in Step 1. You must replace the relevant variables in the sample application.

Python

import json
import requests

## Required. Modify the host and port variables based on the host and port of your TSDB instance.
host="your_tsdb_instance_host"
port=3242

## The HTTP request URL is http://host:port/api/sqlquery.
endpoint_sqlquery = 'http://{0}:{1}/api/sqlquery'.format(host,port)

## The execSqlQuery function uses an SQL SELECT statement as an input. The value of the sqlquery parameter must be of the STRING data type. You can submit the SELECT statement to TSDB and obtain the response.
def execSqlQuery(sqlquery):
  ## Create a JSON string in an HTTP request.
  query = {
    "sql": sqlquery
  }

  ## Use the GET method to submit the HTTP request.
  response = requests.get(endpoint_sqlquery, data=json.dumps(query), headers={'Content-Type':'application/json'})
  if response.status_code != 200:
    ## If the query fails, an error code and an error message are displayed. 
    print ('Failed to execute query. POST /api/sqlquery {}, {}'.format(response.status_code, response.content))
  else:
    print ("Query is successful.")
    ## Deserialize the response into a JSON object in Python.
    parsed = json.loads(response.content)
    ## You can further process the JSON-formatted response. In this example, the JSON-formatted response is only displayed. 
    print (json.dumps(parsed, indent=2, sort_keys=True))

## A sample SQL statement is provided. You can create SQL statements based on the instructions that are provided in the related TSQL query topics.
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)

Run the preceding Python application.

python demoSql.py

The following code shows part of a success response:

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

You must add the HttpClient and Gson libraries to your application code. The HttpClient library is used to submit HTTP requests, and the Gson library is used to serialize and deserialize JSON data. If you use Apache Maven to manage your Java project, you must add dependencies to the pom.xml file of your project.

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

You can use the following sample code to develop your application:

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 {
        // Required. Modify the host and port variables based on the host and port of your TSDB instance.
        String host = "ts-uf6w8b6s6fuse9fua.hitsdb.rds.aliyuncs.com";
        int port = 3242;

        // A sample SQL statement is provided. You can create SQL statements based on the instructions that are provided in the related TSQL query topics.
        String sql = "select * from tsdb.`cpu.usage_system` " +
            "where `timestamp` between '2019-03-01 00:00:00' and '2019-03-01 00:00:10'";

        // Submit the SQL query and obtain the query results.
        Result result = execSqlQuery(host, port, sql);

        System.out.println("Submitted query:" + sql);

        // Display the query results in 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 {
        // The HTTP request URL is http://host:port/api/sqlquery.
        String url = String.format("http://%s:%d/api/sqlquery", host, port);

        // Create a query object. The query object is a plain old Java object (POJO). The query object is serialized into a JSON string.
        Query query = new Query(sql);

        Gson         gson          = new Gson();
        HttpClient   httpClient    = HttpClientBuilder.create().build();
        HttpPost     post          = new HttpPost(url);

        // postingString specifies the JSON string into which the query object is serialized.
        StringEntity postingString = new StringEntity(gson.toJson(query));
        post.setEntity(postingString);
        post.setHeader("Content-type", "application/json");

        // Use the POST method to submit the query to the HTTP request 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());
            // The response to the request that is sent to /api/sqlquery contains a JSON string. The Result Java POJO class is used to deserialize the JSON string into a result object.
            Result result = gson.fromJson(resStr, Result.class);
            return result;
        }
    }

    // Use a Java POJO class to create a query.
    public static class Query {
        String sql;
        public Query(String sql) {
            this.sql = sql;
        }
    }

    // Use a Java POJO class to deserialize the query results.
    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;
        }
    }

}

The following sample code shows part of the result set of a query:

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"
    },
...
]