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.
- How to start HiveServer2 and using Beeline
- Difference between Internal Managed Table and External Table
- How to Update and Drop Table Partitions
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_nameis 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.whereoptional clause is used to filter the partitions.order byoptional 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.
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.
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.
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;
Related Article
- Hive Load Partitioned Table with Examples
- Hive Create Partition Table Explained
- Hive Load CSV File into Table
- Why Hive Table is loading with NULL values?
- Export Hive Table into CSV File with Header?
- Hive – Difference Between Internal Tables vs External Tables?
- Hive Temporary Table Usage And How to Create?
- Hive Create Table Syntax & Usage with Examples
Reference
Happy Learning !!
