通过MySQL中元数据信息删除Hive中的表

一、确定Hive中需删除的表名（order_mulit_partition 分区表）

hive (default)> desc formatted order_mulit_partition;
OK
col_name        data_type       comment
# col_name              data_type               comment             

order_number            string                                      
event_time              string                                      

# Partition Information          
# col_name              data_type               comment             

event_month             string                                      
step                    string                                      

# Detailed Table Information             
Database:               default                  
Owner:                  root                     
CreateTime:             Sun Oct 08 19:12:09 CST 2017     
LastAccessTime:         UNKNOWN                  
Protect Mode:           None                     
Retention:              0                        
Location:               hdfs://hadoop001:8020/user/hive/warehouse/order_mulit_partition  
Table Type:             MANAGED_TABLE            
Table Parameters:                
        transient_lastDdlTime   1507461129          

# Storage Information            
SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe       
InputFormat:            org.apache.hadoop.mapred.TextInputFormat         
OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat       
Compressed:             No                       
Num Buckets:            -1                       
Bucket Columns:         []                       
Sort Columns:           []                       
Storage Desc Params:             
        field.delim             \t                  
        serialization.format    \t                  

二、删除
1、从元数据删除Hive的表需要得到五个ID.TBL_ID,SD_ID ,CD_ID,PART_ID,SERDE_ID.

2、TBL_ID和SD_ID可以从TBL_ID表得到，CD_ID,SERDE_ID可以从SDS表得到，PART_ID可以从PARTITIONS表中得到。

order_mulit_partition表的五个ID分别为

TBL_ID =21 ， SD_ID=26 ，CD_ID=21，PART_ID=6 ，SERDE_ID=26

3、删除脚本（需删除10张表）

[root@hadoop001 etl]# cat delete_hive_metadata.sh 
#! /bin/bash
mysql -uroot -p123456 hive -e"
delete FROM PARTITION_PARAMS WHERE PART_ID=$4;
delete FROM PARTITION_KEY_VALS WHERE PART_ID=$4;
delete FROM  PARTITIONS   WHERE TBL_ID=$1;

delete FROM  PARTITION_KEYS WHERE TBL_ID=$1;
delete FROM TABLE_PARAMS WHERE TBL_ID=$1;
delete FROM TBLS WHERE TBL_ID=$1;
delete FROM SERDE_PARAMS WHERE SERDE_ID=$5;
delete FROM SERDES WHERE SERDE_ID=$5;

delete FROM SDS WHERE SD_ID=$2;
delete FROM COLUMNS_V2 WHERE CD_ID=$3;"

注意删除表的顺序，有些表存在外键。

执行删除脚本，后面的五个值分别为五个ID

[root@hadoop001 etl]# sh delete_hive_metadata.sh 21 26 21 6 26
Warning: Using a password on the command line interface can be insecure.