说明：hive清理元数据，由于分区名称带有中文名称，导致表无法删除，故手动清理元数据库。
1、查询tbl_id和part_id
注意：一个库可能会有同名的表。确认要删的表属于哪个库
select * from TBLS where TBL_NAME=‘dwd_mhs_ipt_dischargesummary’ G
得到tbl_id、sd_id
select * from PARTITIONS where tbl_id=‘974’ G
得到part_id，有多个part_id
得到表ID和分区ID：
tbl_id=974 sd_id=xxx CD_ID=xxx，PART_ID=1198 ，SERDE_ID=xxx
2、删除相关元数据。
删除表ID：
delete from tbl_col_privs where TBL_ID=‘974’;
delete from tbl_privs where TBL_ID=‘974’;
删除分区ID：
delete from PARTITION_PARAMS where part_id =‘1198’;
delete from PARTITION_KEY_VALS where part_id=‘1198’;
delete from PARTITION_PARAMS where part_id =‘1199’;
delete from PARTITION_KEY_VALS where part_id=‘1199’;
批量删分区ID：
delete from PARTITION_PARAMS where part_id IN (‘1227’,‘1228’,‘1229’,‘1230’,‘1231’,‘1232’,‘1233’,‘1234’,‘1235’,‘1236’,‘1237’,‘1238’,‘1239’,‘1240’,‘1241’,‘1242’,‘1243’,‘1244’,‘1245’,‘1246’,‘1247’,‘1248’,‘1249’,‘1250’);
delete from PARTITION_KEY_VALS where part_id IN (‘1227’,‘1228’,‘1229’,‘1230’,‘1231’,‘1232’,‘1233’,‘1234’,‘1235’,‘1236’,‘1237’,‘1238’,‘1239’,‘1240’,‘1241’,‘1242’,‘1243’,‘1244’,‘1245’,‘1246’,‘1247’,‘1248’,‘1249’,‘1250’);
删除分区相关：
delete from PARTITIONS where tbl_id=‘974’;
delete from partition_keys where TBL_ID=‘974’;
注意顺序
3、 hive中删除表：
drop table xxtable;
4、删除hdfs文件
hdfs dfs -rm
其它：
delete from table_params where TBL_ID=‘974’;
delete from TBLS where TBL_ID=‘974’;
delete from sds where sd_id=‘977’;
如需要支持中文分区：修改partition_name 为utf8
show full columns from part_col_stats;
alter table PART_COL_STATS modify column PARTITION_NAME varchar(500) character set utf8;
附：
1.查看表信息
mysql> select * from TBLS where TBL_NAME=‘ads_hosp_timely_mon_hive_v2’ G;
*************************** 1. row ***************************
TBL_ID: 531
CREATE_TIME: 1635302335
DB_ID: 71
LAST_ACCESS_TIME: 0
OWNER:
OWNER_TYPE: USER
RETENTION: 0
SD_ID: 2683
TBL_NAME: ads_hosp_timely_mon_hive_v2
TBL_TYPE: MANAGED_TABLE
VIEW_EXPANDED_TEXT: NULL
VIEW_ORIGINAL_TEXT: NULL
IS_REWRITE_ENABLED:
WRITE_ID: 0
mysql> select * from PARTITIONS where tbl_id=531 G;
*************************** 1. row ***************************
PART_ID: 2586
CREATE_TIME: 1635515531
LAST_ACCESS_TIME: 0
PART_NAME: month=???
SD_ID: 3127
TBL_ID: 531
WRITE_ID: 0
