CODEWITHSUNDEEP
javaapache-sparkexceptionjdbcapache-spark-sql
humblecoder
humblecoder

Reputation: 137

Spark DataFrame write to JDBC - Can't get JDBC type for struct <date:int, day:int...>?

I am new to spark, and was trying to write a dataframe to db2 table. The error I am getting is:

Exception in thread "main" java.lang.IllegalArgumentException: Can't get JDBC type for struct <data:int, day:int, hours:int, minutes:int, month:int, seconds:int, time:bigint, timeZoneOffset:int, year:int>

My database schema is 

localId <-- Integer type
effectiveDate <-- Timestamp
activityDate <-- Timestamp
inDate <-- Timestamp
outDate <-- Timestamp

I created a POJO class for my db table which goes like this 

public class StowageTable {
    private long localId;
    private Date effectiveDate;
    private Date activityDate;
    private Date inDate;
    private Date outDate;
    //setters and getters
}

I then basically read a csv which has the same schema as the db table as follows:

JavaRDD<String> dataFromCSV = javaSparkContext.textFile(fileURL);
//The I create a JavaRDD of the POJO type
JavaRDD<StowageTable> dataToPOJO = dataFromCSV.map((Function<String,  StowageTable) line -> {
    String[] fields = line.split(",");
    StowageTable st = createNewStowageTable(fields);
    return st;
});
//converting the RDD to DataFrame
DataFrame stowageTableDF = sqlContext.createDataFrame(dataToPOJO, StowageTable.class);
//call jdbc persister
persistToTable(stowageTableDF);

My persistToTable(DataFrame df) method is as follows:

private void persistToTable(DataFrame df) {
    Class.forName("")//driver here
    //skipping a few lines for brevity
    df.write().mode(SaveMode.Append).jdbc(url, table, connectionProperties);
}

I found a few solutions here: Spark DataFrame write to JDBC - Can't get JDBC type for array<array<int>> and java.lang.IllegalArgumentException: Can't get JDBC type for array<string> but could not find any which addresses a date-time data type issue. Please suggest me some solution to it. I am on spark 1.6.3.

Upvotes: 4

Views: 6597

Answers (1)

humblecoder
humblecoder

Reputation: 137

Since I could not find any answer yet, and figured out a solution for myself in the meantime so here is the basic idea. If the database has datatype as Timestamp, then you have to use Timestamp in the POJO of the object and then convert that Timestamp to spark's structtype.

Upvotes: 1

Related Questions