Spark Scala向分区Hive表写入报Partition spec incorrect错误求助

1. Hive表结构 #

CREATE TABLE gcganamrswp_work.historical_trend_result(
column_name string,
metric_name string,
current_percentage string,
lower_threshold double,
upper_threshold double,
calc_status string,
final_status string,
support_override string,
dataset_name string,
insert_timestamp string,
appid string,
currentdate string,
indicator map<string,string>)
PARTITIONED BY (
appname string,
year_month int)
STORED AS PARQUET
TBLPROPERTIES ("parquet.compression"="SNAPPY");

2. Spark DataFrame Schema #

root
 |-- metric_name: string (nullable = true)
 |-- column_name: string (nullable = true)
 |-- Lower_Threshold: double (nullable = true)
 |-- Upper_Threshold: double (nullable = true)
 |-- Current_Percentage: double (nullable = true)
 |-- Calc_Status: string (nullable = false)
 |-- Final_Status: string (nullable = false)
 |-- support_override: string (nullable = false)
 |-- Dataset_Name: string (nullable = false)
 |-- insert_timestamp: string (nullable = false)
 |-- appId: string (nullable = false)
 |-- currentDate: string (nullable = false)
 |-- indicator: map (nullable = false)
 |    |-- key: string
 |    |-- value: string (valueContainsNull = false)
 |-- appname: string (nullable = false)
 |-- year_month: string (nullable = false)

3. 写入代码 #

spark.conf.set("hive.exec.dynamic.partition", "true")
spark.conf.set("hive.exec.dynamic.partition.mode", "nonstrict")
data_df.repartition(1)
  .write.mode("append")
  .format("hive")
  .insertInto(Outputhive_table)

4. 报错信息 #

ERROR Hive:1987 - Exception when loading partition with parameters
partPath=hdfs://gcgprod/data/work/hive/historical_trend_result/.hive-staging_hive_2021-09-01_04-34-04_254_8783620706620422928-1/-ext-10000/_temporary/0,
table=historical_trend_result,  partSpec={appname=, year_month=},
replace=false,  listBucketingEnabled=false,  isAcid=false,
hasFollowingStatsTask=false
org.apache.hadoop.hive.ql.metadata.HiveException:
MetaException(message:Partition spec is incorrect. {appname=,
year_month=})
	at org.apache.hadoop.hive.ql.metadata.Hive.loadPartitionInternal(Hive.java:1662)
	at org.apache.hadoop.hive.ql.metadata.Hive.lambda$loadDynamicPartitions$4(Hive.java:1970)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
Caused by: MetaException(message:Partition spec is incorrect. {appname=, year_month=})
	at org.apache.hadoop.hive.metastore.Warehouse.makePartName(Warehouse.java:329)
	at org.apache.hadoop.hive.metastore.Warehouse.makePartPath(Warehouse.java:312)
	at org.apache.hadoop.hive.ql.metadata.Hive.genPartPathFromTable(Hive.java:1751)
	at org.apache.hadoop.hive.ql.metadata.Hive.loadPartitionInternal(Hive.java:1607)

Spark 2.4版本的insertInto API严格按列的位置顺序匹配写入，不会按列名匹配。本次报错由两个问题叠加导致：

1. DataFrame的非分区列顺序、列名大小写、字段类型和Hive表定义不匹配，产生列错位
2. 列错位后，Spark读取最后两列作为分区值时，实际取到的是空值/非法值，导致分区spec为空抛出异常

核心逻辑是动态拉取目标Hive表的元数据，自动对齐DataFrame的列顺序、字段类型，不需要硬编码任何表相关的配置。

步骤1：自动对齐字段顺序与类型 #

// 动态获取目标Hive表的完整schema（非分区列在前，分区列在后，和Hive元数据顺序完全一致）
val targetTableSchema = spark.table(Outputhive_table).schema
val targetColNames = targetTableSchema.fieldNames.map(_.toLowerCase)

// 1. 将当前DataFrame列名统一转小写，消除大小写差异
// 2. 按目标表的列顺序重排列
// 3. 自动转换字段类型匹配目标表定义
val alignedDf = data_df.toDF(data_df.columns.map(_.toLowerCase): _*)
  .selectExpr(targetColNames.map(colName => {
    val targetDataType = targetTableSchema(targetColNames.indexOf(colName)).dataType.simpleString
    s"cast(`$colName` as $targetDataType) as $colName"
  }): _*)

步骤2：执行写入 #

spark.conf.set("hive.exec.dynamic.partition", "true")
spark.conf.set("hive.exec.dynamic.partition.mode", "nonstrict")

alignedDf.repartition(1)
  .write.mode("append")
  .format("hive")
  .insertInto(Outputhive_table)

• 确保DataFrame包含目标表所有必填字段，缺少的字段可提前补对应类型的空值
• 本次场景中year_month字段需要从字符串转为int，需确保原始值均为合法数字格式，避免转换报错
• 该逻辑对任意Hive表通用，无需针对单表配置分区字段或列顺序

内容的提问来源于stack exchange，提问作者Arvinth