The Hive ODBC Driver enables client applications such as Excel PowerPivot to access a Hive data warehouse running
on Windows Azure. This driver requires the ODBC Server Port to be opened on the Hadoop Services on Azure (http://www.hadooponazure.com)
portal. This walkthrough provides the following steps that describe how to access Hive on Windows Azure from Excel PowerPivot in detail.
Alternatively, you can watch the following video posted to youtube that demonstrates these steps.
Log in to www.hadooponazure.com (Hadoop Services on Azure) portal. The
Open Ports tile on the portal should indicate whether the
ODBC Server Port is locked.
If it is locked, click the Open Ports tile. Use the slider to enable ODBC Server Port (port number: 10000).
When you navigate back to portal home page, the Open Ports tile now should indicate that the ODBC Server Port is unlocked.
Click Downloads tile on the Hadoop Services on Azure
portal, and download and install either 32-bit/64-bit version of the Hive ODBC Driver
installation file depending on the version of Excel 2010 you have on your computer.
Installing the Hive ODBC Driver is pretty straight forward.
Launch ODBC Data Source Administrator from
Click Add to create a new DSN.
Select HIVE from the list of ODBC drivers.
In the ODBC Hive Setup dialog box, do the following:
Click OK to close the ODBC Data Source Administrator.
Launch Excel 2010, switch to
PowerPivot tab, and click PowerPivot Window on the ribbon to launch
PowerPivot.Click From Other Sources button on the ribbon to launch the
Table Import Wizard.
Select Others (OLEDB/ODBC) since we will be using the ODBC Driver for Hive to access Hive on Azure, and click
Type a friendly name for the connection. Click Build to build the connection string.
Data Link Properties dialog box, switch to the Provider
tab, select Microsoft OLEDB Provider for ODBC Drivers, and click
Select Use Connection String, and click Build.
Switch to the Machine Data Sources tab, select Hive on Azure DSN that you had created earlier using the
ODBC Data Source Administrator, and click OK.
Enter the password for the Hadoop user in the ODBC Hive Setup dialog box and click
OK to close it.
Select the Allow Saving Password option to save the password in the connection string. Click
OK to close the Data Link Properties dialog box.
Click Test Connection in the Table Import Wizard to test the connection.
Click Next. You see two options to import the data. The first one lets you choose from a list of tables and views from which you want to import the data and the second one lets you specify a HiveQL query that you want to run against your
Hive data warehouse to retrieve the data.
In this walkthrough, you select the first option, and click Next.
Select a table from the list and click Preview & Filter to preview and filter the data.
Click Finish to start importing the data from Hive into PowerPivot.
Click Close to see the data in the PowerPivot.