Built with Tutify.io

    AIStudyLabs

    Hive Show All Table Partitions

    Hive Show All Table Partitions

    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.

    <a name="h-hive-show-partitions-command"></a>

    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];

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

    <a name="h-show-partitions-example"></a>

    Show Partitions Example

    I've a table zipcodes with column names RecordNumber, City, Zipcodeand 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

    <a name="h-list-all-hive-partitions-from-hdfs"></a>

    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

    <a name="h-how-to-know-specific-partition-location-on-hdfs"></a>

    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

    <a name="h-show-partitions-optional-clauses"></a>

    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.

    <a name="h-limit-clause"></a>

    limit clause

    Use limitclause 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;

    <a name="h-where-clause"></a>

    where clause

    Use whereclause 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;

    <a name="h-order-by-clause"></a>

    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;

    <a name="h-related-article"></a>

    Related Article

    <a name="h-reference"></a>

    Reference

    Happy Learning !!