All Products
Search
Document Center

E-MapReduce:Use JDBC to connect to Trino

Last Updated:Mar 22, 2024

After you create an E-MapReduce (EMR) cluster that contains the Trino service, you can use the JDBC driver provided by Trino to connect to a Trino 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 or Trino JDBC driver to the pom.xml file based on the version of your EMR cluster.

EMR version

Component version

JDBC driver

Driver class name

  • EMR 3.X series: EMR V3.38.0 and later

  • EMR 5.X series: EMR V5.5.0 and later

3XX

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

io.trino.jdbc.TrinoDriver

  • EMR 3.X series: EMR V3.25.0 to EMR V3.37.X

  • EMR 4.X series: EMR V4.3.0 to EMR V4.9.0

  • EMR 5.X series: EMR V5.2.1 to EMR V5.4.3

3XX

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

io.prestosql.jdbc.PrestoDriver

Other EMR versions

0.2XX

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

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]

    Parameter description:

    • <COORDINATOR>: the name or IP address of the master node in your cluster.

    • <PORT>: the port number of Trino.

    • [CATALOG]: the catalog name of the Trino database that you want to connect to. This parameter is optional.

    • [SCHEMA]: the schema name of the Trino database that you want to connect to. This parameter is optional.

    JDBC URL examples:

    jdbc:trino://<Name of the master node>:9090               # Connect to a database by using the default catalog and the default schema. 
    jdbc:trino://<Name of the master node>:9090/hive          # Connect to a database by using a catalog named hive and the default schema. 
    jdbc:trino://<Name of the master node>:9090/hive/default  # Connect to a database by using a catalog named hive and a schema named default.
    Note

    Replace <Name of the master node> in the JDBC URLs with the actual name of the master node in your cluster.

    • For a DataLake 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 a cluster of another EMR version is used, 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 the default catalog and the default schema. 
    jdbc:presto://<Name of the master node>:9090/hive          # Connect to a database by using a catalog named hive and the default schema. 
    jdbc:presto://<Name of the master node>:9090/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.

    • 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);
      // ...... Perform operations on your database.
    • 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);
      // ...... Perform operations on your database.
  • Pass parameters 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);
      // ...... Perform operations on your database.
    • EMR cluster of another version

      String url = "jdbc:presto://<Name of the master node>:9090/hive/default?user=hadoop";
      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.

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:<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

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?

In most cases, the error occurs because the version of the JDBC driver does not match the version of Trino. To fix the error, you can use a JDBC driver that is compatible with the version of Trino that you connect to.

References

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