How to Install and Configure the Hortonworks ODBC driver on Mac OS X

This Hadoop tutorial is from the Hortonworks Sandbox – a single-node Hadoop cluster running in a virtual machine. Download to run this and other tutorials in the series. The tutorials presented here are for Sandbox v2.0

Summary

This document describes how to install and configure the Hortonworks ODBC driver on Mac OS X. After you install and configure the ODBC driver, you will be able to access Hortonworks sandbox data using Excel.

In this procedure, we will use Microsoft Excel 2011 to access Hortonworks Sandbox data. You should also be able to access sandbox data using other versions of Excel. The process may not be identical in other versions of Excel, but it should be similar.

Prerequisites:

  • Mac running OS X
  • Hortonworks Sandbox 1.2 (installed and running)
  • Excel 2011

Overview:

To install and configure the Hortonworks ODBC driver on Mac OS X:

  • Download and install the Hortonworks ODBC driver for Mac OS X.
  • Download and install the iODBC Driver Manager for Mac OS X.
  • Configure the Hortonworks ODBC driver
  • Open Excel and test the connection to the Hortonworks Sandbox.

Adding an extra network interface for ODBC

In order for ODBC to work we will have to add a private network between your laptop and the Sandbox Virtual Machine. If the Sandbox VM is running halt it.

Once the VM has been halted open the Virtual Box console. Select Preferences from the Menu bar at the very top of the screen.

Load

The preferences window for Virtual Box will open up.

Load

Select Network and click on the Add icon on the right. Then click on the screwdriver icon to configure the network. Virtual Box will assign an IPv4 Address and subnet.

Load

Click on the DHCP Server tab and configure the DHCP server. When you are done click OK on this screen and OK on the Network screen. This will return you back to the Virtual Box console screen.

Load

Now select your VM and cick on the Settings button. This will open the Setting screen for your VM. Select Network at the top.

Load

Now select Adapter 2. Click on the Enable Network Adapter box. It should be filled in to attach on the Host-only Adapter you created in the Virtual Box preferences. Click OK to close the window.

Load

Boot your VM. Once the Sandbox screen shows on the console hit Alt-F5 to get a login and login as root/hadoop. Type ifconfig and look for eth1. The IP address of Eth1 is the address you will use as the IP address when you configure the ODBC connection.

Load


Step 1: Download and Install the Hortonworks ODBC Driver for Mac OS X

  • Open a web browser and navigate to http://hortonworks.com/hdp/add-ons/.
  • On the Add-Ons page, scroll down to Hortonworks Hive ODBC Driver (Windows + Mac) and select Mac OS X (dmg).

  • Review the Hortonworks license, then click Accept Agreement.

  • A confirmation message appears. Click OK to open the file with the Disk Image Mounter.

  • When the download is complete, the driver package will appear in a new window. Double-click (or right-click with the mouse) the driver package, then select Open.

  • To start the installation, click Continue on the ODBC Driver Installer Welcome screen.

  • Review the license agreement, then click Continue.

  • Click Agree on the pop-up message to agree to the license terms.

  • Click Install to accept the default installation folder.

  • A progress indicator appears while the driver is being installed.

  • When the installation is complete, the Driver Installer displays a confirmation message. Click Close to close the installer.

Now that you have installed the Hortonworks ODBC driver for Mac OS X, the next step is to install the iODBC Driver Manager for Mac OS X.


Step 2: Download and Install the iODBC Driver Manager for Mac OS X

  • Open a web browser and navigate to:

    http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/Downloads

    Scroll down to the Mac OS X section and select the applicable installer package.

    Note: In this tutorial, we successfully installed the latest available version of the iODBC driver for Mac OS X (10.5 Leopard, 10.6 Snow Leopard) on a MacBook Pro running OS X 10.8.3.

  • Click Save File on the confirmation pop-up message.

  • When the installer file download is complete, double-click the file, then select Open.

  • The driver package appears in a new window. Double-click the driver package, then select Open.

  • To start the installation, click Continue on the ODBC Driver Installer Welcome screen.

  • Review the Read Me information, then click Continue.

  • Review the license agreement, then click Continue.

  • Click Agree on the pop-up message to agree to the license terms.

  • Click Install to accept the default installation folder.

  • When the installation is complete, the Driver Installer displays a confirmation message. Click Close to close the installer.

Now that you have installed the iODBC Driver Manager for Mac OS X, the next step is to configure the Hortonworks ODBC .ini files.


Step 3: Configure the Hortonworks ODBC Driver

Overview

To configure the Hortonworks ODBC driver:

  • Enable the Finder to view hidden files.
  • Configure the Hortonworks ODBC .ini files.
  • Set the DYLD\_LIBRARY\_PATH environmental variable.

Procedure

In this procedure we will need to work with files that start with a period. Files that start with a period are considered to be hidden files, and are not usually visible with the Finder. We will run two Terminal commands to make these files visible with the Finder. Please note that these commands are case-sensitive.

    • Open Finder (or click an empty portion of the desktop) to display the Finder menu at the top of the screen. Select Go > Utilities > Terminal, then click Open to open a Terminal window.
    • In the Terminal window, type in the following command, then press the Enter key:

defaults write com.apple.Finder AppleShowAllFiles TRUE

  • In order for the changes to take effect, you must restart the Finder. To restart the Finder, type the following command in the Terminal window, then press the Enter key.

    killall Finder

    You will now be able to view hidden files using the Finder. If you would like to reset Finder later to hide hidden files, use the following Terminal commands to hide hidden files and restart the Finder:

    defaults write com.apple.Finder AppleShowAllFiles FALSE killall Finder

  • Use Go > Go to Folder or the Finder to navigate to the /usr/lib/hive/lib/native/hiveodbc/Setup directory. The Setup directory contains the following sample files:
  • odbc.ini and odbcinst.ini – sample DSN setup files
  • hortonworks.hiveodbc.ini – sample Hortonworks driver configuration file
  • Copy the hortonworks.hiveodbc.ini to the Home directory and rename the file as .hortonworks.hiveodbc.ini (insert a period at the beginning of the file name).
  • In the Home directory, open the .hortonworks.hiveodbc.ini file with TextEdit in plain text mode. Confirm that the line containing the ODBCInstLib setting for the iODBC driver manager is uncommented (does not have a # symbol at the beginning of the line). The path should be set to the location of the libiodbcinst.dylib file by default.

  • Review the contents of the Home directory. If there are no .odbc.ini or .odbcinst.ini files in the Home directory, copy the odbc.ini and .odbcinst.ini files from the /usr/lib/hive/lib/native/hiveodbc/Setup folder to the Home directory.
  • Rename the files to .odbc.ini and .odbinst.ini (insert a period at the beginning of the file name).
  • Use TextEdit in plain text mode to edit the configuration settings in the .odbc.ini file. The host settings should be set to the IP address of the sandbox. PORT should be set to 10000 (the default listening port). HS2AuthMech should be set to 2, and UserName should be set to the sandbox user name (in this case, the default user name “hue”).

  • If there is already an .odbc.ini or .odbcinst.ini file in the Home directory, copy the relevant settings from the odbc.ini and .odbcinst.ini sample files in the /usr/lib/hive/lib/native/hiveodbc/Setup folder to the files in the Home directory. Configure the settings as described in the previous step.
  • The final step is to add the /usr/lib/hive/lib/native/universal directory to the DYLD\_LIBRARY\_PATH environment variable. Open a Terminal window and type the following command, then press the Enter key:

Launchctl setenv DYLD\_LIBRARY\_PATH /usr/lib/hive/lib/native/universal

This command will set the DYLD\_LIBRARY\_PATH variable only for the current session – you will need to run the command again each time you log in. To permanently set the DYLD\_LIBRARY\_PATH environmental variable, navigate to the /etc folder and add the following line to the launchd.conf file:

setenv DYLD\_LIBRARY\_PATH /usr/lib/hive/lib/native/universal

If there is no launchd.conf file in the /etc folder, you can use TextEdit to create a launchd.txt file, then rename it to launchd.conf

Note: You may need to be logged in as an administrator to edit files in the /etc folder.

Now that you have configured the Hortonworks ODBC driver, the next step is to open Excel and test the connection to the Hortonworks Sandbox.


Step 4: Open Excel and Test the Connection to the Hortonworks Sandbox

  • Open a new Excel workbook, then select Data > Get External Data > New Database Query.
  • On the iODBC Data Source Chooser window, select the Hortonworks ODBC data source, then click Test.

  • On the Login pop-up, type “sandbox” in the Username box, then click Ok.

  • It may take several seconds for the connection to be established. After the connection is established, a confirmation message appears. Click OK to close the message.

Now that you have configured the Hortonworks ODBC driver, you can use Excel to access data in the Hortonworks Sandbox.

  • Open a new Excel workbook, then select Data > Get External Data > New Database Query.
  • On the iODBC Data Source Chooser window, select the Hortonworks ODBC data source, then click OK.
  • On the Login pop-up, type “sandbox” in the Username box, then click Ok. The Microsoft Query window and the Tables pop-up box appear.

  • In the Tables pop-up box, select a table, then click Add Table. The table will appear in the top section of the Microsoft Query window. It may take several seconds for the table to appear.

Notes

  • The tables listed in the pop-up have been previously created in the Sandbox with a Create new table command using either HCatalog or Beeswax (Beeswax >Tables).
  • In this tutorial, we selected the omniture table previously created in the “Loading Data into the Hortonworks Sandbox” tutorial, but you can select any available table.

  • To preview the table data, select the table in the Field drop-down, then click Test. The data will appear in the lower section of the Microsoft Query window. It may take several seconds for the data to appear.

  • To import the data into Excel, click Return Data at the bottom right of the Microsoft Query Window, then click OK on the Returning Data to Microsoft Excel pop-up box.

  • The table data will appear in the Excel workbook.

Feedback

We are eager to hear your feedback on this tutorial. Please let us know what you think. Click here to take survey

Comments

Michiel
|
October 7, 2014 at 1:35 pm
|

I followed the tutorial to the letter, but Excel does not recognize the installed ODBC driver :-/

    Renee
    |
    October 13, 2014 at 1:04 pm
    |

    Do you mean it the driver doesn’t show in the iODBC screen? The DSN appeared for me after I realized I had the *.ini files in the wrong location. Have you confirmed they exist in the Home directory?

Rizwan Mian
|
August 22, 2014 at 10:37 am
|

The link provided in the tutorial for the odbc drivers is broken. Instead, see [1].

Troubleshooting bad connection or failure to retrieve data from the Sandbox
– Might need to grant permission in hive for the table being accessed. [2]
– Explore hive logs, typically located in /var/log/hive/
– Enable logging in ~./.hortonworks.hiveodbc.ini to troubleshoot connection or

Also see [3].

[1] http://hortonworks.com/hdp/addons/
[2] http://hortonworks.com/community/forums/topic/sandbox-2-1-odbc-not-working/
[3] http://hortonworks.com/wp-content/uploads/2013/04/Hortonworks-Hive-ODBC-Driver-User-Guide.pdf

Leave a Reply

Your email address will not be published. Required fields are marked *

If you have specific technical questions, please post them in the Forums

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>