Ask Your Question
1

How to properly handle JDBC OFFSETS using a timestamp

asked 2017-09-21 22:14:03 -0600

mstang gravatar image

updated 2017-09-22 11:52:12 -0600

metadaddy gravatar image

I have a JDBC Query Consumer origin set for Incremental Mode using a DB2 TIMESTAMP column for the offset. This works fine until the process is stopped and restarted which gives an error until I copy the offset from the pipeline's offset.json file into the Initial Offset field, reset the origin, and restart the pipeline... not sure what it doesn't like.

Where clause looks like this:

WHERE UPDATED_DATE > ${OFFSET} 
ORDER BY UPDATED_DATE

Initial offset looks like this: 2017-09-21 01:30:08.0

And offset.json looks like this when it stops and won't restart:

{
  "offsets" : {
    "$com.streamsets.datacollector.pollsource.offset$" : "2017-09-21 10:10:12.3"
  },
  "version" : 2
}

If I copy 2017-09-21 10:10:12.3 to Initial Offset and reset the origin the pipeline runs.

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted
1

answered 2017-09-22 09:08:29 -0600

mstang gravatar image

updated 2017-09-22 11:52:47 -0600

metadaddy gravatar image

What I had to do here was to change the WHERE clause to:

WHERE UPDATED_DATE > TIMESTAMP('${OFFSET}') ORDER BY UPDATED_DATE

and then change the the initial offset to : 2017-09-21 01:30:08.0

oddly, when I tried just changing the WHERE clause to:

WHERE UPDATED_DATE > '${OFFSET}' ORDER BY UPDATED_DATE

the offset was seen as ' '

edit flag offensive delete link more

Comments

That is odd. Did you try setting log level to DEBUG and looking in sdc.log? That should show the query with the value of `${OFFSET}` substituted in.

metadaddy gravatar imagemetadaddy ( 2017-09-22 11:53:57 -0600 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-09-21 22:14:03 -0600

Seen: 168 times

Last updated: Sep 22 '17