How to list HDFS location of all the partitions in

Question

Using the command:

describe formatted my_table partition my_partition

we are able to list the metadata including hdfs location of the partition my_partition in my_table. But how can we get an output with 2 columns:

Partition | Location

which would list all the partitions in my_table and their hdfs locations?

Answer 1

Query the metastore.

Demo

Hive

create table mytable (i int) partitioned by (dt date,type varchar(10))
;

alter table mytable add 
    partition (dt=date '2017-06-10',type='A')
    partition (dt=date '2017-06-11',type='A')
    partition (dt=date '2017-06-12',type='A')
    partition (dt=date '2017-06-10',type='B')
    partition (dt=date '2017-06-11',type='B')
    partition (dt=date '2017-06-12',type='B')
;

Metastore (MySQL)

select  p.part_name
       ,s.location

from            metastore.DBS           as d

        join    metastore.TBLS          as t

        on      t.db_id         =
                d.db_id

        join    metastore.PARTITIONS    as p

        on      p.tbl_id        =
                t.tbl_id

        join    metastore.SDS           as s

        on      s.sd_id         =
                p.sd_id

where   d.name     = 'default'
    and t.tbl_name = 'mytable'
;

---

+----------------------+----------------------------------------------------------------------------------+
|      part_name       |                                     location                                     |
+----------------------+----------------------------------------------------------------------------------+
| dt=2017-06-10/type=A | hdfs://quickstart.cloudera:8020/user/hive/warehouse/mytable/dt=2017-06-10/type=A |
| dt=2017-06-11/type=A | hdfs://quickstart.cloudera:8020/user/hive/warehouse/mytable/dt=2017-06-11/type=A |
| dt=2017-06-12/type=A | hdfs://quickstart.cloudera:8020/user/hive/warehouse/mytable/dt=2017-06-12/type=A |
| dt=2017-06-10/type=B | hdfs://quickstart.cloudera:8020/user/hive/warehouse/mytable/dt=2017-06-10/type=B |
| dt=2017-06-11/type=B | hdfs://quickstart.cloudera:8020/user/hive/warehouse/mytable/dt=2017-06-11/type=B |
| dt=2017-06-12/type=B | hdfs://quickstart.cloudera:8020/user/hive/warehouse/mytable/dt=2017-06-12/type=B |
+----------------------+----------------------------------------------------------------------------------+