2. Using Microsoft Excel to Query Hive

Use the following instructions to query data from Hive through Microsoft Excel:

  1. On the client machine where you will run Microsoft Excel:

    1. Download the Windows 64-bit Hortonworks ODBC driver from here.

    2. Execute the MSI and follow the instructions to install the ODBC driver.

    3. Set up an ODBC DSN using the following instructions:

      1. For 64-bit ODBC driver: Open the 64-bit ODBC Administrator pane.

      2. Navigate to the System DSN tab.

      3. Click Add, select the Hortonworks Hive driver and click Finish.

      4. Configure the driver using the following instructions:

        1. Under Host, provide the hostname of the cluster node that runs the Apache Hadoop hiveserver2 service.

        2. Under Port, enter the port of the Hive Server 2 service. By default, the Hive Server port is set to 10001.

      5. Choose Username for authentication, and enter 'Hadoop' as the user name.

      6. Click Ok.

  2. You can now use this ODBC DSN from Excel, to see tables in Hive and pull data from these tables.