Using the JDBC Driver

This section explains how to install and use the JDBC driver for Apache Drill. To use the JDBC driver, you have to:

  1. Meet prerequisites.
  2. Get the Drill JDBC Driver.
  3. Put the Drill JDBC jar file on the classpath.
  4. Use a valid URL in the JDBC connection string when you write application code or configure BI tools.
  5. Use the name of the Drill driver class in application code or in the configuration of client tools.

Most client tools provide a UI where you can enter all of the required connection information, including the driver location, connection URL, and driver class name. For specific examples of client tool connections to Drill through JDBC, see Using JDBC with SQuirreL and Configuring Spotfire Server.

Prerequisites

Getting the Drill JDBC Driver

The Drill JDBC Driver JAR file must exist on a client machine so you can configure the driver for the application or third-party tool that you intend to use. Obtain the driver in one of the following ways:

  • Copy the drill-jdbc-all JAR file from the following Drill installation directory on a node where Drill is installed to a directory on your client machine: <drill_installation_directory>/jars/jdbc-driver/drill-jdbc-all-<version>.jar

    Or

  • Download a TAR file for the latest Drill release to a location on your client machine, and extract the file. On Windows, you may need to use a decompression utility, such as 7-zip. The driver is extracted to the following directory: <drill-installation_directory>/jars/jdbc-driver/drill-jdbc-all-<version>.jar

Using the JDBC URL for a Random Drillbit Connection

The format of the JDBC URL differs slightly, depending on the way you want to connect to the Drillbit: random, local, or direct. This section covers using the URL for a random or local connection. Using a URL to directly connect to a Drillbit is covered later. If you want ZooKeeper to randomly choose a Drillbit in the cluster, or if you want to connect to the local Drillbit, the format of the driver URL is:

jdbc:drill:zk=<zk name>[:<port>][,<zk name2>[:<port>]... <directory>/<cluster ID>;[schema=<storage plugin>]

where

  • jdbc is the connection type. Required.
  • schema is the name of a storage plugin configuration to use as the default for queries. For example,schema=hive. Optional.
  • zk name specifies one or more ZooKeeper host names, or IP addresses. Use local instead of a host name or IP address to connect to the local Drillbit. Required.
  • port is the ZooKeeper port number. Port 2181 is the default. On a MapR cluster, the default is 5181. Optional.
  • directory is the Drill directory in ZooKeeper, which by default is /drill. Optional.
  • cluster ID is drillbits1 by default. If the default has changed, determine the cluster ID and use it. Optional.

Determining the Cluster ID

To determine the cluster ID, check the following file:

<drill-installation>/conf/drill-override.conf

For example:

... drill.exec: { cluster-id: "docs41cluster-drillbits", zk.connect: "centos23.lab:5181,centos28.lab:5181,centos29.lab:5181" } ...

URL Examples

Single-Node Installation

jdbc:drill:zk=maprdemo:5181

jdbc:drill:zk=centos23.lab:2181/drill/docs41cluster-drillbits

jdbc:drill:zk=10.10.100.56:2181/drill/drillbits1;schema=hive

Cluster Installation

jdbc:drill:zk=10.10.100.30:5181,10.10.100.31:5181,10.10.100.32:2181/drill/drillbits1;schema=hive

Using the JDBC URL Format for a Direct Drillbit Connection

If you want to connect directly to a Drillbit instead of using ZooKeeper to choose the Drillbit, replace zk=<zk name> with drillbit=<node name> as shown in the following URL:

jdbc:drill:drillbit=<node name>[:<port>][,<node name2>[:<port>]... <directory>/<cluster ID>[schema=<storage plugin>]

where

drillbit=<node name> specifies one or more host names or IP addresses of cluster nodes running Drill.

The tries Parameter

As of Drill 1.10, you can include the optional tries=<value> parameter in the connection string, as shown in the following URL:

jdbc:drill:drillbit=<node name>[:<port>][,<node name2>[:<port>]...
<directory>/<cluster ID>;[schema=<storage plugin>];tries=5

The “tries” option represents the maximum number of unique drillbits to which the client can try to establish a successful connection. The default value is 5. This option improves the fault tolerance in the Drill client when first trying to connect with a drillbit, which will then act as the Foreman (the node that drives the query).

The order in which the client tries to connect to the drillbits may not occur in the order listed in the connection string. If the first try results in an authentication failure, the client does not attempt any additional tries. If the number of unique drillbits listed in the drillbit parameter is less than the “tries” value, the client tries to connect to each drillbit one time.

For example, if there are three unique drillbits listed in the connection string, and the “tries” value is set to 5, the client can try to connect to each drillbit once, until a successful connection is made, as shown in the image below:

If the client cannot successfully connect to any of the drillbits, Drill returns a failure message.

For definitions of other URL components, see Using the JDBC URL for a Random Drillbit Connection.

Using the Drill Driver Class Name

The class name for the JDBC driver is org.apache.drill.jdbc.Driver. For details, see the Apache Drill JDBC Driver Javadoc.

Starting in Drill 1.16, the DrillStatement interface supports the setMaxRows method. The setMaxRows method sets a limit on the number of rows returned for a result set. The limit set is applied automatically at runtime. By default, there is no limit on the number of rows returned. See Setting an Auto Limit on the Number of Rows Returned for Result Sets.

Starting in 1.13, the DrillStatement interface supports the setQueryTimeout method. The setQueryTimeout method limits the amount of time that the JDBC driver allows a query to run before canceling the query. The setQueryTimeout method sets the number of seconds that the JDBC driver waits for a Statement object to execute before canceling it. By default, there is no limit on the amount of time allowed for a running statement to complete. When you configure a limit, an SQLTimeoutException is thrown if a statement exceeds the limit. A JDBC driver must apply this limit to the execute, executeQuery, and executeUpdate methods.

Example of Connecting to Drill Programmatically

The following sample code shows you how to use the class name in a snippet to connect to Drill using the Drill-Jdbc-all driver:

Class.forName("org.apache.drill.jdbc.Driver");
Connection connection =DriverManager.getConnection("jdbc:drill:zk=
node3.mynode.com:2181/drill/my_cluster_com-drillbits");
Statement st = connection.createStatement();
ResultSet rs = st.executeQuery("SELECT * from cp.`employee`");
while(rs.next()){
System.out.println(rs.getString(1));
}

Ensuring the completion of CTAS queries

When a JDBC client issues a CTAS (CREATE TABLE AS SELECT …) statement then Drill will return a record for each completed writer fragment containing the number of records that fragment wrote. These records are returned in the usual streaming fashion as writer fragments complete, their order being unknowable in advance. If the client application immediately closes its clientside JDBC resources after its call to Statement.executeQuery has returned as follows

Statement ctasStatement = conn.createStatement();
ResultSet ctasResults = ctasStatement.executeQuery(ctasQueryText);
ctasResults.close();
ctasStatement.close();

then it may be that the CTAS statement is still executing, and that it is unintentionally cancelled before completing depending on luck with respect to timing.

This unintended cancellation of the CTAS statement is usually benign if it spawned only one writer fragment, but if it spawned more than one then the chances increase that at least one writer will be interrupted before it has finished writing, resulting in incomplete or even corrupted output. Even the benign cases of such queries conclude in the CANCELLED state rather than the COMPLETED state resulting in misleading query logs and profiles.

To have CTAS queries reliably run to completion the JDBC client should wait for all of the writer fragments to complete by scrolling through the returned ResultSet before closing it or the Statement that created it. Using try-with-resources syntax,

try (
  Statement ctasStatement = conn.createStatement();
  ResultSet ctasResults = ctasStatement.executeQuery(ctasQueryText);
) {
  while (ctasResults.next()); // scroll through results to ensure that we wait for CTAS completion
}

See also: the exec.query.return_result_set_for_ddl config option.

← Interfaces IntroductionUsing JDBC with SQuirreL on Windows →