转载 从mysql层面删除hive元数据

 

 

• 92

由于之前配置了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 ; 

 

 

 

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

 

 

完成！

转载于:https://www.cnblogs.com/kxgdby/p/7956570.html