https://community.cloudera.com/t5/Support-Questions/How-to-ask-hive-query-to-fetch-data-for-specific-partition/m-p/115207#M78001 <P> <A rel="user" href="https://community.cloudera.com/users/10447/manoj-dhake.html" nodeid="10447">@Manoj Dhake</A></P><H3>Partition Based Queries</H3><P>In general, a SELECT query scans the entire table (other than for <A href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Sampling">sampling</A>). If a table created using the <A href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTable">PARTITIONED BY</A> clause, a query can do <STRONG>partition pruning</STRONG> and scan only a fraction of the table relevant to the partitions specified by the query. Hive currently does partition pruning if the partition predicates are specified in the WHERE clause or the ON clause in a JOIN. For example, if table page_views is partitioned on column date, the following query retrieves rows for just days between 2008-03-01 and 2008-03-31.</P><DIV><DIV><TABLE><TBODY><TR><TD><DIV><DIV><CODE>SELECT page_views.*</CODE> <CODE>FROM page_views</CODE> <CODE>WHERE page_views.date &gt;= </CODE><CODE>'2008-03-01'</CODE> <CODE>AND page_views.date &lt;= </CODE><CODE>'2008-03-31'</CODE> </DIV></DIV></TD></TR></TBODY></TABLE></DIV></DIV><P>If a table page_views is joined with another table dim_users, you can specify a range of partitions in the ON clause as follows:</P><DIV><DIV><TABLE><TBODY><TR><TD><DIV><DIV><CODE>SELECT page_views.*</CODE> <CODE>FROM page_views JOIN dim_users</CODE> <CODE>ON (page_views.user_id = dim_users.id AND page_views.date &gt;= </CODE><CODE>'2008-03-01'</CODE> <CODE>AND page_views.date &lt;= </CODE><CODE>'2008-03-31'</CODE><CODE>)</CODE> </DIV></DIV></TD></TR></TBODY></TABLE></DIV></DIV><UL> <LI>See also <A href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+GroupBy">Group By</A>.</LI><LI>See also <A href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy">Sort By / Cluster By / Distribute By / Order By</A>.</LI></UL> Thu, 02 Jun 2016 00:26:58 GMT sunile_manjee 2016-06-02T00:26:58Z