If you want to display all the Partitions of a HIVE table you can do that using SHOW PARTITIONS command. If you want to learn more about Hive Table Partitions you can check it here. So today we are going to understand the below topics.
show partitions syntax
The syntax of show partition is pretty straight forward and it works on both internal or external Hive Tables. The output is order alphabetically by default.
SHOW PARTITIONS table_name;
Lets create a customer table with 2 partition columns ‘country’ and ‘state’ and add few partitions to it.
create table customer (cust_nm string , cust_id Int) partitioned by (country string ,state string); alter table customer add partition (Country = 'USA' ,State = 'California'); alter table customer add partition (Country = 'USA' ,State = 'Ohio'); show partitions customer; partition country=USA/state=California country=USA/state=Ohio
show partitions using where orderby & limit clause
From hive 4.0 we can use where , order by and limit clause along with show partitions in hive.Lets implement and see.
limit clause
Using limit clause you can limit the number of partitions you need to fetch. You can apply this on the entire table or on a sub partitions. Lets check both the scenario below .
SHOW PARTITIONS table_name [PARTITION(partition_spec)] [LIMIT rows];
create table customer (cust_nm string , cust_id Int) partitioned by (country string ,state string); alter table customer add partition (Country = 'USA' ,State = 'California'); alter table customer add partition (Country = 'USA' ,State = 'Ohio'); alter table customer add partition (Country = 'India' ,State = 'Karnataka'); alter table customer add partition (Country = 'India' ,State = 'Delhi'); --show only 2 partitions show partitions customer limit 2; partition country=India/state=Delhi country=India/state=Karnataka --show only 1 sub-partition for partition India show partitions customer (country = 'India') limit 1; country=India/state=Delhi
where clause
Using where clause you can fetch specific partition information from the Hive table. Again like limit this is applicable on entire table or on specific partitions.
SHOW PARTITIONS table_name [PARTITION(partition_spec)] [WHERE where_condition] ;
--check if country partition has USA show partitions customer where country ='USA'; --check if country partition for USA has Delhi as State partition show partitions customer (country = 'India') where state = 'Delhi';
order by clause
Using order by you can display the Hive partitions in asc or desc order. The default ordering is asc.
SHOW PARTITIONS table_name [PARTITION(partition_spec)] [ORDER BY col_list] ;
--check if country partition has USA and display the partitions in desc order show partitions customer where country ='USA' order by state desc;
Note: You can also use all the clauses in one query in Hive
SHOW PARTITIONS table_name [PARTITION(partition_spec)] [WHERE where_condition] [ORDER BY column_list] [LIMIT rows];
Conclusion
So today we learnt how to show partitions in Hive Table. Also the use of where limit order by clause in Partitions which is introduced from Hive 4.0.0.
🙂 kudos for learning something new 🙂