Hive partition table query optimisation

Question

I am new to hive,and hadoop ecosystem in general.From what I learnt of the basics of Hive you can create partitions on hive table based on certain attributes.And if a query has any mention of that attribute then it should supposedly get a performance boost as hive only scans that particular partition file instead of scanning the whole table.My question is suppose we have some hierarchical structure in the data.Say I partition a table based on unique state values and every time a query is based on state hive would only scan that particular state partition instead of scanning the whole table.However say every state also has unique district names.If I make a query based only on district values would hive scan the whole table?

If so then is there some way to change the query in such a way that I can manually instruct hive to query the particular state file to which the district belongs to.And then perform other operations only on that partition file,instead of scanning the whole table for matching district values.

Answer 1

One of the strengths of Hive is that it has strong support for partitioning. However, it cannot read your mind when you write queries.

If you have a partition on state, then you need state in the where clause for partition pruning. So, if you query only on district, the whole table would be scanned.

If you have a partition on district, then you need the district. A query on state would scan the whole table.

If you have a partition on both . . . well, then it is a little more complicated to declare, but your queries would read a minority of partitions with either state or district.

If you are just learning about partitions, I would advise you to start with date partitions. These are the most common and a good way to get familiar with the concept.