Apache Drill connection through Java

Question

Throughout the Wiki of Apache Drill, I could only see queries running via SqlLine client. Is there any programmatical way to run queries in Drill other than the REST API? Any samples or pointers?

Or is it as equivalent as using JDBC driver to run SQL queries?

Answer 1

You can use the Drill JDBC driver, which is documented here: http://drill.apache.org/docs/using-the-jdbc-driver/

Note that if you're building your Java program with Maven, you'll need to install the Drill dependencies locally:

mvn install:install-file -Dfile=/opt/apache-drill-1.0.0/jars/drill-java-exec-1.0.0-rebuffed.jar -DgroupId=org.apache.drill.exec -DartifactId=drill-java-exec -Dversion=1.0.0 -Dpackaging=jar -DgeneratePom=true
mvn install:install-file -Dfile=/opt/apache-drill-1.0.0/jars/drill-common-1.0.0-rebuffed.jar -DgroupId=org.apache.drill -DartifactId=drill-common -Dversion=1.0.0 -Dpackaging=jar -DgeneratePom=true

And here's an example: https://github.com/vicenteg/DrillJDBCExample

Answer 2

Just for the JDBC Part, I use something like this in my Java Code -

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

----------
Testcase
----------
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

import org.apache.drill.exec.ExecConstants;
import org.apache.drill.jdbc.Driver;

import org.testng.Assert;
import org.testng.annotations.Test;

public class TestDrillJdbcDriver {

  /* Drill JDBC Uri for local/cluster zookeeper */
  public static final String DRILL_JDBC_LOCAL_URI = "jdbc:drill:zk=local";

  /* Sample query used by Drill */
  public static final String DRILL_SAMPLE_QUERY = "SELECT * FROM cp.`employee.json` LIMIT 20";


  @Test
  public void testDrillJdbcDriver() throws Exception {
    Connection con = null;

    try {
      con = new Driver().connect(DRILL_JDBC_LOCAL_URI, getDefaultProperties());
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(DRILL_SAMPLE_QUERY);

      int count = 0;
      while (rs.next()) {
        System.out.println(rs.getString(1));
        System.out.println(rs.getString(2));
        System.out.println(rs.getString(3));
        count++;
      }
      Assert.assertEquals(count, 20, "Twenty rows were expected.");
    } catch (Exception ex) {
      System.out.println(ex);
    } finally {
      if (con != null) {
        con.close();
      }
    }
  }

  public static Properties getDefaultProperties() {
    final Properties properties = new Properties();
    properties.setProperty(ExecConstants.HTTP_ENABLE, "false");
    return properties;
  }
}