Visualize Interactive Query Apache Hive data with Microsoft Power BI using direct query in HDInsight

This article describes how to connect Microsoft Power BI to Azure HDInsight Interactive Query clusters and visualize Apache Hive data using direct query. The example provided loads the data from a hivesampletable Hive table to Power BI. The hivesampletable Hive table contains some mobile phone usage data. Then you plot the usage data on a world map:

HDInsight Power BI the map report

You can leverage the Apache Hive ODBC driver to do import via the generic ODBC connector in Power BI Desktop. However it is not recommended for BI workloads given non-interactive nature of the Hive query engine. HDInsight Interactive Query connector and HDInsight Apache Spark connector are better choices for their performance.

Prerequisites

Before going through this article, you must have the following items:

Load data from HDInsight

The hivesampletable Hive table comes with all HDInsight clusters.

  1. Start Power BI Desktop.

  2. From the menu bar, navigate to Home > Get Data > More....

    HDInsight Power BI Get Data More

  3. From the Get Data window, enter hdinsight in the search box.

  4. From the search results, select HDInsight Interactive Query, and then select Connect. If you don't see HDInsight Interactive Query, you need to update your Power BI Desktop to the latest version.

  5. Select Continue to close the Connecting to a third-party service dialog.

  6. In the HDInsight Interactive Query window, enter the following information and then select OK:

    Property Value
    Server Enter the cluster name, for example myiqcluster.azurehdinsight.net.
    Database Enter default for this article.
    Data Connectivity mode Select DirectQuery for this article.

    HDInsight interactive query Power BI DirectQuery connect

  7. Enter the HTTP credentials, and then select Connect. The default user name is admin.

  8. From the Navigator window in the left pane, select hivesampletale.

  9. Select Load from the main window.

    HDInsight interactive query Power BI hivesampletable

Visualize data on a map

Continue from the last procedure.

  1. From the Visualizations pane, select Map, the globe icon. A generic map then appears in the main window.

    HDInsight Power BI customizes report

  2. From the Fields pane, select country and devicemake. A world map with the data points appears in the main window after a few moments.

  3. Expand the map.

Next steps

In this article, you learned how to visualize data from HDInsight using Microsoft Power BI. For more information on data visualization, see the following articles: