从mysql层面删除hive元数据

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

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

解决方法： 
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/bashsource /usr/local/mysql/.bash_profilemysql -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

完成！