Connecting To Presto via JDBC

Hi Guys,

In this blog we’ll be discussing about how to make a connection to presto server using JDBC, but before we get started let’s discuss what Presto is.

What is Presto ?

So, Presto is an open source distributed SQL query engine for running interactive analytic queries against different data sources. The sizes may ranges from gigabytes to petabytes. It runs on a cluster of machines and its installation includes a coordinator and multiple workers. It allows querying data where it lives, including Hive, Cassandra, relational databases or even proprietary data stores. A single Presto query can combine data from multiple sources, allowing for analytics across your entire organization.

For deploying presto on your machine you can go through the following link : Presto Installation

Setting up a JDBC connection

Prerequisite : The presto cluster must be running before establishing the connection.

Below is your JDBC driver url for presto driver.

JDBC_DRIVER = "com.facebook.presto.jdbc.PrestoDriver";

And your Database url should look like :

DB_URL = "jdbc:presto://localhost:8080/catalog/schema";

The following JDBC URL formats are supported:

jdbc:presto://host:port
jdbc:presto://host:port/catalog
jdbc:presto://host:port/catalog/schema

Here host:port is the host name and port number that you have specified in the discovery.uri parameter in config.properties file present inside the presto directory. Catalog is the catalog name to which you want to connect as a datasource and schema is the name of the database present in the datasource.

Catalog files are present inside the /etc/catalog folder in your presto directory. You can have multiple properties file for different datasources in the catalog directory.

So, here is a sample JDBC program:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class PrestoJdbc {
public static void main(String args[]) {
    //Connect to Presto server using Presto JDBC
final String JDBC_DRIVER = "com.facebook.presto.jdbc.PrestoDriver";
    final String DB_URL = "jdbc:presto://localhost:9000/catalogName/schemaName";
    //  Database credentials
    final String USER = "username";
    final String PASS = "password";
    Connection conn = null;
    Statement stmt = null;
    try {
      //Register JDBC driver
      Class.forName(JDBC_DRIVER);
      //Open a connection
      conn = DriverManager.getConnection(DB_URL, USER, PASS);
      //Execute a query
      stmt = conn.createStatement();
      String sql = "select * from tableName";
      ResultSet res = stmt.executeQuery(sql);
      //Extract data from result set
      while (res.next()) {
        //Retrieve by column name
        String name = res.getString("name");
        //Display values
        System.out.println("name : " + name);
      }
      //Clean-up environment
      res.close();
      stmt.close();
      conn.close();
    } 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 sqlException) {
        sqlException.printStackTrace();
      }
      try {
        if (conn != null) conn.close();
      } catch (SQLException se) {
        se.printStackTrace();
      }
    }
  }
}

Happy learning. :)