Apache Drill - JDBC Interface

Apache Drill provides JDBC interface to connect and execute queries. We can use JDBC interface in JDBC based SQL Client like “SquirreL SQL Client” and work on all the features of drill. We can use the same JDBC interface to connect drill from our Java based application. Let us see how to connect drill and execute commands in our sample Java application using JDBC interface in this section.

Java Application

Apache Drill provides a JDBC driver as a single jar file and it is available @ /path/to/drill/jars/jdbc-driver/drill-jdbc-all-1.6.0.jar. The connection string to connect the drill is of the following format −

jdbc:drill:zk = <zk_host>:<zk_port>
jdbc:drill:zk = <zk_host>:<zk_port>/<zk_drill_path>/<zk_drillbit_name
jdbc:drill:zk = <zk_host>:<zk_port>/<zk_drill_path>/<zk_drillbit_name;schema = hive

Considering ZooKeeper is installed in the local system, the port configured is 2181, the drill path is “drill” and drillbit name is “drillbits1”, the connection string may be among the following commands.

jdbc:drill:zk = localhost:2181
jdbc:drill:zk = localhost:2181/drill/dillbits1
jdbc:drill:zk = localhost:2181/drill/dillbits1;schema = hive

if the drill is installed in a distributed mode, we can replace the “localhost” with the list of drill installed system IP/name as shown in the following command.

jdbc:drill:zk = 1.2.3.4:2181,5.6.7.8:2181/drill/dillbits1;schema = hive

The connection to drill is just like any other JDBC interface. Now, create a new maven project with "com.tutorialspoint.drill.samples" as the package name and “connect-drill” as the application name.

Then, update the following code in “App.java” file. The coding is simple and self-explanatory. The query used in the application is the default JSON file packaged into drill.

Coding

package com.tutorialspoint.drill.samples;
import java.sql.*;
import java.lang.*;
public class App{

   public static void main( String[] args ) throws SQLException, ClassNotFoundException{
   
      // load the JDBC driver
      Class.forName("org.apache.drill.jdbc.Driver");
      
      // Connect the drill using zookeeper drill path
      Connection connection = DriverManager.getConnection("jdbc:drill:zk = localhost:2181/drill/drillbits1");
      
      // Query drill
      Statement st = connection.createStatement();
      ResultSet rs = st.executeQuery("SELECT * from cp.`employee.json` LIMIT 3");
      
      // Fetch and show the result
      while(rs.next()){
         System.out.println("Name: " + rs.getString(2));
      }
   }
}

Now add following drill dependency tag to “pom.xml” file.

<dependency>
   <groupId>org.apache.drill.exec</groupId>
   <artifactId>drill-jdbc-all</artifactId>
   <version>1.1.0</version>
</dependency>

Now, you can compile the application by using the following command.

mvn clean package

Once the application is compiled, execute it using the following command.

java -cp target/connect-drill-1.0.jar:/path/to/apache-drill-1.6.0/jars/
   jdbc-driver/drill-jdbc-all-1.6.0.jar com.tutorialspoint.drill.samples.App

The output of this application list is the name of the first three employees available in “employee.json” file and it will show in the console as shown in the following program.

Result

Name: Sheri Nowmer
Name: Derrick Whelply
Name: Michael Spence
Advertisements