Using GROUP BYs or multiple INSERTs with complex data types in Hive.

In any sort of ad hoc data analysis, the first step is often to extract a specific subset of log lines from our files.  For example, when looking at a single partner’s web traffic, I often use an initial query to copy that partner’s data into a new table.  In addition to segregating out only the data relevant to my analysis, I use this to copy the data from S3 into HDFS, which will make later queries more efficient.  (Using maps as our log lines is how we support dynamic columns.)

create external table if not exists
original_logs(fields map<string,string>) location “...” ;

create table if not exists
extracted_logs(fields map<string,string>) ;

insert overwrite table extracted_logs
select * from original_logs where fields[“partnerId”] = 123 ;

If I’m doing this for multiple partners, it’s tempting to use a multiple-insert so Hadoop only needs to make one pass of the original data.

create external table if not exists
original_logs(fields map<string,string>) location “...” ;

create table if not exists
extracted_logs(fields map<string,string>)
partitioned by (partnerId int);

from original_logs
insert overwrite table extracted_logs partition (partnerId = 123)
select * from original_logs where fields[“partnerId”] = 123
insert overwrite table extracted_logs partition (partnerId = 234)
select * from original_logs where fields[“partnerId”] = 234

Unfortunately, in Hive 0.7.x, this query fails with the error message “Hash code on complex types not supported yet.”  A multiple-insert statement uses an implicit group by, and Hive 0.7.x does not support grouping by complex types.  This bug was partially addressed in 0.8, which added support for arrays and maps, but structs and unions are still not supported.

At an initial glance, it does look like adding this support should be straightforward.  This could be a good candidate for our next open source day.

mdadm: device or resource busy

I just spent a few hours tracking an issue with mdadm (Linux utility used to manage software RAID devices) and figured I'd write a quick blog post to share the solution so others don't have to waste time on the same.

As a short background, we use mdadm to create RAID-0 stripped devices for our Sugarcube analytics (OLAP) servers using Amazon EBS volumes.

The issue manifested itself as a random failure during device creation:


$ mdadm --create /dev/md0 --level=0 --chunk 256 --raid-devices=4 /dev/xvdh1 /dev/xvdh2 /dev/xvdh3 /dev/xvdh4
mdadm: Defaulting to version 1.2 metadata
mdadm: ADD_NEW_DISK for /dev/xvdh3 failed: Device or resource busy

I searched and searched the interwebs and tried every trick I found to no avail. We don't have dmraid installed on our Linux images (Ubuntu 12.04 LTS / Alestic cloud image) so there's no possible conflict there.  All devices were clean, as they are freshly created EBS volumes and I knew none of them were in use.  

Before running mdadm --create, mdstat was clean:

$ cat /proc/mdstat

Personalities : [linear] [multipath] [raid0] [raid1] [raid6] [raid5] [raid4] [raid10]

unused devices: <none>

And yet after running it the devices were assigned to two different devices instead of just /dev/md0:

$ cat /proc/mdstat

Personalities : [linear] [multipath] [raid0] [raid1] [raid6] [raid5] [raid4] [raid10]

md127 : inactive xvdh4[3](S) xvdh3[2](S)

      1048573952 blocks super 1.2

md0 : inactive xvdh2[1](S) xvdh1[0](S)

      1048573952 blocks super 1.2

unused devices: <none>

Looking into dmesg didn't reveal anything interesting either:

$ dmesg 

...

[3963010.552493] md: bind<xvdh1>

[3963010.553011] md: bind<xvdh2>

[3963010.553040] md: could not open unknown-block(202,115).

[3963010.553052] md: md_import_device returned -16

[3963010.566543] md: bind<xvdh3>

[3963010.731009] md: bind<xvdh4>

And strangely, the creation or assembly would sometime work and sometime not:

$ mdadm --manage /dev/md0 --stop

mdadm: stopped /dev/md0

$ sudo mdadm --assemble --force /dev/md0 /dev/xvdh[1234]

mdadm: /dev/md0 has been started with 4 drives.

$ mdadm --manage /dev/md0 --stop

mdadm: stopped /dev/md0

$ sudo mdadm --assemble --force /dev/md0 /dev/xvdh[1234]

mdadm: cannot open device /dev/xvdh3: Device or resource busy

$ mdadm --manage /dev/md0 --stop

mdadm: stopped /dev/md0

$ sudo mdadm --assemble --force /dev/md0 /dev/xvdh[1234]

mdadm: cannot open device /dev/xvdh1: Device or resource busy

mdadm: /dev/xvdh1 has no superblock - assembly aborted

$ mdadm --manage /dev/md0 --stop

mdadm: stopped /dev/md0

$ sudo mdadm --assemble --force /dev/md0 /dev/xvdh[1234]

mdadm: /dev/md0 has been started with 4 drives.

I started suspecting I was facing some kind of underlying race condition where the devices would get assigned/locked during the device creation process.   So I started googling for "mdadm create race" and  I finally found a post that tipped me off. While it didn't provide the solution, the post put me on the right track by mentioning udev and it took only a few more minutes to narrow down on the solution:  disabling udev events during device creation to avoid contention on device handles.

So now our script goes something like:

$ udevadm control --stop-exec-queue

$ mdadm --create /dev/md0 --run --level=0 --raid-devices=4 ...

$ udevadm control --start-exec-queue

And we now have consistent reliable device creation.

Hopefully this blog post will help other passers-by with a similar problem.  Good luck!

Amazon Web Services Outages: 4 Steps for Survival

(Cross-post from the Bizo Blog)

Another Amazon Web Services (AWS) cloud outage over the past weekend took down some pretty major services such as Netflix, Heroku, Pinterest and Instagram.  At Bizo, a company that provides business marketing services for hundreds of F1000 clients, we serve billions of requests a day across tens of thousands of websites, and have our entire infrastructure on the AWS cloud, but didn’t have any downtime.  The simple reason is that we take our customers’ uptime and site performance seriously, and have built tools and services on AWS to ensure high-availability (HA) and low-latency (LL) services.  Despite the FUD created by many of the industry blogs and press, it is possible to create HA and LL services on AWS if you follow some simple steps.

Amazon Web Services Outages: 4 Steps for Survival

AWS Billing Info in Hive

Amazon recently (finally!) launched programmatic access to your AWS billing data.

Once you turn it on, select a bucket, grant access to the AWS system user, you'll get a .csv file with your estimated billing for the month. The files are delivered daily, but they contain month-to-date information, and will replace the file from the previous day.

It's easy enough to view this information in excel (or similar), but I thought it would be fun to take a look in hive, especially once we start having data for a few months to aggregate over.

Amazon delivers the data to the root of your bucket. I decided to start moving it to a hive-partitioned path, to make it easier to query once we start have more data. I wrote a simple scala script to move the data to [bucket]/partioned/year=[year]/month=[month]/[file]. Here's some example code.

Ok, now we're ready to read the data in Hive.

Here's a hive schema for the AWS billing information. It uses the csv-serde (make sure you add that jar before running the create table statement). Run alter table aws_billing recover partitions; to load in the partitions (one per year/month), and you're ready to query.

Like I said, it's overkill to use hive to read this data for a month or so, but it's just so addictive having a SQL interface to arbitrary S3 data :).

Here are some example queries to get you started.

Costs by Service

select ProductCode, UsageType, Operation, sum(TotalCost)
  from aws_billing
 where RecordType in ("PayerLineItem", "LinkedLineItem")
 group
    by ProductCode, UsageType, Operation
;
  

EC2 usage, by size (across EC2/EMR)

select ProductCode, UsageType,sum(TotalCost)
  from aws_billing
 where RecordType in ("PayerLineItem", "LinkedLineItem")
   and UsageType like "BoxUsage%"
 group
    by ProductCode, UsageType
;
  

the golden rule of programming style

There's an interesting page on the subject of compilation units per file over at the scala style guide.

The guideline, is, delightfully vague, which I will paraphrase as: Mostly use single files, unless you can't, or unless it's better if you don't.

The author(s) go on to expand on the reasoning behind breaking the guideline:

Another case is when multiple classes logically form a single, cohesive group, sharing concepts to the point where maintenance is greatly served by containing them within a single file. These situations are harder to predict… Generally speaking, if it is easier to perform long-term maintenance and development on several units in a single file rather than spread across multiple, then such an organizational strategy should be preferred for these classes.

This touches on what I consider to be the golden rule of programming style: Make your intent clear and the code easy to read.

Software spends most of its life in maintenance, which is why we have style guides and coding standards. It's valuable to have consistent looking code to promote a shared vocabulary, improve readability, and steer away from confusing or error-prone constructs.

It is just as important to be able to understand, both as an author and as a reviewer, that in certain cases following the letter of the law goes against the main goal of improving readability and maintenance. A one-size-fits-all rule does not always work, and as the authors of this particular guideline mention, "these situations are harder to predict."

Make your intent clear and the code easy to read.

Scala Test Plug-in for Sublime Text 2

I have documented and put some polish on the Sublime Text 2 plug-in I blogged about previously.  It lets you run a single Scala Test, or all tests in your project.  It also lets you quickly navigate to any scala files in your project folder, and switch back and forth between a class and its test.  Check it out here: https://github.com/patgannon/sublimetext-scalatest

Dev Days: Hacking, Open Source and Docs

Dev Days

Every month we have a "Dev Day" where engineers take a break from their projects and work on "other stuff".  Most start-up engineering teams have a "Hack Day" where everyone gets to hack on anything they want as long as they ship and share it with the rest of the team.  Of course we have Hack Days but we also have other types of Dev Days too.  In fact, we have three types of Dev Days:

• Hack Days 
• Open Source Days 
• Doc Days

Open Source Days

You know what Hack Days are so I'll move on quickly to Open Source Days.  Just like most companies these days, Bizo uses a lot of open source software (OSS).  We love OSS and the community of developers and companies that share it.  Over the last few years, we've used plenty of OSS but we've also created and given back lots of code as well.

Actually today is one of our Open Source Days so all the engineers are working on both new and old open source projects.  You can check out our (growing) list of projects by visiting code.bizo.com.  Over the years, we've created a lot of tools around AWS including s3cp, fakesdb, aws-tools (package of all CLI tools).  We've also built a lot of stuff for Hadoop (Hive, etc) including csv-serde, gdata-storagehandler and our latest is a scala query language called revolute (still in development).  In addition, we've have a wide variety of other awesome code including the awesome Joist, dependence.js, raphy-charts and other fun stuff!

Doc Days

The third type of Dev Day we have is called Doc Days.  I know that you are thinking but Doc Days are extremely valuable days for engineering and everyone else for that matter.  On Doc Day the entire engineering team works on wiki pages, code documentation, design docs, architecture docs and even blog posts.  It really is better than it sounds!  

If you've read my post on "building a kick ass engineering team", you know that one of the keys is the 3Cs...  Communication, Communication, Communication!  (My high school baseball coach taught me that one.)  As a engineering team, we believe that communication is one of the best things we can do for each other.  As any company grows communication becomes a larger and larger part of day to day and we see Doc Days as a way to ensure that we are communicating as clearly and accurately as we can.

Conclusion

These Dev Days have been a huge success for Bizo engineering.  We've even inspired other departments to have similar days (Marketing in particularly like these documentation days!).    We challenge you to go beyond the "Hack Day" and start thinking about other Dev Days that your engineering organization can benefit from.