java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date
程序员文章站
2022-04-21 12:04:17
...
在进行spark程序连接mysql数据库时,遇到以上问题。
java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date
在mysql数据库中有一个时间字段:timeinfo,异常值默认为“0000-00-00”
问题代码如下:
// hive和mysql关联使用
// 加载Hive表数据
val hiveDF = spark.table("fanyanyan.lianjia")
// 加载MySQL表数据
val mysqlDF = spark.read.format("jdbc")
.option("url", "jdbc:mysql://node1:3306")
.option("dbtable", "fanyanyan.lianjia")
.option("user", "root")
.option("password", "123456")
.option("driver", "com.mysql.jdbc.Driver")
.load()
// JOIN
val resultDF = hiveDF.join(mysqlDF, hiveDF.col("url") === mysqlDF.col("url"))
resultDF.show
解决方法:
在jdbc的url加上 zeroDateTimeBehavior 参数。
即:加入
.option("zeroDateTimeBehavior","convertToNull")
正确的代码段如下所示:
// 加载MySQL表数据
val mysqlDF = spark.read.format("jdbc")
.option("url", "jdbc:mysql://node1:3306")
.option("dbtable", "fanyanyan.lianjia")
.option("user", "root")
.option("password", "123456")
.option("driver", "com.mysql.jdbc.Driver")
.option("zeroDateTimeBehavior","convertToNull")
.load()
推荐阅读
-
解决:java.sql.SQLException:Value ‘0000-00-00‘ can not be represented as java.sql.Date
-
java.sql.SQLException: Value ‘0000-00-00 00:00:00‘ can not be represented as java.sql.Timestamp异常
-
FAQ(60):java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date
-
java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Tim 异常解决办法
-
mysql错误:Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp;的解决方法
-
FAQ(64):java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date
-
java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date(已解决)
-
java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp at
-
java.sql.SQLException: Value ‘0000-00-00 00:00:00’ can not be represented as java.sql.Timestamp
-
SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp