MicroStrategy Integration with Big Data

MicroStrategy provides a powerful and easy-to-use BI framework for Apache Hadoop by creating a connection between MicroStrategy 9 and CDH (Cloudera’s Distribution Including Apache Hadoop). The Cloudera Connector for MicroStrategy enables accessing Hadoop data through MicroStrategy. The driver achieves this by translating Open Database Connectivity (ODBC) calls from MicroStrategy into SQL and passing the SQL queries to the underlying Impala or Hive engines. The driver supports Cloudera Impala 1.0 and above, and the Apache Hive versions supplied with CDH 4.2 and above.

This paper provides an overview of the steps to establish ODBC connectivity to and from Windows and Linux Operating Systems

1. Download the driver from the Cloudera downloads site. Users will have to register and create a new account on Cloudera’s website before downloading the connector. (http://go.cloudera.com/microstrategy_connector_download)
2. Install the certified ODBC driver.
3. After the driver is installed, run the ODBC Data Source Administrator.
4. In the ODBC Data Source Administrator window, click the System DSN tab, then click Add.
5. Select the Cloudera ODBC Driver for Apache Hive and click Finish.
6. The Hive ODBC DSN Configuration dialog box appears.
7. Fill in the fields with the details of your data source. The data source could be either a Cloudera Impala server or an Apache Hive server.
8. • Enter the hostname or IP address and the port number. To connect to Impala, specify the details for any node in the cluster that is running the impalad daemon.
   • For Impala, leave the Type field blank when connecting to an Impala node secured with Kerberos authentication, or enter HS2NoSASL for the Type field if the node is not secured by Kerberos. For Hive, always leave the Type field blank.
   • The default port number is 21050 for an Impala node, or 10000 for an Apache Hive server.
   • When connecting to a cluster that has Kerberos authentication enabled, fill in the Kerberos principal for the HiveServer2 or Impala service. For example: node1/hiveserver2@myRelm.com

8. Click OK.
9. The installation is now complete

1. Install the 32-bit Cloudera Connector for MicroStrategy for Red Hat on the Linux system that will host the MicroStrategy Intelligence Server.
2. Locate the odbc.ini file found in the home directory of the MicroStrategy installation.
3. Edit the odbc.ini and add the following content at the end of the file to create a new DSN:

[ODBC DATA SOURCES]

IMPALA-SERVER=Hive ODBC Driver

[IMPALA-SERVER]

Driver=odbc_driver_libs_path/libhiveodbc.so.1

Description=Hive ODBC Driver

Host= <provide name of the host server>

Port=<provide port number>

Database=<provide name of the database>

FRAMED=0

Trace=Yes

TraceFile=/tmp/odbc.log

Type=HS2NoSasl[ODBC DATA SOURCES]

HS2=Hive ODBC Driver

[HS2]

Driver=odbc_driver_libs_path/libhiveodbc.so.1

Description=Hive ODBC Driver

Host= <provide name of the host server>

Port=<provide port number>

Database=<provide name of the database>

FRAMED=0

Trace=Yes

TraceFile=/tmp/odbc.log

1. Save and close the odbc.ini file.
2. Edit the ODBC.sh file found in the location <MSTR_HOME_PATH>/env and add the section below to the end of the file. Replace the <FULL_PATH_TO_LOCATION_OF_DRIVER> with the location where the driver is installed.

#

# ODBC Driver for Hive

#

HIVE_CONFIG='<FULL_PATH_TO_DRIVER>/cloudera/20v2’

if [ “${HIVE_CONFIG}” != ‘<HIVE_CONFIG>’ ]; then

export HIVE_CONFIG

mstr_append_path LD_LIBRARY_PATH “${HIVE_CONFIG:?}”/lib

export LD_LIBRARY_PATH

fi

Save and close the ODBC.sh file
Add the following entry to the odbcinst.ini file, which is located in the Home Directory of the MicroStrategy installation on Linux:
[ODBC DRIVERS]

Hive Driver=Installed

[Hive Driver]

Driver=odbc_driver_libs_path/libhiveodbc.so.1

Description=Hive Driver

Setup=odbc_driver_libs_path/libhiveodbc.so.1

APILevel=2

ConnectFunctions=YYY

DriverODBCVer=1.0

FileUsage=0

SQLLevel=1
Note: Substitute the full path to the ODBC driver libraries for odbc_driver_libs_path.Save and close the odbcinst.ini file.
Once the ODBC Connectivity is set up, you can create a new Database Instance.