how to read a db table from the last access

[ptn]

i want to poll a db table every 5 minutes and read only new or updated records. there is an "update timestamp" field on the source table, so can use this to determine what has changed. this is moving data from oracle to postgres. essentially i am looking to replicate the data from A to B. want to design in a way that if the job fails, the next time it will read from where it left off.

basically i would like to first migrate the last 30 days of data from oracle table A to postgres table B. both have the same 10 columns.

next every 5-10 minutes, i would like to read only new (or updated) records in A and apply to B.

what i think i need to do is first read the max "update timestamp" from table B - this will give me the starting point.

then pass that max value to the source component's where clause. ie select * from A where updatets > +maxval+

here +maxval+ is a context or variable that contains the max value selected earlier.

i am kinda new to talend, have used before for certain tasks, but never did anything like this.

maybe there is something built in to do this exact thing? i could not find anything.

any help would be greatly appreciated!!

[pakapi_]

Hi ptn,

you wrote your job in words, now it's time to move it to talend components. Liitle hint. Try to use:

• tDBInput - to fetch max timestamp loaded to Postgres
• tSetGlobalVar - to save fetched timestamp in variable
• tDBInput - to SELECT data from Oracle with condition (>=) based on your variable form tSetGlobalVar
• tDBOutput - to save data passed from tDBInput in Postgres (INSERT OR UPDATE)

Enough for a start 🙂

[David_Beaty]

Hi,

Further to what @Kamil Kowalczyk​ said, you should probably ensure you don’t select data too close to the current system time - lag the selection by a few seconds or a minute. You might find that data doesn’t get committed to the source DB in chronological and you’d miss some data if you let the acquisition select data right up to “now”.

Thanks

David

[Pri_passion]

As per me, make one stage table in between source and target , which will filter the records from the last run of your target table.

and use insert and update option in target to dump the data from stage table.

your stage table should be truncate and load.

[DataTeam1]

@Paul Newman​ try to study example below. It should be exactly what you need

Notice that I convert last update timestamp to string type context and then I convert it again to timestamp in WHERE clause