Spark JDBC DataFrame with TIMESTAMP(3) column

Question

I have a MySQL table with a TIMESTAMP(3) column that I want to pull in as a Spark DataFrame. The MySQL JDBC driver is failing to read the TIMESTAMP(3) column.

Is there a configuration or an efficient way I can specify my own Encoder to correctly parse this column?

Schema:

CREATE TABLE table_x
(
    user_id VARCHAR(255) NOT NULL,
    item_id VARCHAR(255) NOT NULL,
    serialized_item MEDIUMTEXT NOT NULL,
    creation_date TIMESTAMP DEFAULT 'CURRENT_TIMESTAMP' NOT NULL,
    last_updated_date TIMESTAMP(3) DEFAULT 'CURRENT_TIMESTAMP(3)' NOT NULL
);

Code:

import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.SQLContext
import org.apache.spark.sql.types._

val conf = new SparkConf().
  setMaster("local[4]").
  setAppName("AppName")
val sc = new SparkContext(conf)
val sqlContext = new SQLContext(sc)
import sqlContext.implicits._

val props = new Properties()
props.setProperty("user", "...")
props.setProperty("password", "...")
val df = sqlContext.read.jdbc("...", "table_x", props)
df.take(10).foreach(println)

Stacktrace:

java.sql.SQLException: Cannot convert value '2016-03-30 09:41:03.043' from column 6 to TIMESTAMP.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
        at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1321)
        at com.mysql.jdbc.BufferRow.getTimestampFast(BufferRow.java:573)
        at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:6617)
        at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5943)
        ...
Caused by: java.lang.IllegalArgumentException: nanos > 999999999 or < 0
        at java.sql.Timestamp.setNanos(Timestamp.java:389)
        at com.mysql.jdbc.TimeUtil.fastTimestampCreate(TimeUtil.java:1135)
        at com.mysql.jdbc.ResultSetImpl.fastTimestampCreate(ResultSetImpl.java:1030)
        at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1310)
        ...

You'll need to pull it as a string and process it afterwards — eliasah, Mar 30 '16 at 19:09
You can execute an arbitrary subquery in the `dbtable`. See for example http://stackoverflow.com/q/34365692/1560062 — zero323, Mar 30 '16 at 20:01
Actually switching to the latest MySQL JDBC driver (version 5.1.38) resolved this issue. I should have checked that earlier. — Cem Catikkas, Mar 30 '16 at 21:04
@CemCatikkas You should post it as an answer. It could be an useful information and can easily be lost from the comments. — zero323, Mar 31 '16 at 06:47

score 0 · Answer 1 · answered Mar 31 '16 at 23:26

0

I forgot to mention that I was using MySQL JDBC driver version 5.1.6.

I don't see this issue anymore with 5.1.38.

answered Mar 31 '16 at 23:26

Cem Catikkas

7,171
4
29
33

Spark JDBC DataFrame with TIMESTAMP(3) column

1 Answers1