hive - 如何列出配置单元表中所有分区的 HDFS 位置？

Question

使用命令:

describe formatted my_table partition my_partition

我们能够列出元数据，包括 my_table 中分区 my_partition 的 hdfs 位置。但是我们如何获得包含 2 列的输出:

Partition | Location

哪个会列出 my_table 中的所有分区及其 hdfs 位置？

Answer 1

查询元存储。

演示

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')
;
        

元存储 (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 |
+----------------------+----------------------------------------------------------------------------------+