一、确定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.