1.问题描述:
使用spark通过jdbc往clickhouse批量插入数据时,报错,异常信息如下:
Job aborted due to stage failure: Task 0 in stage 33.0 failed 4 times, most recent failure: Lost task 0.3 in stage 33.0 (TID 607, hdp-dn-33-30.wanda.prod.cn, executor 7): java.lang.NullPointerException
at com.github.housepower.jdbc.data.type.DataTypeInt64.serializeBinary(DataTypeInt64.java:77)
at com.github.housepower.jdbc.data.Column.write(Column.java:31)
at com.github.housepower.jdbc.data.Block.appendRow(Block.java:93)
at com.github.housepower.jdbc.statement.ClickHousePreparedInsertStatement.addParameters(ClickHousePreparedInsertStatement.java:162)
at com.github.housepower.jdbc.statement.ClickHousePreparedInsertStatement.addBatch(ClickHousePreparedInsertStatement.java:95)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$.savePartition(JdbcUtils.scala:651)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$saveTable$1.apply(JdbcUtils.scala:821)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$saveTable$1.apply(JdbcUtils.scala:821)
at org.apache.spark.rdd.RDD$$anonfun$foreachPartition$1$$anonfun$apply$29.apply(RDD.scala:935)
at org.apache.spark.rdd.RDD$$anonfun$foreachPartition$1$$anonfun$apply$29.apply(RDD.scala:935)
at org.apache.spark.SparkContext$$anonfun$runJob$5.apply(SparkContext.scala:2074)
at org.apache.spark.SparkContext$$anonfun$runJob$5.apply(SparkContext.scala:2074)
at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
at org.apache.spark.scheduler.Task.run(Task.scala:109)
at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:345)
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)
Driver stacktrace:
报错代码行:
df.write.mode(SaveMode.Append).option("batchsize", 1000000).option("numPartitions", "1").option("isolationLevel", "NONE").jdbc(prop.getProperty("url"), tableName_insert, prop)
通过查看代码没有发现哪有空指针,而且错误日志也很难发现到底是哪有空指针。
2.解决方法:
通过查看数据,以及表结构发现问题所在。
表字段:
`agencykey` Int64,
`agencylevelinfoid` Nullable(Int64),
`agencylevelid` Nullable(String),
`agencylevelname` Nullable(String),
`deleteddate` Nullable(String),
`insertTime` Nullable(String)
上面agencykey字段是不允许为空的,但是插入的DataFrame中该字段的数据为null,所以报错空指针。
此时,再回头看错误信息,发现一行信息:
com.github.housepower.jdbc.data.type.DataTypeInt64.serializeBinary(DataTypeInt64.java:77)
该行信息提示出了字段类型Int64,所以检查类型为Int64的字段。
记录一下,以防以后在发生这样的错误。