hive3删除分区时显示未找到该分区

Hive3删除分区时显示未找到该分区

问题描述：在执行"alter table student_tmp drop partition(p_date_sttc=20191001);"命令时报错！！

1.错误描述

Error: Error while compiling statement: FAILED: SemanticException [Error 10006]: Partition not found (p_date_sttc = '20191001') (state=42000,code=10006)

2.问题分析

1.查询语法。 ——没问题！
2.查询是否存在该分区。 ——分区存在！！

0: jdbc:hive2:***************> show partitions student_tmp;
INFO  : Compiling command(queryId=hive_20191104191952_4def8e20-c8fd-443b-b76d-adf6db94dd80): show partitions student_tmp
INFO  : Semantic Analysis Completed (retrial = false)
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:partition, type:string, comment:from deserializer)], properties:null)
INFO  : Completed compiling command(queryId=hive_20191104191952_4def8e20-c8fd-443b-b76d-adf6db94dd80); Time taken: 0.115 seconds
INFO  : Executing command(queryId=hive_20191104191952_4def8e20-c8fd-443b-b76d-adf6db94dd80): show partitions student_tmp
INFO  : Starting task [Stage-0:DDL] in serial mode
INFO  : Completed executing command(queryId=hive_20191104191952_4def8e20-c8fd-443b-b76d-adf6db94dd80); Time taken: 0.094 seconds
INFO  : OK
+-----------------------+
|       partition       |
+-----------------------+
| p_date_sttc=20191001  |
+-----------------------+

3.查看表结构。

0: jdbc:hive2:***************>  show create table student_tmp;
......
......
......
+----------------------------------------------------+
|                   createtab_stmt                   |
+----------------------------------------------------+
| CREATE TABLE `student_tmp`(                        |
|   `student_id` varchar(10) COMMENT '??',           |
|   `subject` varchar(30) COMMENT '??',              |
|   `score` decimal(5,1) COMMENT '??',               |
|   `es_date` varchar(10) COMMENT '??????')          |
| COMMENT '?????'                                    |
| PARTITIONED BY (                                   |
|   `p_date_sttc` varchar(8))                        |
| ROW FORMAT SERDE                                   |
|   'org.apache.hadoop.hive.ql.io.orc.OrcSerde'      |
| WITH SERDEPROPERTIES (                             |
|   'field.delim'='|',                               |
|   'serialization.format'='|')                      |
| STORED AS INPUTFORMAT                              |
|   'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'  |
| OUTPUTFORMAT                                       |
|   'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' |
| LOCATION                                           |
|   'hdfs://******/warehouse/tablespace/managed/hive/student_tmp' |
| TBLPROPERTIES (                                    |
|   'bucketing_version'='2',                         |
|   'transactional'='true',                          |
|   'transactional_properties'='default',            |
|   'transient_lastDdlTime'='1572866338')            |
+----------------------------------------------------+

分析整个建表语句貌似没什么问题。
然而，此时有一位大佬经过，瞄了一眼，说了一句：“这个分区的字段类型看着有点奇怪…”
于是呼，事不宜迟，即刻对分区类型进行了更改
varchar(8) => string/char(8) !!!
_{紧接着一系列操作：删表=>更改类型后建表=>添加分区=>删除分区}

此时,当我执行文章开头的那句命令时,奇迹发生了该分区被顺利删除!!!

0: jdbc:hive2:***************> show partitions student_tmp;
......
+-----------------------+
|       partition       |
+-----------------------+
| p_date_sttc=20191001  |
+-----------------------+
1 row selected (0.254 seconds)
0: jdbc:hive2:***************> alter table student_tmp drop partition(p_date_sttc=20191001);
......
Time taken: 0.202 seconds
INFO  : OK
No rows affected (0.439 seconds)
0: jdbc:hive2:***************> show partitions student_tmp;
......
+------------+
| partition  |
+------------+
+------------+
No rows selected (0.241 seconds)

3.解决问题

在建立分区表时,注意分区字段的类型 !!!
在此hive版本下,用string/char代替varchar !!!
varchar也可以顺利添加分区，但是无法删除。
注：貌似只有varchar不行！！

后记

当前hive版本为：Hive 3.1.0.3.0.0.0-1634

$ hive --version
......
Hive 3.1.0.3.0.0.0-1634
Git git://ctr-e138-1518143905142-366319-01-000006.hwx.site/grid/0/jenkins/workspace/HDP-parallel-centos7/SOURCES/hive -r c5901f2263fbf508054b809d5e2b510c101c8504
Compiled by jenkins on Thu Jul 12 20:32:02 UTC 2018
From source with checksum 01d623de41911f0d1cfb36b6b44ccfd2