On the Use of Nested Data in Hive

We recently wrapped up some efficiency improvements to Voldemort's Build and Push process. As we started rolling it out in production, I wanted to crunch some operational data to measure the resource utilization gains on the Hadoop cluster.

I asked around to determine what would be the best way to get visibility into our Build and Push jobs' metrics. Various methods were suggested, but I was eventually pointed to the most promising one of the bunch: an experimental* Hive table containing metadata about all of our Hadoop jobs.

*I was warned in advance that the table was not ready for general consumption because its schema was not yet finalized.

Hive, We Meet Again

It has been almost two years since I last used Hive, so I was enthusiastic to go at it again.

I'm glad to report that Hive works a lot more smoothly nowadays. I'm not sure how much of that improvement is because of Hive itself getting better and how much is thanks to the Hadoop team at LinkedIn running a pretty tight operation.

In any case, I clearly remember that when I was playing around with it 2-3 years ago, there were still plenty of rough edges: queries that would run forever unless they were written just right, manual steps to optimize query plans, etc. Now, everything Just Works?.

Everything? No, of course not. Nothing is ever perfect (:

Nested Data

The table I needed to deal with contained a fair amount of nested data. The structure of the table is one row per job execution, and then any metadata for which there is more than one item per job is stored in nested arrays. For example, the Map-Reduce counters are stored in an array of counter groups, each of which contains an array of counters. The counter is a struct containing name, display name and value. You can see a glimpse of one of the field's nested structure in this post's banner picture, above.

I appreciate the possibilities that nested data brings to the table (pun not intended), but unfortunately, SQL is not always well suited to deal with it. In my case, I needed to find a few specific elements of the array, extract their values, and compute some aggregations on them. Let's see how this can be achieved.

Querying Nested Arrays

At first, I found the explode(array) table-generating function, but that seemed quite limited. I needed to explode two levels of nested arrays, and that didn't seem possible (or at least not without creating intermediate tables). I also needed to get a hold of the job ID associated with my counters, in order to do some further queries, and that didn't seem possible either with the explode function.

Then I tried lateral views, and that did the trick. The end result was a bit convoluted, with my main select query joined against several lateral views of the same table. But at least it worked.

Can We Do Better?

I am not sure whether there could be a better syntax to support nested array exploration in SQL. I imagine there could be, but that is a conversation for another day. In any case, I tend to agree with Postgres' documentation:

Tip: Arrays are not sets; searching for specific array elements can be a sign of database misdesign.

In the case of my table containing metadata on job executions, I believe nested data is perfectly fine, but it would have been more appropriate to use nested maps rather than nested arrays. In this case, one of the nested fields (the counter's name) would have been a perfectly appropriate map key, since the array of counter elements would be considered bogus if it contained two counters with the same name.

Using nested maps would have allowed me to access exactly the counters I wanted with a very simple syntax, without any joining against lateral views. Indeed, the syntax for accessing specific map elements is even simpler than performing a join against a normalized set of tables!

Closing Remarks

When designing Hive table schemas, always carefully consider whether the use of a nested array is appropriate. If there is any field inside the array's elements which you expect to be unique across all elements of the array, then consider the possibility of using a map keyed on that field, instead of an array.

More generally, whenever designing any data schema, try to anticipate which query patterns you expect to see. If getting at some part of your data seems convoluted of otherwise costly, take a moment to see if you potentially have a schema design issue on your hands.

Finally, if working on large scale challenges with a data-driven approach appeals to you, feel free to reach out to me. There might be a place for you within LinkedIn's Data org!

In a future installment, I'd like to share some of the specific efficiency improvement numbers that came out of the analysis mentioned in this post. Stay tuned!

EDIT [2016-02-24] : The analysis that came out of this Hive work is now published here: Slashing Voldemort's Hadoop Resource Usage