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!
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].jarCopy the file to the OBIEE 12c domain directory, lib subfolder
[ORACLE_HOME]\user_projects\domains\bi\liband restart the managed server.
Start Drill in cluster mode in the MapR sandbox
Create a data source in WebLogic
- Log into the WebLogic console and create a new data source
Lock & Edit the configuration then create a new Data Source
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.
Next
Next
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.
Test the connection to Drill
Set the schema to connect to by default in the “System Properties” section
Click Next then select the target to deploy the new JDBC data source to
Finish.
Load the Java Data source in the OBIEE Administration Tool
Open the OBIEE Administration online then select Load Java Datasources
Specify the WebLogic hostname, the managed server port, user and password
![image[80]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8eEV2wWr4VObFfnZq_6nL73hRk8e4r-j_kTKf_Bi5PTP_YyDOyRwOPRl_HQpkUYmSn68-ml26yaPicElY361UaocWxGob-i8aLHlicQtJIgS9-eoS027Xe37hmy8txZJ6m7XuBy-Tn84m/s1600-h/image803.png "image[80]")
If the Java data source is loaded successfully
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.
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.
Now the exciting part, let’s create a new connection pool:
- Call Interface: JDBC(JNDI)
- Data source name: http://[Hostname and Managed Server Port]/obiee/javads/[JNDI name] for example: http://localhost:9502/obiee/javads/jndi/drill/
One way to make that your data source is accessible is to open the URL in your web browser
Import the Drill data source Metadata
Next
The JNDI data source created in WebLogic points to the “hive” schema.
A new physical catalog “DRILL” is created along with a physical schema “hive.default”, the schema contains one table “orders”.
Let’s view the data
Create a simple “Business Model” and analysis et voila!
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+
