Custom connector - append all rows with an ID greater than last appended

DanHoughton
DanHoughton
in Archive

I am making a custom connector. The data vendor’s api needs me to send the IDs of the rows I want to ingest, and they can then send up to 10-20k rows without a time-out. So, I need to break up my requests in blocks of 10k rows, and give the ID range each block. I can run this every hour, as each hour will be less than 10k rows, and append.

 

Question is, what’s the best way to query the existing table, grab the highest ID present, add one, then use this as a starting ID for the next pull?

 

Notes:

 

- the vendor has a version of the Api that works with dates, but even a single day can cause time outs. 

 

- the feed is json

 

- I am using append, rather than replace

 

Comments

  • laura_igl
    laura_igl

    Can anyone help with this request?

    Thanks,

  • DanHoughton
    DanHoughton

    I've just noticed in the Redshift Advanced connector, there's the ability to pass parameters into your SQL, including:

     

    Enter the query parameter value, it is the initial value for query parameter. The last run date is optional by default it is '02/01/1700' if is not provided. For example: !{lastvalue:_id}!=1,!{lastrundate:start_date}!=02/01/1944

  • DanHoughton
    DanHoughton

    To be clear, this doesn't solve my issue, as my connector is JSON, not Redshift. Is there a way of achieving something similar with JSON?

Categories