HIVE SHOW PARTITIONS

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  🙂