Sunday, August 14, 2016

Using Apache Drill as a JDBC data source in OBIEE 12c

Apache Drill is an open-source SQL query engine for Big Data exploration. Robin Moffatt from Rittman Mead has written two excellent blog posts about Apache Drill:
I will be experimenting with an approach based on a new feature of OBIEE 12c: the ability to have a JDBC/JNDI based data source in the repository; unfortunately documentation about this feature is limited. The goal of this post is to document the main steps for connecting to Apache Drill via the new JDBC/JNDI interface in OBIEE 12c. One of the advantages of JDBC over ODBC is that there is no need to install and configure client drivers a step that can be tricky especially in *nix systems.
Following are the main steps to be completed for the JDBC/JNDI based data source configuration:
  • Copy the Apache Drill JDBC library to the WebLogic class path
  • Create a JDNI data source in WebLogic
  • Load the Java Data source in the OBIEE Administration Tool
  • Create a database and connection pool for the new data source
  • Import the data source metadata and start building stuff!
Note that for this experiment, I will be using the MapR sandbox already configured with Apache Drill among other tools.

Apache Drill JDBC library file

Once you download/unzip the Apache Drill package file, the JDBC library file to be copied is:
[Apache Drill]\jars\jdbc-driver\drill-jdbc-all-[version].jar
Copy the file to the OBIEE 12c domain directory, lib subfolder
[ORACLE_HOME]\user_projects\domains\bi\lib
and restart the managed server.
Start Drill in cluster mode in the MapR sandbox
image

Create a data source in WebLogic

  • Log into the WebLogic console and create a new data source
image
Lock & Edit the configuration then create a new Data Source
image
Specify a name for the new JDBC data source, a JNDI name, set the database type drop-down to “Other” then click Next/Next. In later steps, we will provide connection details for the database.
image
Next
image
Next
image
Set the values for Driver Class Name field and the JDBC URL
Driver Class Name: org.apache.drill.jdbc.Driver
URL: jdbc:drill:zk=[hostname and port]/drill/cluster-id
where [hostname and port] is the ZooKeeper quorum hostname and port (5181 in this example)
to find out the cluster-id, take a look at the configuration file for running Apache Drill in cluster mode: drill-override.conf (see screenshot below). In my current setup, I am pointing to the MapR Apache Drill sandbox.
image
image
Test the connection to Drill
image
Set the schema to connect to by default in the “System Properties” section
image
Click Next then select the target to deploy the new JDBC data source to
image
Finish.

Load the Java Data source in the OBIEE Administration Tool

Open the OBIEE Administration online then select Load Java Datasources
image
Specify the WebLogic hostname, the managed server port, user and password
image[80]
If the Java data source is loaded successfully
image

Create a database and connection pool for the new data source

Now that the JNDI data source has been successfully loaded into the Administration Tool, we can create a new database and a connection pool in the OBIEE physical layer.
image
We now need to update of the database feature IDENTIFIER_QUOTE_CHAR from the default double quotation mark ( “ ) to a back tick ( `) as required by Apache Drill.
image
Now the exciting part, let’s create a new connection pool:
image
One way to make that your data source is accessible is to open the URL in your web browser
image

Import the Drill data source Metadata

SNAGHTML551da85
Next
SNAGHTML55599ee
The JNDI data source created in WebLogic points to the “hive” schema.
image
A new physical catalog “DRILL” is created along with a physical schema “hive.default”, the schema contains one table “orders”.
image
Let’s view the data
image
Create a simple “Business Model” and analysis et voila!
image
Being able to import a JDBC data source into the OBIEE Administration Tool is a very powerful capability that will make it possible to import new types of data sources into OBIEE and perform analyses, I will be exploring new JDBC based data sources in the near future.
Fiston
a+