Hive元数据说明与应用

一、Hive数据存储

1、底层数据

• 底层数据存储在分布式存储（HDFS）中。

2、元数据

• 元数据信息维护在MetaStore。
• Hive默认使用的元数据存储数据库是Derby，Derby不支持多个客户端同时访问，所以需要对Derby进行替换，一般常用MySQL替换Derby。

二、Hive相关元数据表

1、Hive版本表

(1) version表

2、数据库表

(1) DBS表

3、表和视图表

(1) TBLS表

(2) TTABLE_PARAMS表

4、文件存储表

(1) SDS表

(2) SERDES表

(3) SERDE_PARAMS表

5、字段表

(1) COLUMNS_V2表

6、分区表

(1) PARTITIONS表

(2) PARTITION_KEYS表

(3) PARTITION_KEY_VALS表

(4) PARTITION_PARAMS表

三、应用

1、根据传入的tablename删除元数据所有信息

• (1) 存储过程：

• 存储过程名字为t1，输入为tbl_id

•   DELIMITER // 
    drop procedure if exists t1;
    create  procedure t1 ( in tbinput int) 
    begin
        declare v_sd_id int ;
        declare v_part_id int ;
        declare v_cd_id int ;
        declare v_serde_id int ;
  
        select tbinput;
        select SD_ID into v_sd_id from tbls where TBL_ID = tbinput;
        select part_id  into v_part_id from partitions where tbl_id = tbinput;
        select cd_id , serde_id into v_cd_id,v_serde_id from sds where sd_id = v_sd_id;
        select v_sd_id,v_part_id,v_cd_id,v_serde_id;
  
        if v_part_id is not  null then 
            delete from partition_params where part_id = v_part_id;
            delete from partition_key_vals where part_id = v_part_id;
        end if;
  
        delete from serdes where serde_id = v_serde_id;
        delete from serde_params where serde_id = v_serde_id;
        delete from columns_v2 where cd_id = v_cd_id;
        delete from sds where sd_id = v_sd_id;
        delete from partitions where tbl_id = tbinput;
        delete from partition_keys where tbl_id = tbinput;
        delete from table_params where tbl_id = tbinput;
        delete from tbls where tbl_id = tbinput;
    end ;
    //
    delimiter ; 
  
    mysql> call t1(tbl_id);
  复制代码

• (2) shell封装：

•   hive_mysql_delete.sh 
    #!/bin/bash
    source /usr/local/mysql/.bash_profile
    mysql -uroot -p密码 hive数据库 -e "
    SET FOREIGN_KEY_CHECKS = 0; 
    call t1(tbl_id);  
    SET FOREIGN_KEY_CHECKS = 1;
    quit" >> /tmp/mysql_delete.log
  复制代码