Hive 元数据信息查询

查询指定仓库的表及表备注信息

select 
    t.TBL_NAME,tp.param_value
from TBLS T left join 
TABLE_PARAMS tp 
on t.tbl_id=tp.tbl_id 
where ( t.TBL_NAME like 'ods%'
or t.TBL_NAME like 'dw%'
or t.TBL_NAME like 'dim%')
and tp.param_key='comment'
and T.db_id in (86,79,46)

查询指定库中表的列、备注等信息

select 
    *
from 
(
    select 
         t.db_id
        ,t.tbl_id 
        ,c2.INTEGER_IDX
        ,t.TBL_NAME
        ,c2.COLUMN_NAME
        ,c2.TYPE_NAME
        ,c2.COMMENT as col_COMMENT
    from TBLS T left join SDS s 
    on t.sd_id=s.sd_id
    left join CDS c 
    on s.cd_id=c.cd_id
    left join COLUMNS_V2 c2
    on c.cd_id=c2.cd_id 
    where ( t.TBL_NAME like 'ods%'
    or t.TBL_NAME like 'dw%'
    or t.TBL_NAME like 'dim%')
    and T.db_id in (86,79,46)
union all 
    select 
        t1.db_id,
        t1.tbl_id,
        (pk.INTEGER_IDX+t1.INTEGER_IDX) as INTEGER_IDX,
        t1.tbl_name,
        pk.PKEY_NAME COLUMN_NAME,
        pk.PKEY_TYPE TYPE_NAME,
        pk.PKEY_COMMENT as col_COMMENT
    from 
    (
        select 
             max(c2.INTEGER_IDX)+1 as INTEGER_IDX
            ,t.TBL_id
            ,t.tbl_name 
            ,t.db_id
        from TBLS T left join SDS s 
        on t.sd_id=s.sd_id
        left join CDS c 
        on s.cd_id=c.cd_id
        left join COLUMNS_V2 c2
        on c.cd_id=c2.cd_id 
        where ( t.TBL_NAME like 'ods%'
        or t.TBL_NAME like 'dw%'
        or t.TBL_NAME like 'dim%')
        and T.db_id in (86,79,46)
        group by t.TBL_id
            ,t.tbl_name
    ) t1 left join 
    PARTITION_KEYS pk 
    on t1.tbl_id = pk.TBL_ID
) tmp1
order by tmp1.db_id,tmp1.tbl_id,tmp1.INTEGER_IDX

最后

以上就是还单身豌豆为你收集整理的Hive 元数据信息查询Hive 元数据信息查询的全部内容，希望文章能够帮你解决Hive 元数据信息查询Hive 元数据信息查询所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。