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/addons/.
  • 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/DownloadsScroll 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

Procedure

Overview to configure the Hortonworks ODBC driver:
  • Enable the Finder to view hidden files.
  • Configure the Hortonworks ODBC .ini files.
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
  • Navigate to the /usr/lib/hive/lib/native/hiveodbc/Setup directory on the terminal using cd /usr/lib/hive/lib/native/hiveodbc/Setup
  • Type command ls The Setup directory contains the following sample files:
    • odbc.ini
    • odbcinst.ini
  • Copy both the files and move them to your root directory.
    Use cp ./* ~
  • The /usr/lib/hive/lib/native/universal directory contains hortonworks.hiveodbc.ini – sample Hortonworks driver configuration file inside.
  • Copy the hortonworks.hiveodbc.ini to the Home directory using following command.
cd /usr/lib/hive/lib/native/universal

ls

cp hortonworks.hiveodbc.ini ~
  • Now your root directory should contain odbc.ini, odbcinst.ini and hortonworks.hiveodbc.ini files. Type following to verify if your files are moved into root directory.
cd ~

ls
  • Open the .odbc.ini file. Enter the host address at which your sandbox resolves into HOST settings. PORT should be 10000(default listening port). HS2AuthMech should be set to 2, and UID should be set to the sandbox user name (in this case, the default user name "sandbox").
NOTE
  • If you already have.odbc.ini file in the Home directory, just copy the relevant HOST, PORT and UID settings to the file.
vi .odbc.ini
Press
i
Type the host address HOST = (the ip address your sandbox runs on) Type port address
PORT=10000
Type username
UID=sandbox
Hit escape button Type
:x
to save the changes made to the file. image00
  • If there is already an .odbc.ini and .odbcinst.ini file in the Home directory, copy the relevant settings from the odbc.ini file as described in the previous step.
  • Once you have the required three files in your home directory, rename the file by insert a period at the beginning of these file names.
mv odbc.ini .odbc.ini
mv odbcinst.ini .odbcinst.ini
mv hortonworks.hiveodbc.ini .hortonworks.hiveodbc.ini
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

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

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?

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=""> <s> <strike> <strong>