从mysql层面删除hive元数据

痛定思痛。 2022-06-06 10:12 226阅读 0赞

由于之前配置了hive，hdfs 被格式化了，导致现在hive上原来的元数据没有清理掉。   
由于hive 上所有元数据均保存在mysql中，所以，可以从mysql上删除表相关信息即可删除hive表，而且不会影响hdfs上数据。   
分析：

hive在mysql上的相关元数据表关系图：

![Center][]

解决方法：   
1、先在 mysql 中建存储过程   
说明：   
建的存储过程名字为t1，输入为tbl\_id。因为表名可能重复，但是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 ; 
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
       15
       16
       17
       18
       19
       20
       21
       22
       23
       24
       25
       26
       27
       28
       29
       30
       31
       32

2、编写shell(最好用mysql的用户去执行shell)：

hadoop002:mysqladmin:/usr/local/mysql:>cat mysql_delete.sh 
    #!/bin/bash
    source /usr/local/mysql/.bash_profile
    mysql -uroot -ppassword hive3 -e " SET FOREIGN_KEY_CHECKS = 0; call t1(71); SET FOREIGN_KEY_CHECKS = 1; quit" >> /tmp/mysql_delete.log
     
      
       1
       2
       3
       4
       5
       6
       7
       8

完成！

[Center]: /images/20220606/23976496752747bcbc700ad59468f4a1.png