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>
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.
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.
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:
- TSQL supports queries of time series data and metadata, but does not allow you to write, modify, or delete data.
- 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. |