Connecting Apache Drill Using Spring Boot JDBC Template and Performing SQL Queries On NO-SQL Database

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 no-sql 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 embedded mode and run your java application.