Hive: how to show all partitions of a table?
I have a table with 1000+ partitions.
"Show partitions" command only lists a small number of partitions.
How can i show all partitions?
Update:
I found "
show partitions" command only lists exactly 500 partitions."
select ... where ..." only processes the 500 partitions!
CLI has some limit when ouput is displayed. I suggest to export output into local file:
$hive -e 'show partitions table;' > partitions
hive> show partitions table_name;
Okay, I'm writing this answer by extending wmky's answer above & also, assuming that you've configured mysql for your metastore instead of derby.
select PART_NAME FROM PARTITIONS WHERE TBL_ID=(SELECT TBL_ID FROM TBLS WHERE TBL_NAME='<table_name>');
The above query gives you all possible values of the partition columns.
Example:
hive> desc clicks_fact;
OK
time timestamp
..
day date
file_date varchar(8)
# Partition Information
# col_name data_type comment
day date
file_date varchar(8)
Time taken: 1.075 seconds, Fetched: 28 row(s)
I'm going to fetch the values of partition columns.
mysql> select PART_NAME FROM PARTITIONS WHERE TBL_ID=(SELECT TBL_ID FROM TBLS WHERE TBL_NAME='clicks_fact');
+-----------------------------------+
| PART_NAME |
+-----------------------------------+
| day=2016-08-16/file_date=20160816 |
| day=2016-08-17/file_date=20160816 |
....
....
| day=2017-09-09/file_date=20170909 |
| day=2017-09-08/file_date=20170909 |
| day=2017-09-09/file_date=20170910 |
| day=2017-09-10/file_date=20170910 |
+-----------------------------------+
1216 rows in set (0.00 sec)
Returns all partition columns.
Note: JOIN table DBS ON DB_ID when there is a DB involved (i.e, when, multiple DB's have same table_name)
You can see Hive MetaStore tables,Partitions information in table of "PARTITIONS". You could use "TBLS" join "Partition" to query special table partitions.