Time Series Database:TSQL JDBC support

You can find the JDBC access information in the console of your instance. This feature is available only for specific TSDB instance versions.

This topic describes how to use the JDBC protocol to query data from TSDB in a Java application using TSQL.

I. JDBC connection example

1. Import driver dependencies

Runtime requirements:

• Java 1.8 Runtime.

• Configure JDBC access for the instance and obtain the JDBC URL.

• Configure the blacklists and whitelists for the instance to allow the client application to access the instance.

The TSQL JDBC driver dependency is published to the Maven repository. This section uses a Maven project named tsql_jdbc_app as an example. 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>

2. JDBC connection example code

Note that this is only an example. You must modify the parameters for your specific application.

• host: The hostname or IP address of your TSDB instance.

• port: The default JDBC port for Alibaba Cloud Lindorm Time Series Database is 3306.

• sql: The TSQL query statement to execute.

In your Java project, create a package named com.alibaba.tsdb.tsql and a Java source file named TsqlJdbcSampleApp.

Complete code:

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. Compile and execute

In the root directory of the project, run the maven clean install command.

After the command is executed, an executable JAR file named tsql_jdbc_app-1.0-SNAPSHOT-jar-with-dependencies.jar is generated in the target directory of the project.

Run the application:

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

II. Limits of the JDBC protocol

The TSQL JDBC protocol has several functional limitations. Before you use the TSQL JDBC protocol, review the following limitations:

1. TSQL currently supports only querying time series data and time series metadata. It does not support data writing, modification, or deletion.

2. TSDB does not support transactions.

The limitations of specific JDBC APIs are described as follows:

Interface	Method	TSDB JDBC support
Connection	setAutoCommit(boolean)	Only `true` is allowed as an input parameter.
Connection	getAutoCommit()	Returns `true`.
Connection	commit()	Invoking this method throws a `SQLFeatureNotSupportedException`.
Connection	rollback()	Invoking this method throws a `SQLFeatureNotSupportedException`.
Connection	setTransactionIsolation(int level)	Only `TRANSACTION_NONE` is allowed.
Connection	getTransactionIsolation()	Only `TRANSACTION_NONE` is allowed.
Connection	setSavePoint()	Invoking this method throws a `SQLFeatureNotSupportedException`.
Connection	setSavePoint(String name)	Invoking this method throws a `SQLFeatureNotSupportedException`.
Connection	rollback(Savepoint savepoint)	Invoking this method throws a `SQLFeatureNotSupportedException`.
Connection	releaseSavePoint(Savepoint savepoint)	Invoking this method throws a `SQLFeatureNotSupportedException`.
Connection	setNetworkTimeout()	Invoking this method throws a `SQLFeatureNotSupportedException`.
Connection	getNetworkTimeout()	Invoking this method throws a `SQLFeatureNotSupportedException`.