When to use Pig Latin versus Hive SQL?

Once your big data is loaded into Hadoop, what’s the best way to use that data?  You’ll need some way to filter and aggregate the data, and then apply the results for something useful.  Collecting terabytes and petabytes of web traffic data is not useful until you have a way to extract meaningful data insights out of it.

That’s where MapReduce comes in.  MapReduce permits you to filter and aggregate data from HDFS so that you can gain insights from the big data.  However, writing MapReduce code with basic Java may require you to write many lines of code laboriously, with additional time needed for code review and QA.

So instead of writing plain Java code to use MapReduce, you now have the options of using either the Pig Latin or Hive SQL languages to construct MapReduce programs.  (There’s also another option to use the Hadoop Streaming protocol with STDIN and STDOUT with any language such as Python or even BASH shell script, but we’ll explore that option more on another day.)  The benefit is that you only need to write much fewer lines of code, thus reducing overall development and testing time.  The rule of thumb is that writing Pig scripts takes 5% of the time compared to writing MapReduce programs in Java, while reducing runtime performance by only 50%.  Although Pig and Hive scripts generally don’t run as fast as native Java MapReduce programs, they are vastly superior in boosting productivity for data engineers and analysts.

When should you use Pig Latin and when should you use Hive?

Depending on where you work, you may need to simply use whatever standards your company has established.

For example, Hive is commonly used at Facebook for analytical purposes.  Facebook promotes the Hive language and their employees frequently speak about Hive at Big Data and Hadoop conferences.

However, Yahoo! is a big advocate for Pig Latin.  Yahoo! has one of the biggest Hadoop clusters in the world.  Their data engineers use Pig for data processing on their Hadoop clusters.

Alternatively, you may have a choice of Pig or Hive at your organization, especially if no standards have yet been established, or perhaps multiple standards have been set up.

If you know SQL, then Hive will be very familiar to you.  Since Hive uses SQL, you will feel at home with all the familiar select, where, group by, and order by clauses similar to SQL for relational databases.  You do, however, lose some ability to optimize the query, by relying on the Hive optimizer.  This seems to be the case for any implementation of SQL on any platform, Hadoop or traditional RDBMS, where hints are sometimes ironically needed to teach the automatic optimizer how to optimize properly.

However, compared to Hive, Pig needs some mental adjustment for SQL users to learn.  Pig Latin has many of the usual data processing concepts that SQL has, such as filtering, selecting, grouping, and ordering, but the syntax is a little different from SQL (particularly the group by and flatten statements!).  Pig requires more verbose coding, although it’s still a fraction of what straight Java MapReduce programs require.  Pig also gives you more control and optimization over the flow of the data than Hive does.

Personally, I use both Pig Latin and Hive, although for different purposes.  I learned Pig Latin first, and have used it to construct dataflows, where I typically have a scheduled job to periodically crunch the massive data from HDFS and to transfer the summarized data into a relational database for reporting, dashboarding, and ad-hoc analyses.  I also use Hive for some simple ad-hoc analytical queries into the data in HDFS, as Hive queries are a lot faster to write for those types of queries.  However, I don’t use Hive for the automated batch jobs that move data between HDFS and other systems.  I find that I can tune the dataflow process better using Pig than with Hive.  Additionally, some of the datasets that I need in Hadoop have not yet been structured with metadata schemas for use with Hive.  In those cases, Pig is much more flexible in reading those datasets than Hive is.

Hadoop expert Alan Gates has an excellent write-up comparing the differences between Pig Latin and Hive and when to use each of them.

If you are a data engineer, then you’ll likely feel like you’ll have better control over the dataflow (ETL) processes when you use Pig Latin, especially if you come from a procedural language background.  If you are a data analyst, however, you will likely find that you can ramp up on Hadoop faster by using Hive, especially if your previous experience was more with SQL than with a procedural programming language.  If you really want to become a Hadoop expert, then you should learn both Pig Latin and Hive for the ultimate flexibility.