Query Hive Tables with Ambari Hive Views in HDInsight

This is an introductory walk through of querying hive tables and visualizing the data in the Ambari Hive View. This is another option to build and debug HiveQL other than in Visual Studio with the Azure Data Lake Tools plugin. In my blog article Populating data into hive tables, I demonstrated populating internal and external hive tables and this article will build on that.

Hive is a data warehouse infrastructure built on top of Hadoop. It provides tools to enable data ETL, a mechanism to put structures on the data, and the capability to query and analyze large data sets that are stored in Hadoop. The Hive View is designed to help you author, execute, understand, and debug Hive queries.”

https://docs.hortonworks.com/HDPDocuments/Ambari-2.2.0.0/bk_ambari_views_guide/content/ch_using_hive_view.html

  1. Go to https://<yourcluster>.azurehdinsight.net
  2. Click on the top right waffle > Hive View
    Query Hive Tables with Ambari Hive Views in HDInsight-1
  3. In my Hive, I click on my database usdata and its two tables crimes and crimes_ext. You can see its schema.
    Query Hive Tables with Ambari Hive Views in HDInsight-2
  4. I query the number of rows in my crimes table to show it being quite large.
    Query Hive Tables with Ambari Hive Views in HDInsight-3
  5. A query to show the number of each type of crime for years before or at 2017.
    Query Hive Tables with Ambari Hive Views in HDInsight-4
    The execution took about 2-3 mins with 2 worker nodes in the cluster.
  6. Clicking on Visualization tab on the right, I am able to visualize the data in my last query. The red squares indicate settings and clicks to arrive to the stacked bar chart.Query Hive Tables with Ambari Hive Views in HDInsight-5
  7. Click Data Explorer to explore to see various charts defined automatically based on the columns in your query.
    Query Hive Tables with Ambari Hive Views in HDInsight-6

I have shown a brief introduction of Ambari Hive views with queries. This is one of the tools for data analysts and developers to quickly and easily build hive queries.


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s