Connect to Treasure Data’s Trino (a.k.a. PrestoSQL) using our JDBC Driver.

Limitations

The Trino JDBC connection has several limitations:

  • setAutoCommit(false) throws an exception because transactions are not supported.

  • presto-jdbc queries share the same Presto resources you have available for any other ad hoc or scheduled queries that you submit from the TD Console or through the REST API.

  • presto-jdbc queries are shown in your account job log, with the exception of the following standard entries:

    • Query result table

    • Query plan  

    • Progress log  

  • The query result is converted into JSON before being submitted back to separate tools for display. As such, very large result tables may take some time before being displayed. If you have latency problems, contact Support.

  • Certain BI tools issue “information_schema” requests that cause failed queries in our system. This may be an issue in your tool during first use.

If you see any unexpected errors/bugs/logs that occur during beta use, contact Support.

Our aim is to keep this API up as much as possible. Specifically, we expect to achieve a downtime of less than 1 hour total per month.

Setup

  • Download trino-jdbc driver (a.k.a. presto-jdbc driver)

    • For using secure HTTPS (port 443) connection you need to use presto 0.148 or later.

  • Use the following parameters to connect prestobase on development server:

    • Driver name: io.trino.jdbc.TrinoDriver (if you use PrestoSQL Driver, you may use io.prestosql.jdbc.PrestoDriver)

    • user: (Your TD API key)

    • password: (dummy string. This value will be ignored)

      • After presto-jdbc 0.180, because of a bug, a dummy string needs to be set to the password.

Connection URL examples

The connection URL needs to have the following format:

jdbc:trino://api-presto.treasuredata.com:443/td-presto/(database name)?SSL=true Or jdbc:presto://api-presto.treasuredata.com:443/td-presto/(database name)?SSL=true

jdbc:trino://api-presto.treasuredata.com:443/td-presto?SSL=true
OR
jdbc:presto://api-presto.treasuredata.com:443/td-presto?SSL=true
jdbc:trino://api-presto.treasuredata.com:443/td-presto/sample_datasets?SSL=true
OR
jdbc:presto://api-presto.treasuredata.com:443/td-presto/sample_datasets?SSL=true

Example Code

$ javac Sample.java
$ java -cp .:trino-jdbc-0.359.jar Sample (your TD API key)
time=1412351990, method=GET, path=/category/office
time=1412351976, method=GET, path=/item/software/2265
time=1412351961, method=GET, path=/category/toys
time=1412351947, method=GET, path=/item/electronics/3305
time=1412351932, method=GET, path=/category/software

Sample.java

import java.sql.*;

class Sample {
  public static void main(String[] args) {
    if(args.length != 1) {
      System.err.println("Provide your TD API key as an argument");
      return;
    }

    String apikey = args[0];
    try {
      Connection conn = DriverManager.getConnection("jdbc:trino://api-presto.treasuredata.com:443/td-presto/sample_datasets?SSL=true", apikey, "dummy_password");
      Statement stmt = conn.createStatement();
      try {
        ResultSet rs = stmt.executeQuery("SELECT time, method, path from www_access limit 5");
        while(rs.next()) {
            long time = rs.getLong(1);
            String method = rs.getString(2);
            String path = rs.getString(3);
            System.out.println(String.format("time=%s, method=%s, path=%s", time, method, path));
        }
      }
      finally {
        stmt.close();
        conn.close();
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}
  • No labels