How to Install and Configure the Hortonworks ODBC driver on Windows 7

Summary

This tutorial describes how to install and configure the Hortonworks ODBC driver on Windows 7.

The Hortonworks ODBC driver enables you to access data in the Hortonworks Data Platform from Business Intelligence (BI) applications such as Microsoft Excel, Tableau, Qlik, Micro Strategy, Cognos, and Business Objects.

Prerequisites:

  • Windows 7
  • Hortonworks Sandbox

Overview

The Hortonworks ODBC driver installation consists of the following steps:

  • Download and install the Hortonworks ODBC driver.
  • Configure the ODBC connection in Windows 7.

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 eth0. The IP address of Eth0 is the address you will use as the IP address when you configure the ODBC connection.

Step 1: Download and Install the Hortonworks ODBC Driver

  • In Windows 7, open a web browser and navigate to http://hortonworks.com/download/. Click the Add-Ons link at the bottom of the Hortonworks Data Platform box.
  • On the Add-Ons page, scroll down to Hortonworks Hive ODBC Driver (Windows + Mac) and select the driver that matches the version of Excel installed on your system (32-bit or 64-bit). For this tutorial, we will configure for Excel 2013 64-bit, so we’ll select Windows 64-bit (msi).
  • Review the Hortonworks license, then click Accept Agreement.
  • To start the download, click Save File on the pop-up message.
  • After the download is complete, double-click to open the downloaded HortonworksHiveODBC64.msi file, then click Run on the pop-up security message to open the setup wizard.
  • To start the installation, click Next on the Hortonworks Hive ODBC Driver Setup Wizard Welcome screen.
  • Review the license agreement, then select the checkbox to accept the license terms. Click Next to continue.
  • Click Next to accept the default installation folder.You can also type in a different location, or click Change to select a different installation folder using a file browser.
  • To begin the installation, click Install.
  • If a reboot is required, a pop-up message will appear. Click OK to continue.
  • When the installation is complete, the setup wizard displays a confirmation message. Click Finish to close the setup wizard.
  • A pop-up message will appear. Close any open applications, then click Yes to restart your system.

Now that you have successfully installed the Hortonworks ODBC driver, the next step is to configure the driver.

Step 2: Configure the Hortonworks ODBC Driver

  • In the Windows Control Panel, select Administrative Tools, then double-click Data Sources (ODBC) to open the ODBC Data Source Administrator.
  • Select the System DSN tab. The Sample Hortonworks Hive DSN should be selected by default; if not, select it. Click Configure to continue.
  • On the Hortonworks Hive ODBC Driver DSN Setup window, type in the settings as shown in the image below. Type the IP address of the Hortonworks sandbox in the Host box. The Authentication mechanism should be set to User Name, and the sandbox user name should be entered in the User Name box (in this case the default user name, “hue”).

Notes

  • The sandbox IP address is displayed in the command prompt window after the sandbox VM (Virtual Machine) starts, and also appears in the browser address box when you open the sandbox.

  • If you are running more than one VM, the last number of the IP address may vary depending on the order in which the sandbox VM was started relative to the other VMs. In this example, the sandbox VM was started first, and its IP address is 198.168.56.101. If the sandbox VM had been started second (after another VM), its IP address would then be 198.168.56.102.

  • The IP address provided in this tutorial is an example – your IP address may be different. Be sure to check the sandbox command prompt window after starting the VM for the IP address used for your sandbox installation.

  • Click Test to test the configuration settings. If the test is successful, a confirmation message appears. Click OK to close the message box.
  • On the Hortonworks Hive ODBC Driver DSN Setup window, click OK to save the driver configuration settings.
  • Click OK to close the ODBC Data Source Administrator window.

Now that you have configured the Hortonworks ODBC driver, you can enable ODBC connections in BI tools such as Microsoft Excel, then use those applications to access data in the Hortonworks Platform.

Comments

|
November 19, 2013 at 12:26 pm
|

This is really awesome feature !! Data can be visualized in better now …. Looking forward to try this out !!

Nia Nam
|
May 23, 2014 at 12:37 am
|

Hello,

I am trying to connect to Excel using Hive Odbc.
But I couldn’t suceed it, because system always shows message “Odbc Administration has stopped” when I click test bitton.

Please let me know some idea to slove this problem.

OS: Windows7
HDP Sandbox: 1.3 and 2.1 (I have tried both versions)
Hortonworks Hive ODBC Driver (Win/Mac – v1.3.19)

Can I download lower version(maybe 1.2) of ODBC Driver?

Thank you.
Best Regards,

Bert Wilson
|
June 29, 2014 at 8:03 pm
|

It would be helpful to update the instructions and screen shots to the current version of VM VirtualBox. It took some trial and error for me to get the connection working because there were a few settings that were not covered in the instructions. (I also had to log in as Admin on my Win 7 to setup and save the ODBC Data Sources but that’s not a big deal.)

Kumar Chinnakali
|
July 9, 2014 at 8:47 am
|

Good flow,

it could be still better if we provide couple of information such as, the 64bit / 34bit odbc selection in Data Source Administrative Tools and IP Address, is it mandate to give exactly same, or it’s optional.

But it’s superb and well informed, Tons of Thanks for the core team !

Adelard
|
July 17, 2014 at 9:21 am
|

I need assistance to setup the private network on the virtual box. The instructions seems a bit confusing. I have tried many attempts with no success. Also, not sure which adapter to use.

I am running Virtual Box 4.3.12 on Windows 7
Hortonworks Sandbox 2.1

Any assistance will be much appreciated!

Thank you in advance.
Adelard

shashi
|
July 28, 2014 at 12:38 pm
|

when i type iconfig , i dont get a IP address. can you please help

    David McDaniel
    |
    July 31, 2014 at 1:16 pm
    |

    Shashi, the command is ifconfig, not iconfig. The easiest way to do this is: ifconfig -a | more.

    David

shashi
|
August 5, 2014 at 12:06 pm
|

i dont see this odbc in drop down when i try to select in excel. please help

    Deepak Chauhan
    |
    September 2, 2014 at 4:04 am
    |

    Check if excel you are using is 32 or 64 bit version (one way is to check from task bar which shows 32/64 written in front of excel process running). You should then download the ODBC driver for that version only. Otherwise, you may see one of the following problems later on when you try to get data into excel sheet:
    1) You will not see the ODBC driver in the list when you try to setup connection from excel
    2) You will get error stating architecture mismatch between driver and application

Samir
|
August 21, 2014 at 6:52 am
|

Hello,

Is there a way to enable tex engine using ODBC driver?

Thanks,
Samir

Deepak Chauhan
|
August 29, 2014 at 2:42 am
|

Excellent and quite a clear step by step tutorial. However, on thing to make sure for making it work properly:

Check if excel you are using is 32 or 64 bit version (one way is to check from task bar which shows 32/64 written in front of excel process running). You should then download the ODBC driver for that version only. Otherwise, you may see one of the following problems later on when you try to get data into excel sheet:
1) You will not see the ODBC driver in the list when you try to setup connection from excel
2) You will get error stating architecture mismatch between driver and application

Ardavan
|
September 5, 2014 at 12:37 pm
|

After configuring ODBC driver- as I attempt to connect to data source I get the following message :
“The specified DSN contains an architecture mismatch between the Driver and Application”
any suggesiton/feedback ?

Stas Zubarev
|
September 16, 2014 at 1:08 pm
|

Some comments:
– Screenshots are not from Win7 😉
– No need to add extra network – just connect your driver to 127.0.0.1
– Setup both drivers 32bit and 64bit. Configure both drivers also.
– To select data from table you need to grant access to table in Hive:
grant SELECT on table to user hue;
grant SELECT on table to user sandbox;

Billy Lam
|
November 4, 2014 at 11:07 pm
|

While doing connectivity test on hive odbc driver, always get connect() failed with remarks: SQLSTATE (34) Error from Hive, error= 10061. Have tried different ips, like 192.168.56.1, 10.0.2.15, 127.0.0.1, or 192.168.56.101, etc… and yet not working.
Please help !

    Mungeol Heo
    |
    November 5, 2014 at 7:36 pm
    |

    Same problem!

    Mungeol Heo
    |
    November 5, 2014 at 8:23 pm
    |

    Hi, try to use sandbox 2.1. I met the same problem when I used 2.2 previewer.
    However, it works at 2.1.
    I think it is because the odbc river does not support 2.2 previewer yet.
    After all, the title is ‘Hive ODBC Driver for HDP 2.1 (v1.4.8)’.

Viswanath
|
December 17, 2014 at 5:53 pm
|

It always give me a timeout error “SQLSTATE: HY000[Hortonworks][HiveODBC] (34) Error from Hive: ETIMEDOUT.” any idea what should i do…I am using with HDP 2.2

vamsi
|
January 6, 2015 at 10:52 pm
|

HORTON HIVE ODBC DRIVER INSTALLATION ERROR SHOWING
“Installation directory should be on local directory”
Even though I have selected for default directory it shows the error ..
Please help…
Thanks in advance.

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>