Use JDBC to access Presto - E-MapReduce - Alibaba Cloud Documentation Center

After you create an E-MapReduce (EMR) cluster that contains the Presto service, you can use the JDBC driver provided by Presto to connect to a Presto database. Then, you can perform complex data query, analysis, and processing operations on data in the database, or integrate query results into Java applications.

Add the dependency of a JDBC driver to the pom.xml file in Maven

You can add the required dependency of the Presto JDBC driver to the pom.xml file based on the version of your EMR cluster.

Component version	JDBC driver	Driver class name
0.2XX	`<dependency> <groupId>com.facebook.presto</groupId> <artifactId>presto-jdbc</artifactId> <version>0.2XX</version> </dependency>`	com.facebook.presto.jdbc.PrestoDriver

The Java version must be Java 8 or later, and all users must be granted the query permissions on the system.jdbc table.

Connect to a database

You can use a JDBC URL specified in the following format to connect to a database:

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

JDBC URL examples:

jdbc:presto://master-1-1:8889               # Connect to a database by using the default catalog and the default schema. 
jdbc:presto://master-1-1:8889/hive          # Connect to a database by using a catalog named hive and the default schema. 
jdbc:presto://master-1-1:8889/hive/default  # Connect to a database by using a catalog named hive and a schema named default.

JDBC connection parameters

A JDBC driver supports various parameters. You can use one of the following methods to pass the parameters to DriverManager:

Pass parameters to DriverManager as properties.

String url = "jdbc:presto://<Name of the master node>:8889/hive/default";
Properties properties = new Properties();
properties.setProperty("user", "presto");
Connection connection = DriverManager.getConnection(url, properties);
// ...... Perform operations on your database.

Pass parameters as URL parameters.

String url = "jdbc:presto://<Name of the master node>:8889/hive/default?user=presto";
Connection connection = DriverManager.getConnection(url);
// ...... Perform operations on your database.

The following table describes the common parameters.

Parameter	Data type	Description
user	STRING	The username that is used for identity authentication and authorization.
password	STRING	The password that is used for Lightweight Directory Access Protocol (LDAP) authentication.
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 enable SSL for a Java application to connect to a database based on HTTPS. Default value: false.
SSLKeyStorePath	STRING	The path used to store the keystore file.
SSLKeyStorePassword	STRING	The password used to access the keystore file.
SSLTrustStorePath	STRING	The path used to store the Java truststore file.
SSLTrustStorePassword	STRING	The password used to access the Java truststore file.
KerberosRemoteServiceName	STRING	The name of the Kerberos service.
KerberosPrincipal	STRING	The name of the Kerberos principal.
KerberosUseCanonicalHostname	BOOLEAN	Specifies whether to use a canonical 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 store the Kerberos credential cache.

Example

The following code shows how to use a JDBC driver to connect to a database:

Connection connection = null;
Statement statement = null;
try {
    // The JDBC URL of the component. 
    String url = "jdbc:presto://<Name of the master node>:8889/hive/default";
    Properties properties = new Properties();
    properties.setProperty("user", "presto");
    // 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
    }
  }
}

References

If you want to perform only simple queries, we recommend that you connect to Presto by using the CLI. For more information, see Use the CLI to connect to Presto.