Spark SQL查询Double列报错：FloatWritable无法转换为DoubleWritable

问题描述 #

执行查询Double类型列的Spark SQL操作时，抛出java.lang.ClassCastException: org.apache.hadoop.io.FloatWritable cannot be cast to org.apache.hadoop.io.DoubleWritable异常，怀疑Spark读取Hive中Double类型列的流程存在差异。

执行代码 #

val testBase = spark.sql(
      s"""select
         | cast(clk_rate_7_day as double)
         |from %s
         |where ds between date_sub('%s', 0) and '%s'
         |union all
         |select
         | cast(clk_rate_7_day as double)
         |from %s
         |where ds between date_sub('%s', 0) and '%s'
         |""".stripMargin.format(trainDataInput, jobDate, jobDate, trainDataYuncunInput, jobDate, jobDate))
testBase.show()

异常日志 #

testBase: org.apache.spark.sql.DataFrame = [clk_rate_7_day: double]
org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 2.0 failed 4 times, most recent failure: Lost task 0.3 in stage 2.0 (TID 11, hadoop4445.jd.163.org, executor 5): java.lang.ClassCastException: org.apache.hadoop.io.FloatWritable cannot be cast to org.apache.hadoop.io.DoubleWritable
    at org.apache.hadoop.hive.serde2.objectinspector.primitive.WritableDoubleObjectInspector.get(WritableDoubleObjectInspector.java:36)
    at org.apache.spark.sql.hive.HadoopTableReader$$anonfun$14$$anonfun$apply$8.apply(TableReader.scala:423)
    at org.apache.spark.sql.hive.HadoopTableReader$$anonfun$14$$anonfun$apply$8.apply(TableReader.scala:423)
    at org.apache.spark.sql.hive.HadoopTableReader$$anonfun$fillObject$2.apply(TableReader.scala:460)
    at org.apache.spark.sql.hive.HadoopTableReader$$anonfun$fillObject$2.apply(TableReader.scala:451)
    at scala.collection.Iterator$$anon$11.next(Iterator.scala:410)
    at scala.collection.Iterator$$anon$11.next(Iterator.scala:410)
    at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage1.processNext(Unknown Source)
    at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43)
    at org.apache.spark.sql.execution.WholeStageCodegenExec$$anonfun$13$$anon$1.hasNext(WholeStageCodegenExec.scala:636)
    at org.apache.spark.sql.execution.SparkPlan$$anonfun$2.apply(SparkPlan.scala:255)
    at org.apache.spark.sql.execution.SparkPlan$$anonfun$2.apply(SparkPlan.scala:247)

原因与解决方法 #

核心原因 #

异常本质是Hive表元数据标注的字段类型与底层实际存储的数据类型不匹配：

• 要么是Hive表元数据中clk_rate_7_day被标记为Double，但实际存储的是Float类型数据；
• 要么是union all关联的两张表中，clk_rate_7_day的实际存储类型不一致（一张是Double，一张是Float），Spark根据元数据统一按Double读取时，遇到Float类型数据触发转换异常。

解决步骤 #

1. 校验表字段的实际类型
   执行Hive命令查看表的元数据和存储格式：

   DESCRIBE FORMATTED your_table_name;
   

   重点检查clk_rate_7_day的Type字段，同时确认底层存储文件（如Parquet/ORC）的实际字段类型。

2. 修正SQL中的类型转换逻辑
   如果字段实际存储为Float，可调整转换方式：

   • 直接转为Float类型：

     cast(clk_rate_7_day as float)
     

   • 先转字符串再转Double（避免直接类型转换冲突）：

     cast(cast(clk_rate_7_day as string) as double)
     

3. 修复Hive表元数据
   如果是元数据标记错误，可通过修改表结构修正字段类型：

   ALTER TABLE your_table_name CHANGE COLUMN clk_rate_7_day clk_rate_7_day float;
   

   注意：修改前需确认实际存储数据类型，避免数据损坏。

4. Spark读取时强制类型转换
   也可以在Spark读取表后显式转换字段类型：

   val df1 = spark.read.table(trainDataInput)
     .filter(col("ds") === jobDate)
     .withColumn("clk_rate_7_day", col("clk_rate_7_day").cast(DoubleType))
   
   val df2 = spark.read.table(trainDataYuncunInput)
     .filter(col("ds") === jobDate)
     .withColumn("clk_rate_7_day", col("clk_rate_7_day").cast(DoubleType))
   
   val testBase = df1.unionAll(df2)
   testBase.show()
   

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