Why hpdr?

Where I work, much of our big data lives in Hive tables with separate partitions for the different time units: YYYY, MM, DD, HH, and MIN.

That is, we don't have a single YYYYMMDDHHMIN partition.

When newly-arrived developers meet our Hive data, they almost inevitably make a series of mistakes, in a particular order. They start with something like this:

	SELECT event FROM my_table
	WHERE create_date > '2016-11-15' AND create_date <= '2016-12-15'

view raw nopartitions.sql hosted with ❤ by GitHub

Since no partitions are specified, the query never finishes. It's pulling data from every partition going back several years.

Then, with partitions in hand, they try this:

	SELECT event FROM my_table
	WHERE YYYY=2016 AND ((MM=11 AND DD>=15) OR (MM=12 AND DD<15));

view raw justpartitions.sql hosted with ❤ by GitHub

But that's not right because there might be events in those partitions whose create_date is outside the date range. Like an event we don't want because it has a too-early create_date of 2016-11-14 23:59:59 but it slips into the YYYY=2016,MM=11,DD=15 partition due to time skew.

They almost get it right by using both the date range and the partition range:

	SELECT event FROM my_table
	WHERE create_date > '2016-11-15' AND create_date <= '2016-12-15'
	AND YYYY=2016 AND ((MM=11 AND DD>=15) OR (MM=12 AND DD<15)));

view raw almostthere.sql hosted with ❤ by GitHub

But that's still not right because there might be events with a create_date's that fall within the date range but are not in those partitions. Like an event with a create_date of 2016-12-14 23:59:59 that slips into the unincluded YYYY=2016,MM=12,DD=15 partition.

They need to add some extra "slop", like one hour, on each end of the partition range so that they're finding all the events within the date range.

They end up with what I believe to be the "correct" query:

	SELECT event FROM my_table
	WHERE create_date > '2016-11-15' AND create_date <= '2016-12-15'
	AND YYYY=2016 AND
	(
	(MM=11 AND DD=14 AND HH>=23)
	OR (MM=11 AND DD>14)
	OR (MM=12 AND DD<15)
	OR (MM=12 AND DD=15 AND HH<01)
	);

view raw thatstheone.sql hosted with ❤ by GitHub

But look how unwieldy that partition range is. And look what happens the next month when we cross the year boundary:

	SELECT event FROM my_table
	WHERE create_date > '2016-12-15' AND create_date <= '2017-01-15'
	AND
	( (YYYY=2016 AND MM=12 AND DD=14 AND HH>=23)
	OR (YYYY=2016 AND MM=12 AND DD>14)
	OR (YYYY=2017 AND MM=01 AND DD<15)
	OR (YYYY=2017 AND MM=01 AND DD=15 AND HH<01)
	)

view raw yearboundary.sql hosted with ❤ by GitHub

Crafting these partition date ranges over and over is not something you want to be doing by hand. This is why hpdr.