Build a Presto JDBC Connection on EMR for Java Queries - E-MapReduce

Prerequisites

Before you begin, make sure that you have:

An EMR cluster with the Presto service running
Java 8 or later
Query permissions on the system.jdbc table for all users that will connect

Add the JDBC driver dependency

Add the Presto JDBC driver to your pom.xml based on your EMR cluster version.

Component version	Dependency	Driver class name
0.2XX	`groupId`: `com.facebook.presto` `artifactId`: `presto-jdbc` `version`: `0.2XX`	`com.facebook.presto.jdbc.PrestoDriver`

<dependency>
  <groupId>com.facebook.presto</groupId>
  <artifactId>presto-jdbc</artifactId>
  <version>0.2XX</version>
</dependency>

Connect to a database

Use the following URL format to connect:

jdbc:presto://<COORDINATOR>:<PORT>/[CATALOG]/[SCHEMA]

The default port is 8889. Examples:

jdbc:presto://master-1-1:8889               // Default catalog and schema
jdbc:presto://master-1-1:8889/hive          // Catalog: hive, default schema
jdbc:presto://master-1-1:8889/hive/default  // Catalog: hive, schema: default

Pass connection parameters

Pass parameters to DriverManager using either of the following methods. Both methods are equivalent.

As a Properties object:

String url = "jdbc:presto://<master-node-name>:8889/hive/default";
Properties properties = new Properties();
properties.setProperty("user", "presto");
Connection connection = DriverManager.getConnection(url, properties);

As URL parameters:

String url = "jdbc:presto://<master-node-name>:8889/hive/default?user=presto";
Connection connection = DriverManager.getConnection(url);

Connection parameters

Parameter	Type	Default	Description
`user`	STRING	—	Username for authentication and authorization
`password`	STRING	—	Password for Lightweight Directory Access Protocol (LDAP) authentication
`socksProxy`	STRING:NUMBER	—	SOCKS proxy address and port. Example: `localhost:1080`
`httpProxy`	STRING:NUMBER	—	HTTP proxy address and port. Example: `localhost:8888`
`SSL`	BOOLEAN	`false`	Enable SSL for HTTPS-based connections
`SSLKeyStorePath`	STRING	—	Path to the keystore file
`SSLKeyStorePassword`	STRING	—	Password for the keystore file
`SSLTrustStorePath`	STRING	—	Path to the Java truststore file
`SSLTrustStorePassword`	STRING	—	Password for the Java truststore file
`KerberosRemoteServiceName`	STRING	—	Kerberos service name
`KerberosPrincipal`	STRING	—	Kerberos principal name
`KerberosUseCanonicalHostname`	BOOLEAN	`false`	Use canonical hostname for Kerberos
`KerberosConfigPath`	STRING	—	Path to the Kerberos configuration file
`KerberosKeytabPath`	STRING	—	Path to the Kerberos keytab file
`KerberosCredentialCachePath`	STRING	—	Path to the Kerberos credential cache

Example

The following example connects to a Presto database, runs a query, and prints the results:

Connection connection = null;
Statement statement = null;
try {
    // JDBC URL pointing to the Presto coordinator
    String url = "jdbc:presto://<master-node-name>:8889/hive/default";
    Properties properties = new Properties();
    properties.setProperty("user", "presto");
    // Create a connection
    connection = DriverManager.getConnection(url, properties);
    // Create a statement
    statement = connection.createStatement();
    // Execute a query
    ResultSet rs = statement.executeQuery("select * from t1");
    // Print results
    int columnNum = rs.getMetaData().getColumnCount();
    int rowIndex = 0;
    while (rs.next()) {
        rowIndex++;
        for (int i = 1; i <= columnNum; i++) {
            System.out.println("Row " + rowIndex + ", Column " + i + ": " + rs.getInt(i));
        }
    }
} catch (SQLException e) {
    LOG.ERROR("Exception thrown.", e);
} finally {
    // Close the statement
    if (statement != null) {
        try {
            statement.close();
        } catch (Throwable t) {
            // No-ops
        }
    }
    // Close the connection
    if (connection != null) {
        try {
            connection.close();
        } catch (Throwable t) {
            // No-ops
        }
    }
}

What's next

For simple queries without Java integration, connect to Presto using the CLI instead. See Use the CLI to connect to Presto.