Loop through the REST API to get more than 5000 rows

Question

Hello,

I am creating a pipeline to load data from REST API to blob storage. The API returns only 5000 rows with below warning message. How can i get the full data with more than 5000 rows? The data is from 1983, so is there a way i can loop for first 10 years, then next 10 years like that?

"warnings": [
{
"warning": "incomplete return",
"description": "The API can only return 5000 rows in JSON format. Please consider constraining your request with facet, start, or end, or using offset to paginate results."
}

Thanks in advance for your help.

Arun

Answer 1

Hi @Kumar, Arun ,

Thank you for posting query in Microsoft Q&A Platform.

You need to make multiple API calls in loop to fetch data in batches. From your error message it seems your API support few parameters such as facet, start, or end, or using offset that helps to fetch data in batches.

Technically, you need to implement pipeline based which loops and use these parameters to fetch data. One exmple, was shared in above comments by @Madhusudhan P but by code.

I have created a video of implementing almost similar logic using ADF pipeline. Same can be adopted in Synapse pipeline as well. Please consider checking below video and implement in similar fashion.
Copy data from REST API which sends response in Pages using Azure data factory

Hope this helps. Please let me know if any further queries or how it goes.

--------------------

Please consider hitting Accept Answer button. Accepted answers help community as well.