Hive – How to Show All Partitions of a Table?

In Hive, SHOW PARTITIONS command is used to show or list all partitions of a table from Hive Metastore, In this article, I will explain how to list all partitions, filter partitions, and finally will see the actual HDFS location of a partition.

Hive SHOW PARTITIONS Command

Hive SHOW PARTITIONS list all the partitions of a table in alphabetical order.

Hive keeps adding new clauses to the SHOW PARTITIONS, based on the version you are using the syntax slightly changes.

Syntax:


SHOW PARTITIONS [db_name.]table_name 
[PARTITION(partition_spec)] 
[WHERE where_condition] 
[ORDER BY col_list] [LIMIT rows];
  • db_name is an optional clause. This is used to specify the database name where the table exists.
  • PARTITION(partition_spec)] is also an optional clause. This is used to list a specific partition of a table.
  • where optional clause is used to filter the partitions.
  • order by optional clause is used to specify the ascending or descending of the partitions.

H<strong>ive 4.0</strong> added where, order by and limit optional clauses

Show Partitions Example

I’ve a table zipcodes with column names RecordNumber, City, Zipcode and State. I’ve used partition key as sate and loaded some data into the table.

Now let’s run show partitions and see what it get’s us.


jdbc:hive2://127.0.0.1:10000>SHOW PARTITIONS zipcodes

Yields below output.

hive show partitions
Hive show partitions

Any command you run on Beeline or Hive CLI, it returns limited results, If you have more partitions and if you wanted to get all partitions of the table, use the below commands.


bin/beeline -u jdbc:hive2://127.0.0.1:10000 scott tiger -e 'show partitions zipcodes;' > partitions.txt
#Use below, If you are using HiverServer1 and using Hive CLI
hive -e 'show partitions zipcodes;' > partitions.txt

List All Hive Partitions from HDFS

You can run the HDFS list command to show all partition folders of a table from the Hive data warehouse location. This option is only helpful if you have all your partitions of the table are at the same location.


hdfs dfs -ls /user/hive/warehouse/zipcodes
(or)
hadoop fs -ls /user/hive/warehouse/zipcodes 

These yields similar to the below output.

hive show partitions HDFS

How to Know Specific Partition Location on HDFS?

You can also get the HDFS location of each partition by running any of the following Hive commands.


DESCRIBE FORMATTED zipcodes PARTITION(state='PR');
SHOW TABLE EXTENDED LIKE zipcodes PARTITION(state='PR');

Running SHOW TABLE EXTENDED on table and partition results in the below output. location attribute shows the location of the partition file on HDFS.

hive show partition location

Show Partitions Optional Clauses

In order to explain the optional clauses, I will use different examples with date type as a partition key. let’s call our table name LOG_TABLE with the partition on LOG_DATE column.

limit clause

Use limit clause with show partitions command to limit the number of partitions you need to fetch.


SHOW PARTITIONS LOG_TABLE LIMIT 10;
SHOW PARTITIONS LOG_TABLE PARTITION(LOG_DATE='2009-04-02') LIMIT 5;

where clause

Use where clause to fetch specific partition information from the Hive table.


SHOW PARTITIONS LOG_TABLE PARTITION(LOG_DATE='2008-06-03') WHERE hr >= 5 DESC 
LIMIT 5;

order by clause

Using order by you can display the Hive partitions in asc or desc order. Like any other SQL, the default ordering is asc.


SHOW PARTITIONS LOG_TABLE PARTITION(LOG_DATE='2007-04-02') ORDER BY hr DESC LIMIT 5;
SHOW PARTITIONS LOG_TABLE WHERE hr >= 10 AND LOG_DATE='2010-03-03' ORDER BY hr DESC LIMIT 5;

Reference

Happy Learning !!

Leave a Reply

Hive – How to Show All Partitions of a Table?