在hive中,如何只选择一个动态分区的值(当有一个或多个分区可用时)

xqkwcwgp  于 2021-06-26  发布在  Hive
关注(0)|答案(1)|浏览(269)

现在我有一个表,它的结构如下,

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)

现在我想得到这个表的分区。

hive> show partitions clicks_fact;
OK
day=2016-09-02/file_date=20160902
..
day=2017-06-30/file_date=20170629
Time taken: 0.144 seconds, Fetched: 27 row(s)

我可以将分区作为两者的组合 day & file_date . 现在,有没有办法只得到 file_date

thigvfpy

thigvfpy1#

hive为元数据检索提供了非常有限的选项。
直接查询元存储。

演示

Hive

create table clicks_fact (i int) partitioned by (day date,file_date int)
;

alter table clicks_fact add
    partition (day=date '2016-09-02',file_date=20160901)
    partition (day=date '2016-09-02',file_date=20160902)
    partition (day=date '2016-09-03',file_date=20160901)
    partition (day=date '2016-09-03',file_date=20160902)
    partition (day=date '2016-09-03',file_date=20160903)
;

元存储(mysql)

use metastore;

select  distinct
        pkv.PART_KEY_VAL

from            DBS                 as d

        join    TBLS                as t

        on      t.DB_ID =
                d.DB_ID

        join    PARTITION_KEYS      as pk

        on      pk.TBL_ID =
                t.TBL_ID

        join    PARTITIONS          as p

        on      p.TBL_ID =
                t.TBL_ID       

        join    PARTITION_KEY_VALS  as pkv

        on      pkv.PART_ID =
                p.PART_ID

            and pkv.INTEGER_IDX =
                pk.INTEGER_IDX       

where   d.NAME       = 'local_db'
    and t.TBL_NAME   = 'clicks_fact'
    and pk.PKEY_NAME = 'file_date'
;
+--------------+
| PART_KEY_VAL |
+--------------+
|     20160901 |
|     20160902 |
|     20160903 |
+--------------+

相关问题