Apache Drill:Running SQL Query from Spring Boot

Apache Drill:Running SQL Query from Spring Boot

This Article will help to you to set up Spring boot project and connect to apace drill through Jdbc driver and perform simple query. just for introduction sake Apache Drill is Apace drill is open source Implementation of Google's Dremel system used by Google BigQuery, Initially released on  second quarter of 2015 and also Drill  is one the top projects of Apace. Which allows to query nosql db without creating any schema to know more follow this url [https://blogs.ashrithgn.com/2019/09/20/apache-drill-why-and-how-basic-usage-features-drill-bit/]

So lets Dive into Setting up Spring boot application

  1. Project Pom.xml of spring boot application
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
   <modelVersion>4.0.0</modelVersion>
   <parent>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-parent</artifactId>
       <version>2.1.8.RELEASE</version>
       <relativePath/> <!-- lookup parent from repository -->
   </parent>
   <groupId>com.example</groupId>
   <artifactId>demo</artifactId>
   <version>0.0.1-SNAPSHOT</version>
   <name>demo</name>
   <description>Demo project for Spring Boot</description>

   <properties>
       <java.version>1.8</java.version>
   </properties>

   <dependencies>

       <dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter-web</artifactId>
       </dependency>

       <dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter-jdbc</artifactId>
       </dependency>

       <dependency>
           <groupId>com.fasterxml.jackson.core</groupId>
           <artifactId>jackson-databind</artifactId>
           <version>2.7.4</version>
       </dependency>

       <!-- https://mvnrepository.com/artifact/org.apache.drill.exec/drill-jdbc-all -->
       <dependency>
           <groupId>org.apache.drill.exec</groupId>
           <artifactId>drill-jdbc-all</artifactId>
           <version>1.16.0</version> //keep same as drill bit version
       </dependency>

       <!-- https://mvnrepository.com/artifact/org.apache.drill.exec/drill-java-exec -->
       <dependency>
           <groupId>org.apache.drill.exec</groupId>
           <artifactId>drill-java-exec</artifactId>
           <version>1.16.0</version> //keep same as drill bit version
       </dependency>


       <dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter-test</artifactId>
           <scope>test</scope>
       </dependency>
   </dependencies>

   <build>
       <plugins>
           <plugin>
               <groupId>org.springframework.boot</groupId>
               <artifactId>spring-boot-maven-plugin</artifactId>
           </plugin>
       </plugins>
   </build>

</project>

  1. Override Jdbc Connection to connect to Apace drill's JDBC
@Configuration
public class DataSourceConnection {
    @Value("${spring.datasource.url}")
    String url;




    @Bean
    public DataSource getSource() {
        DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName("org.apache.drill.jdbc.Driver");
        dataSourceBuilder.url(url);
        dataSourceBuilder.username("");
        dataSourceBuilder.password("");
        return dataSourceBuilder.build();
    }

}
  1. Create Sample Controller and perform the query
@RestController
public class SampleController {


    @Autowired
    JdbcTemplate jdbcTemplate;

    @RequestMapping(path = "/",method = RequestMethod.GET,produces = {"application/json"})
    public List<Map> test() {
        List<Map> result = jdbcTemplate.query("SELECT * FROM mongo.testDb.testCollection",(resultSet, i) -> {
            HashMap<String,String> data = new HashMap<>();
            data.put("2,",resultSet.getString("<column name>"));
            data.put("3,",resultSet.getString("<column name 2>"));
            return data;
        });
        return result;
    }
}
  1. Enable mongo plugin in Drill web ui interface
  2. Start drill in embeded mode and run your java application.
Springboot with apache drill