解决方法: 1、先在 mysql 中建存储过程 说明: 建的存储过程名字为t1,输入为tbl_id。因为表名可能重复,但是tbl_id不会。
DELIMITER //
dropprocedureifexists t1;createprocedure t1 ( in tbinput int)
begindeclare 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
deletefrom partition_params where part_id = v_part_id;deletefrom partition_key_vals where part_id = v_part_id;endif;deletefrom serdes where serde_id = v_serde_id;deletefrom serde_params where serde_id = v_serde_id;deletefrom columns_v2 where cd_id = v_cd_id;deletefrom sds where sd_id = v_sd_id;deletefrom partitions where tbl_id = tbinput;deletefrom partition_keys where tbl_id = tbinput;deletefrom table_params where tbl_id = tbinput;deletefrom 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_profile
mysql -uroot -ppassword hive3 -e"
SET FOREIGN_KEY_CHECKS = 0;
call t1(71);
SET FOREIGN_KEY_CHECKS = 1;
quit" >> /tmp/mysql_delete.log