Oracle Business Intelligence and Cloudera Impala configuration
It's pretty easy to find a guide for connecting OBI EE to Cloudera's Impala when OBI EE is installed on MS Windows. But I haven't found any guides for Linux platform. So, here it is.
Steps to connect OBI EE to Cloudera Impala when OBI EE is installed on Linux are the following:
- Download ODBC drivers
- Install and configure ODBC drivers on Linux (server side)
- Install and configure ODBC drivers on Windows (client side)
- Create and configure physical layer of repository
Downloading ODBC drivers
All the drivers you need are located on Cloudera's website and are available for download for free. Go to Download Cloudera Connectors page and select "Impala Drivers & Connectors".
Locate and download drivers for your platform. I use Oracle Linux 6.5 64 bit for my server and MS Windows 7 64 bit for my client machine.
You will get two files. One is a Linux ODBC driver, and another one is a Windows driver.
Installing and configuring ODBC drivers on Linux
Copy ODBC driver file to the server and install it using YUM (or any other package manager used in your Linux distributive).
ODBC driver will be installed to /opt/cloudera/impalaodbc directory.
Then you need to configure ODBC connection to Impala host. You do it by editing odbc.ini file. It's located at <MIDDLEWARE_HOME>/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup directory.
Add the following lines to this file. Change HOST and PORT to your actual parameters.
Description=Cloudera ODBC Driver for Impala (64-bit) DSN
The next configuration file you need to change is cloudera.impalaodbc.ini file. It is located in /opt/cloudera/impalaodbc/lib/64 directory.
The default value for DriverManagerEncoding parameter is UTF-32.
Change it to UTF-16.
This configuration should be sufficient for your OBI EE server to connect to Impala server.
Installing and configuring ODBC drivers on Windows
These steps are needed to allow developers to connect to Impala host and import metadata for building a repository.
Install ODBC driver on a MS Windows machine used by a developer. And then create a system ODBC connection. Change Host and Port parameters. Data Source Name should be the same as the name of connection on Linux.
Creating physical layer
Start Oracle BI Administration Tool and create a new repository. When importing metadata select Connection type: ODBC 3.5 and chose previously created connection.
Import metadata as usual.
You'll get a new database created in the physical layer of the repository. It needs a couple of tweaks before use.
The firs one: open database properties and change Database type from ODBC 3.5 to Apache Hadoop.
The second one: open Connection Pool properties and make sure that Call interface is ODBC 2.0 and disable Shared logon option.
After that, create a repository as usual.
Now you can use Impala's data for creating reports using OBIEE!
Have a nice day!
I'm doing my best to provide as detailed and simple guide as I can. But sometimes I can fail. So, don't hesitate to leave a comment, ask a question, say if everything is understandable or not. No registration is required for commenting.
OBIEE specialist since 2007 and Oracle Discoverer before. DWH architect, BI enthusiast, blogger. Lazy cats owner. All opinions are my own and not the views of my employer.