OBIEE 12c - Apache Drill Configuration - JDBC

Apache Drill is an open-source SQL Query Engine for Big Data Exploration.  Of the several features  of Drill the below certainly caught my attention:
- Point and Query
- Ability to Query across several databases ie RDBMS, Hive, HDFS etc.,

More information about this can be found on Apache Web Site, which is comprehensive enough for one to get going.

In OBI world , performance has been a bottle neck to query data that existed in multiple databases ie data combined from multiple sources.  This blog post is an attempt to see how well OBI/Drill configuration will cope up with this challenge.

I have chosen OBI 12c for this test, as it supports creation of JDBC data sources and there is no need to install any ODBC/JDBC clients which can be painful on *nix systems.

Apache Drill Installation

Installation of Drill is quite easy, download the latest version of Apache Drill from Download Drill  or from the Apache Drill Mirror site.

Once downloaded, extract the contents of the .tar.gz file.

That's all is needed for installation.

How to Start Drill

- Navigate to the Installation Directory

- Issue the below command to start Drill:

   /bin/drill-embedded

Once started, you should see the prompt to Drill as below:

JDBC Datasource Configuration

The first step in the configuration of Drill for OBI is to copy the JDBC driver from Apache Drill installation location to OBI 12c Domain directory.

And then the JDBC configuration need to be completed in the Weblogic Server.

Create JDBC Data Source in Weblogic

1. If you have not already done so, in the Change Center of the Administration Console, click Lock & Edit.
2. In the Domain Structure tree, expand Services, then select Data Sources.
3. On the Summary of Data Sources page, click New and select Generic Data Source.
4. On the JDBC Data Sources Properties page, enter the details as shown below
   
5. Next, Next ( accept defaults)
6. In the Test Configuration screen, provide the Drill driver and ZooKeeper connection details.  In my case, I have installed the drill on local machine, single node in embedded mode.  Hence my URL is for direct drillbit connection is of the format:  jdbc.drill.drillbit=
7. Once the configuration is tested successfully, Next.
8. Provide targets as below 
   
9. Finish

This ends the creation of JDBC Data source.

The next step is to import JDBC data sources in OBI Admin tool.

OBI Admin Tool - Loading JDBC data source

A new feature available with OBI 12c is the ability to load JDBC data sources.  To start using the JDBC data sources in OBI 12c follow these steps.

1.  First load the JDBC data sources into RPD as shown below:

2. When prompted provide the FMW Managed Server Details, as below:  Please note the port number required is the Managed Server Port.

3. Click OK, when everything is correct you should see the successful connection.

4.Now create a new physical database and associated connection pool for the JDBC Data Source as below:

5.  Before importing the metadata the data base properties need to be amended to cater for the Drill syntax for writing table names ( surrounded by ` character).

6.Now Import the Metadata and you should see default connections configured in Drill listed to start import process.

Now you can start importing the tables/view as you do for any relational db and start modelling your RPD.

Thanks to Robin Moffatt for his blog on the OBI - Drill ODBC config