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

DanHoughton

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

 

laura_igl

Can anyone help with this request?

Thanks,

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

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?