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?

Ranson

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

Percentage

With Drill 1.1.0, the Drill jars are now in Maven Central. – Percentage

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;
  }
}

Immerge

Answer 3

Other than sqlline you can use

1. Drill Web UI to run queries. Default port is 8047 on local (embedded) installation.
2. Or download and configure MapR ODBC driver and it comes will Drill Explorer (another UI) which can be used. https://drill.apache.org/docs/installing-the-driver-on-windows/
3. Or configure any other tool that works with ODBC (i have configured Teradata SQL Assistant to Drill using MapR ODBC driver)
4. JDBC integration - Use SQuirrel or DBVisualizer through which you can run queries. https://drill.apache.org/docs/using-jdbc-with-squirrel-on-windows/
5. Programmatically would be through JDBC. ( using drill-jdbc-all-1.0.0jar and org.apache.drill.jdbc.Driver class)

Kloof

Answer 4

Other way to execute query by Calling REST API in Apache Drill

public class RequestQuery {

private String queryType;
private String query;

public String getQueryType() {
    return queryType;
}
public void setQueryType(String queryType) {
    this.queryType = queryType;
}
public String getQuery() {
    return query;
}
public void setQuery(String query) {
    this.query = query;
}

}

import javax.ws.rs.client.Client;
import javax.ws.rs.client.ClientBuilder;
import javax.ws.rs.client.Entity;
import javax.ws.rs.client.WebTarget;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.ramyam.eis.apache.drill.api.model.RequestQuery;

public class RestAPI {

    private static Log logger = LogFactory.getLog(RestAPI.class);
    public static void main(String[] args) {
        getQueryResponce();
    }

    private static void getQueryResponce(){
        Client client = null;
        WebTarget target = null;
        try {
            logger.info("---------Started execution-----------");
            RequestQuery query = new RequestQuery();
            query.setQueryType("SQL");
            //MySQL
            //query.setQuery("SELECT * FROM MYSQL.foodmart.collections");
            //query.setQuery("SELECT * FROM MYSQL.foodmart.collections limit 100");
            //query.setQuery("SELECT payment_due_from,NumItems FROM MYSQL.foodmart.collections limit 10");
            //query.setQuery("SELECT count(SPORTS_PREFERENCE) FROM MYSQL.foodmart.collections group by SPORTS_PREFERENCE");
            //query.setQuery("SELECT DISTINCT payment_due_from FROM MYSQL.foodmart.collections ");

            //Mongo DB
            //query.setQuery("select * from mongo.apache_drill.pt_BMS_preferences_data where SPORTS_PREFERENCE = 'Cricket' limit 10");
            //query.setQuery("SELECT COUNT(SPORTS_PREFERENCE) FROM mongo.apache_drill.pt_BMS_preferences_data GROUP BY SPORTS_PREFERENCE");
            query.setQuery("SELECT DISTINCT(SPORTS_PREFERENCE) FROM mongo.apache_drill.pt_BMS_preferences_data ");
            client = ClientBuilder.newClient();
            target = client.target("http://localhost:8047/query.json");
            //target = target.path(path);
            Response response = target.request().accept(MediaType.APPLICATION_JSON)
                    .post(Entity.json(query), Response.class);
            if (response.getStatus() != 200) {
                throw new RuntimeException("Failed : HTTP error code : " + response.getStatus());
            }
            String string = response.readEntity(String.class);
            logger.info(query.getQueryType()+"->"+query.getQuery());
            logger.info("Responce:\n"+string);
            logger.info("---------End execution-----------");
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage(),e);
        }
    }

Cenotaph