Ask Your Question
0

Can't JDBC Query Consumer incremental mode work with DateTime type offset column?

asked 2017-11-08 21:07:57 -0500

casel.chen gravatar image

updated 2017-11-08 21:16:18 -0500

After click "Run" button, the pipeline worked well and the data ingested into HBase as expected, but after drift all records in MySQL and no more new records come in, it complains the following error message:

Pipeline Status: RUNNING_ERROR: com.streamsets.pipeline.api.StageException: JDBC_77 - MySQLSyntaxErrorException attempting to execute query 'SELECT * FROM d_landa_yys_info WHERE updated_at > 2017-11-06 14:20:08.0 ORDER BY updated_at'. Giving up after 1 errors as per stage configuration. First error: SQLState: 42000 Error Code: 1064 Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '14:20:08.0 ORDER BY updated_at' at line 1

image description

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted
-1

answered 2017-11-08 22:00:42 -0500

jeff gravatar image

It looks as though MySQL is returning the updated_at column value as a String representation of the timestamp. If that's the case, the query should treat it as a string, since SDC will simply insert the value as shown. Try surrounding the ${OFFSET} portion with quotes, like '${OFFSET}'.

edit flag offensive delete link more

Comments

I found working on the way of your suggestion, there is a risk of losing data. My origin mysql table has 44836 records, but after run pipeline, it only synced 43975 records. Then, I removed the quote around ${OFFSET} and reset origin then run pipeline again, this time the whole 44836 records synced

casel.chen gravatar imagecasel.chen ( 2017-11-12 20:12:09 -0500 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-11-08 21:07:57 -0500

Seen: 223 times

Last updated: Nov 08 '17