use hive to calculate salary statistics for georgia educators (third of a three-part series)

This post represents the completion of the trilogy started in use mapreduce to calculate salary statistics for georgia educators (first of a three-part series) and use pig to calculate salary statistics for georgia educators (second of a three-part series).  As the name suggests, we're going to try to solve a simple question (specifically the one listed at Simple Open Georgia Use Case) with Apache Hive and compare/contrast a bit on this approach vs. MapReduce or Pig.

As before, be sure to check out the content at Open Georgia Analysis & Preparing Open Georgia Test Data for the context of this discussion and make sure you have a working copy of the Hortonworks Sandbox if you want to test any of the information presented for yourself.

The first thing we need to do is build a Hive table on top of the data we previously loaded at /user/hue/opengeorgia/salaryTravelReport.csv in HDFS.  There are many different options that can be explored when creating a Hive table, but we're looking for simplicity so we will emulate the instructions from the "Create tables for the Data Using HCatalog" section of the Sandbox's Tutorial #4.

I went ahead and created a new database called opengeorgia via Hue's HCat UI, as well.  I then created a table named salary using the CSV file above as the "Input File" and used the options visualized below (pardon the "chop job").

When reproducing the actions from this blog out on HDP 1.3 for Windows that I set up during installing hdp on windows (and then running something on it) I realized that the screenshot below omitted the orgType column (string datatype).  You can see that in https://github.com/lestermartin/hadoop-exploration/blob/master/src/main/hive/opengeorgia/CreateSalaryTable.hql.

Now, do a quick double-check by running a "select count(*) from opengeorgia.salary;" query from Hue's Query Editor within the Beeswax (Hive) UI.  You should be told there are 76,943 rows in the newly created table if you built (or just downloaded) the file described in Format & Sample Data for Open Georgia.  To actually answer the question raised in Simple Open Georgia Use Case you really won't have to do much more than that as seen below.  This query is also saved as TitleBreakdownForSchoolsIn2010.hql in the GitHub > lestermartin > hadoop-exploration project.

The results in Hue should look like the following.

Walking these results we see the same answers as we did with MapReduce and Pig.  Our spot-checks were to double-check that the 9 Audiologists's salaries (see screenshot above) averaged out to be $73,038.71 and later in the results that the highest paid 3rd Grade Teacher from this dataset made $102,263.29.  I also verified that there were 181 rows reported (note: Hue's query results begin with 0, not 1).

Obviously, we expected all the computations to deliver the same results.  The real purpose of these three blog postings was to show that there are different tools in the Hadoop ecosystem and that each have their own sweet spot.  For this well-formed data and simple/typical reporting use case it is easy to see that Hive makes much more sense than MapReduce or even Pig.  As I said before, the answer is most often "it depends" and this is clearly not a "Hive is best for everything" statement, so please take it in the spirit it was offered.

I hope you got something out of this post and its predecessors; use mapreduce to calculate salary statistics for georgia educators (first of a three-part series) and use pig to calculate salary statistics for georgia educators (second of a three-part series).  Comments & feedback are always appreciated – even if they are critical; well... constructively critical.