This topic describes how to use a Java Database Connectivity (JDBC) driver provided by Presto or Trino to connect to a database. Java applications can connect to a database by using a JDBC driver provided by Presto or Trino.
Add the dependency of a JDBC driver to the pom.xml file in Maven
You can add the required dependency of the Presto or Trino JDBC driver to the pom.xml file based on the version of your E-MapReduce (EMR) cluster.
EMR version | Component version | JDBC driver | Driver class name |
---|---|---|---|
| 3XX |
| io.trino.jdbc.TrinoDriver |
| 3XX |
| io.prestosql.jdbc.PrestoDriver |
Other EMR versions | 0.2XX |
| com.facebook.presto.jdbc.PrestoDriver |
Connect to a database
- If your EMR cluster is of V3.38.0 or a later minor version, or of V5.5.0 or a later minor version, you can use a JDBC URL specified in the following format to connect to a database:
jdbc:trino://<COORDINATOR>:<PORT>/[CATALOG]/[SCHEMA]
JDBC URL examples:jdbc:trino://<Name of the master node> :9090 # Connect to a database by using a catalog and a schema. jdbc:trino://<Name of the master node>:9090/hive # Connect to a database by using a hive catalog and a schema. jdbc:trino://<Name of the master node>:9090/hive/default # Connect to a database by using a hive catalog and the default schema.
Note Replace<Name of the master node>
in the JDBC URLs with the actual name of the master node in your cluster.- For a data lake cluster, the name of the master node is master-1-1.
- For a Hadoop cluster, the name of the master node is emr-header-1.
- If your EMR cluster is of another version, 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://<Name of the master node>:9090 # Connect to a database by using a catalog and a schema. jdbc:presto://<Name of the master node>:9090/hive # Connect to a database by using a hive catalog and a schema. jdbc:presto://<Name of the master node>:9090/hive/default # Connect to a database by using a hive catalog and the default schema.
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:
- EMR cluster of V3.38.0 or a later minor version, or of V5.5.0 or a later minor version
String url = "jdbc:trino://<Name of the master node>:9090/hive/default"; Properties properties = new Properties(); properties.setProperty("user", "hadoop"); Connection connection = DriverManager.getConnection(url, properties); ......
- EMR cluster of another version
String url = "jdbc:presto://<Name of the master node>:9090/hive/default"; Properties properties = new Properties(); properties.setProperty("user", "hadoop"); Connection connection = DriverManager.getConnection(url, properties); ......
- EMR cluster of V3.38.0 or a later minor version, or of V5.5.0 or a later minor version
- Pass parameters to DriverManager as URL parameters:
- EMR cluster of V3.38.0 or a later minor version, or of V5.5.0 or a later minor version
String url = "jdbc:trino://<Name of the master node>:9090/hive/default?user=hadoop"; Connection connection = DriverManager.getConnection(url); ......
- EMR cluster of another version
String url = "jdbc:presto://<Name of the master node>:9090/hive/default?user=hadoop"; Connection connection = DriverManager.getConnection(url); ......
- EMR cluster of V3.38.0 or a later minor version, or of V5.5.0 or a later minor version
The following table describes the common parameters.
Parameter | Format | 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. |
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 remote 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 snippet 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:<trino/presto>://<Name of the master node>: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
}
}
}
FAQ
Q: When I connect to a database by using a JDBC driver, the error message "Authentication failed: Basic authentication or X-Trino-User must be sent"
is displayed. What do I do?
A: The version of the JDBC driver does not match the version of Trino. Use the correct package of the JDBC driver.