Use JDBC

You can add the following configurations to the pom.xml file to connect to the Presto JDBC driver based on the version of Presto in your E-MapReduce (EMR) cluster:

• The version of Presto is 3XX:

  <dependency>
      <groupId>io.prestosql</groupId>
      <artifactId>presto-jdbc</artifactId>
      <version>3XX</version>
  </dependency>

• The version of Presto is 0.2XX:

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

The driver class name varies based on the version of Presto.

• If the version of Presto is 3XX, the class of the Presto JDBC driver is io.prestosql.jdbc.PrestoDriver.
• If the version of Presto is 0.2XX, the class of the Presto JDBC driver is com.facebook.presto.jdbc.PrestoDriver.

You can use the Presto JDBC driver to connect to a database. The JDBC connection string is in the following format:

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

Examples:

jdbc:presto://emr-header-1:9090               # Connect to a database by using a specific catalog and schema. 
jdbc:presto://emr-header-1:9090/hive          # Connect to a database by using the hive catalog and a specific schema. 
jdbc:presto://emr-header-1:9090/hive/default  # Connect to a database by using the hive catalog and the default schema. 

The Presto JDBC driver supports various parameters. These parameters can be passed to DriverManager as URL parameters or as properties.

• Example of passing parameters to DriverManager as properties:

  String url = "jdbc:presto://emr-header-1:9090/hive/default";
  Properties properties = new Properties();
  properties.setProperty("user", "hadoop");
  Connection connection = DriverManager.getConnection(url, properties);
  ......

• Example of passing parameters to DriverManager as URL parameters:

  String url = "jdbc:presto://emr-header-1:9090/hive/default?user=hadoop";
  Connection connection = DriverManager.getConnection(url);
  ......

The following table describes the connection parameters.

Parameter	Format	Description
user	STRING	The username.
password	STRING	The password.
socksProxy	STRING:NUMBER	The address and port of the SOCKS proxy server. Example: localhost:1080.
httpProxy	STRING:NUMBER	The address and port of the HTTP proxy server. Example: localhost:8888.
SSL	BOOLEAN	Specifies whether to use HTTPS for connections. Default value: false.
SSLTrustStorePath	STRING	The path used to store the Java truststore file.
SSLTrustStorePassword	STRING	The password of the Java truststore file.
KerberosRemoteServiceName	STRING	The name of the remote Kerberos service.
KerberosPrincipal	STRING	The Kerberos principal name.
KerberosUseCanonicalHostname	BOOLEAN	Specifies whether to use a normalized hostname. Default value: false.
KerberosConfigPath	STRING	The path used to store the Kerberos configuration file.
KerberosKeytabPath	STRING	The path used to store the Kerberos keytab file.
KerberosCredentialCachePath	STRING	The path used to cache the Kerberos credential.

In this example, the Presto JDBC driver is used for Java.

.....
// Load the class of the Presto JDBC driver. 
try {
    Class.forName("com.facebook.presto.jdbc.PrestoDriver");
} catch(ClassNotFoundException e) {
    LOG.ERROR("Failed to load presto jdbc driver.", e);
    System.exit(-1);
}
Connection connection = null;
Statement statement = null;
try {
    String url = "jdbc:presto://emr-header-1:9090/hive/default";
    Properties properties = new Properties();
    properties.setProperty("user", "hadoop");
    // Create a connection object. 
    connection = DriverManager.getConnection(url, properties);
    // Create a statement object. 
    statement = connection.createStatement();
    // Execute a query statement. 
    ResultSet rs = statement.executeQuery("select * from t1");
    // Return 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 {
  // Destroy the statement object. 
  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
    }
  }
}