Treasure Data JDBC Driver

This article will explain how to use Treasure Data’s JDBC (Java Database Connectivity) driver. This driver enables you to use Treasure Data with a standard JDBC interface.

Table of Contents

Download

You can download the driver itself from the link below. The driver is still in beta; any feedback is appreciated.

This driver only works with Treasure Data. It does not run on other environments, such as your local Hadoop/Hive cluster.

JDBC URL

Use the following JDBC URL format with your Java application or JDBC-compliant SQL/BI tool.

jdbc:td://api.treasuredata.com/<db_name>;useSSL=true

For example, to access a database named “sample_db” in your account, your JDBC URL would be:

jdbc:td://api.treasuredata.com/sample_db;useSSL=true

You can choose the query engine (Hive or Presto) by the parameter.

jdbc:td://api.treasuredata.com/sample_db;useSSL=true;type=hive
jdbc:td://api.treasuredata.com/sample_db;useSSL=true;type=presto

If you are trying to connect from behind a proxy, you can specify the proxy settings using the following properties:

  • httpproxyhost
  • httpproxyport
  • httpproxyuser
  • httpproxypassword

For example:

jdbc:td://api.treasuredata.com/testdb;httpproxyhost=myproxy.com;httpproxyport=myport;httpproxyuser=myusername;httpproxypassword=mypassword

Query Execution Internals

Because Treasure Data is a cloud service, its JDBC driver behaves in a slightly different manner compared to that of traditional RDBMs.

SELECT Statement

When a SELECT statement is sent to the driver, the driver will issue the query to the cloud. The driver will regularly poll the job results while the jobs run on the cloud. The query may take several hours, we recommend that you use a background thread.

INSERT Statement

When a INSERT statement is sent to the driver, the data is first buffered in local memory. The data is uploaded into the cloud every 5 minutes. Please note that the upload doesn’t occur in realtime.

Sample Java Program

Here’s a sample Java program that executes a SELECT statement. This program counts the number of records within the testdb.www_access table.

JDBCSample.java

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import com.treasure_data.jdbc.TreasureDataDriver;

public class JDBCSample {
  public static void loadSystemProperties() throws IOException {
    Properties props = System.getProperties();
    props.load(TreasureDataDriver.class.getClassLoader().getResourceAsStream("treasure-data.properties"));
  }

  public static void main(String[] args) throws Exception {
    loadSystemProperties();
    try {
      Class.forName("com.treasure_data.jdbc.TreasureDataDriver");
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
      System.exit(1);
    }

    Connection conn = DriverManager.getConnection(
      "jdbc:td://api.treasuredata.com/testdb;useSSL=true",
      "YOUR_MAIL_ADDRESS_HERE",
      "YOUR_PASSWORD_HERE");
    Statement stmt = conn.createStatement();
    String sql = "SELECT count(1) FROM www_access";
    System.out.println("Running: " + sql);
    ResultSet res = stmt.executeQuery(sql);
    while (res.next()) {
      System.out.println(String.valueOf(res.getObject(1)));
    }
  }
}

treasure-data.properties

This file is only required if using INSERT statements. If you are only using SELECT statement, this file is not required.

td.logger.agentmode=false
td.api.key=<your API key>

If this article is incorrect or outdated, or omits critical information, please let us know. For all other issues, please see our support channels. Live chat with our staffs also work well.