Connect Apache Drill via Java

Question

Throughout the wiki for drilling apache, I could only see requests launched using the sqline client. Is there any programmatic way to run a request in Drill other than REST Api? Any patterns or pointers?

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

Answer 1

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

Note that if you are creating your Java program using Maven, you will need to install 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 is an example: https://github.com/vicenteg/DrillJDBCExample

Answer 2

For the JDBC part only, I use something similar 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; } } 

Answer 3

Besides sqlline, you can use

• Drill a web interface to run queries. The default port is 8047 on a local (built-in) installation.
• Or download and configure the MapR ODBC driver, and it will include Drill Explorer (another user interface), which can be used. https://drill.apache.org/docs/installing-the-driver-on-windows/
• Or configure any other tool that works with ODBC (I configured Teradata SQL Assistant for Drill using the MapR ODBC driver)
• JDBC Integration - Use SQuirrel or DBVisualizer through which you can run queries. https://drill.apache.org/docs/using-jdbc-with-squirrel-on-windows/
• Programmatically will be through JDBC. (using drill-jdbc-all-1.0.0jar and org.apache.drill.jdbc.Driver)

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