All Products
Search
Document Center

Time Series Database:JDBC-based TSQL queries

Last Updated:Jan 20, 2022

The SQL query engine of TSDB supports the Java Database Connectivity (JDBC) protocol. You can access TSDB from common clients that support JDBC or use TSQL in your Java application to query time series data in TSDB through the JDBC protocol.

In certain TSDB versions, you can view the URL required to access TSDB through JDBC on the Instance Details page of a TSDB instance.

This topic describes how to use TSQL in your Java application to query time series data of TSDB through the JDBC protocol.

I. JDBC connection example

1. Add dependencies for the JDBC driver

Make sure the following requirements are met:

  • Java runtime environment 1.8 is used.
  • You have configured JDBC-based access to the TSDB instance and have obtained the URL for the access.
  • You have configured a blacklist or a whitelist for the TSDB instance to ensure that the application client can access the TSDB instance.

JDBC driver dependencies of TSQL have been published to a Maven repository. The following example uses the Maven repository to manage the tsql_jdbc_app project. You need to add the following content to the pom.xml file.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.alibaba.tsdb.tsql</groupId>
    <artifactId>tsql_jdbc_app</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.drill.exec/drill-jdbc -->
        <dependency>
            <groupId>org.apache.drill.exec</groupId>
            <artifactId>drill-jdbc-all</artifactId>
            <version>1.15.0</version>
            <exclusions>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>log4j-over-slf4j</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-assembly-plugin</artifactId>
                <configuration>
                    <archive>
                        <manifest>
                            <mainClass>com.alibaba.tsdb.tsql.TsqlJdbcSampleApp</mainClass>
                        </manifest>
                    </archive>
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                </configuration>
                <executions>
                    <execution>
                        <id>make-assembly</id> <!-- this is used for inheritance merges -->
                        <phase>package</phase> <!-- bind to the packaging phase -->
                        <goals>
                            <goal>single</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>
Note

After you add the preceding content, you can obtain a .jar file that includes all .jar dependency files and the .class file of your application.

2. Sample code for a JDBC connection

The following provides an example for your reference. You must change the following parameter settings:

  • host: Specify the host name or IP address of your TSDB instance.
  • port: Specify the JDBC port. In Alibaba Cloud TSDB, the default JDBC port is 3306.
  • sql: Specify the TSQL query statement to execute.

In the Java application project, create the com.alibaba.tsdb.tsql package and the TsqlJdbcSampleApp Java source file.Code demo

The example code is as follows:

package com.alibaba.tsdb.tsql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TsqlJdbcSampleApp {
    public static void main(String[] args) throws Exception {
        Connection connection = null;
        Statement stmt = null;

        try {
            // step 1: Register JDBC driver
            Class.forName("org.apache.drill.jdbc.Driver");

            // hostname or address of TSDB instance.
            String host = "ts-uf64t3199j58j8251.tsql.hitsdb.rds.aliyuncs.com";
            // port for TSQL JDBC service
            int port = 3306;

            String jdbcUrl = String.format("jdbc:drill:drillbit=%s:%s", host, port);

            // step 2: Open connection
            System.out.println("Connecting to database @ " + jdbcUrl + "  ...");
            connection = DriverManager.getConnection(jdbcUrl);

            // step 3: Create a statement
            System.out.println("Creating statement ...");
            stmt = connection.createStatement();


            // step 4: Execute a query using the statement.
            String sql = "select hostname, `timestamp`, `value` " +
                "from tsdb.`cpu.usage_system` " +
                "where `timestamp` between '2019-03-01' and '2019-03-01 00:05:00'";

            ResultSet rs = stmt.executeQuery(sql);

            // step 5: Extract data from ResultSet.
            int row = 0;
            System.out.println("hostname\ttimestamp\tvalue");
            System.out.println("-----------------------------------------------------");
            while (rs.next()) {
                row++;
                System.out.println(rs.getString("hostname") + "\t" + rs.getTimestamp("timestamp") + "\t" +rs.getDouble("value"));
            }
            System.out.println("-----------------------------------------------------");
            System.out.println( row + "rows returned");

        } catch(SQLException se){
            //Handle errors for JDBC
            se.printStackTrace();
        }catch(Exception e){
            //Handle errors for Class.forName
            e.printStackTrace();
        }finally{
            //finally block used to close resources
            try{
                if(stmt!=null)
                    stmt.close();
            }catch(SQLException se2){
            }// nothing we can do
            try{
                if(connection!=null)
                    connection.close();
            }catch(SQLException se){
                se.printStackTrace();
            }//end finally try
        }//end try
        System.out.println("Goodbye!");
    }
}

3. Compilation and execution

In the root directory of the project, run the following Maven command: maven clean install

After the command is executed, you can obtain an executable file named tsql_jdbc_app-1.0-SNAPSHOT-jar-with-dependencies.jar in the /targets directory of the project.

Run the JAR file.

java -jar target/tsql_jdbc_app-1.0-SNAPSHOT-jar-with-dependencies.jar

The following figure shows a part of the query results.TSQL query results based on the JDBC application

Note

Summary: You can perform the preceding steps to compile Java applications to query time series data by using the JDBC protocol.

II. Limits on the use of JDBC

Before you use the JDBC protocol to access a TSDB instance, make sure that you are aware of the following limits:

  1. TSQL supports queries of time series data and metadata, but does not allow you to write, modify, or delete data.
  2. TSDB does not support transactions.

The following table describes the limits on calling JDBC APIs.

API Method TSDB limits
Connection setAutoCommit(boolean) Only allows the value true as the input parameter.
Connection getAutoCommit() Returns the value true.
Connection commit() The following error message appears when you call this method: SQLFeatureNotSupportedException.
Connection rollback() The following error message appears when you call this method: SQLFeatureNotSupportedException.
Connection setTransactionIsolation(int level) Only allows TRANSACTION_NONE as the input parameter.
Connection getTransactionIsolation() Only allows TRANSACTION_NONE as the input parameter.
Connection setSavePoint() The following error message appears when you call this method: SQLFeatureNotSupportedException.
Connection setSavePoint(String name) The following error message appears when you call this method: SQLFeatureNotSupportedException.
Connection rollback(Savepoint savepoint) The following error message appears when you call this method: SQLFeatureNotSupportedException.
Connection releaseSavePoint(Savepoint savepoint) The following error message appears when you call this method: SQLFeatureNotSupportedException.
Connection setNetworkTimeout() The following error message appears when you call this method: SQLFeatureNotSupportedException.
Connection getNetworkTimeout() The following error message appears when you call this method: SQLFeatureNotSupportedException.