Hive的元数据查询(实战篇)

根据描述查找相关表

create table data_map_table_info(
                                    id int primary key auto_increment comment '主键',
                                    table_name varchar(100) comment '表名',
                                    alltext text comment '所有字段信息'
);
# 得到所有字段的模糊搜索
insert into  data_map_table_info(table_name, alltext)
select *
from (select hive.TBLS.TBL_NAME, group_concat(COMMENT, ',') alltext
      from hive.TBLS
               left join hive.SDS on hive.TBLS.SD_ID = hive.SDS.SD_ID
               left join hive.COLUMNS_V2 on hive.SDS.CD_ID = hive.COLUMNS_V2.CD_ID
               join hive.VERSION
      group by hive.TBLS.TBL_NAME) t1
where alltext like '%描述%';

得到表的所有字段

create table data_map_table_field as
select TBLS.`TBL_ID`,
       TBLS.`CREATE_TIME`,
       TBLS.`DB_ID`,
       TBLS.`LAST_ACCESS_TIME`,
       TBLS.`OWNER`,
       TBLS.`RETENTION`,
       TBLS.`SD_ID`,
       TBLS.`TBL_NAME`,
       TBLS.`TBL_TYPE`,
       TBLS.`VIEW_EXPANDED_TEXT`,
       TBLS.`VIEW_ORIGINAL_TEXT`,
       TBLS.`IS_REWRITE_ENABLED`,
       SDS.`CD_ID`,
       SDS.`INPUT_FORMAT`,
       SDS.`IS_COMPRESSED`,
       SDS.`IS_STOREDASSUBDIRECTORIES`,
       SDS.`LOCATION`,
       SDS.`NUM_BUCKETS`,
       SDS.`OUTPUT_FORMAT`,
       SDS.`SERDE_ID`,
       COLUMNS_V2.`COMMENT`,
       COLUMNS_V2.`COLUMN_NAME`,
       COLUMNS_V2.`TYPE_NAME`,
       COLUMNS_V2.`INTEGER_IDX`
from hive.TBLS
         left join hive.SDS on hive.TBLS.SD_ID = hive.SDS.SD_ID
         left join hive.COLUMNS_V2 on hive.SDS.CD_ID = hive.COLUMNS_V2.CD_ID
         join hive.VERSION
where hive.TBLS.TBL_NAME = 'tablename';

查找表的所有分区

create table data_map_table_partitions as
select TBLS.`TBL_ID`,
       TBLS.`CREATE_TIME`,
       TBLS.`DB_ID`,
       TBLS.`OWNER`,
       TBLS.`RETENTION`,
       TBLS.`SD_ID`,
       TBLS.`TBL_NAME`,
       TBLS.`TBL_TYPE`,
       TBLS.`VIEW_EXPANDED_TEXT`,
       TBLS.`VIEW_ORIGINAL_TEXT`,
       TBLS.`IS_REWRITE_ENABLED`,
       PARTITIONS.`PART_ID`,
       PARTITIONS.`LAST_ACCESS_TIME`,
       PARTITIONS.`PART_NAME`,
       cast(SUBSTRING(PART_NAME, 4, 10) AS date) datestr
from hive.TBLS
         left join hive.PARTITIONS on hive.TBLS.TBL_ID = hive.PARTITIONS.TBL_ID
where hive.TBLS.TBL_NAME = 'tablename'
order by cast(SUBSTRING(PART_NAME, 4, 10) AS date) desc;