如何列出配置单元表中所有分区的HDFS位置？ [英] How to list HDFS location of all the partitions in a hive table?

问题描述

使用以下命令：

 描述格式化的my_table分区my_partition 
  

我们可以在 my_table中列出元数据，包括分区 my_partition 的hdfs位置。但是我们怎样才能得到一个有两列的输出：

 分区|位置
  

它将列出 my_table 和他们的hdfs位置？

解决方案

查询Metastore。 Demo



Hive

  create table mytable（i int）通过（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）



 从metastore.DBS中选择p.part_name 
，s.location 
 
作为d 
 
加入Metastore .TBLS as t 
 
 on t.db_id = 
 d.db_id 
 
将metastore.PARTITIONS作为p 
 
加入p.tbl_id = 
 t.tbl_id 
 
将metastore.SDS作为s 
 
加入s.sd_id = 
 p.sd_id 
 
 where d.name ='default'
和t.tbl_name ='mytable'
; 
  





---

  + ---------------------- + --------------------------- -------------------------------------------------- ----- + 
 | part_name |位置| 
 + ---------------------- + ---------------------- -------------------------------------------------- ---------- + 
 | 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 | 
 + ---------------------- + ---------------------- -------------------------------------------------- ---------- + 
  

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?

解决方案

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 |
+----------------------+----------------------------------------------------------------------------------+

这篇关于如何列出配置单元表中所有分区的HDFS位置？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！