Loop through the REST API to get more than 5000 rows

Kumar, Arun 21 Reputation points
2022-12-31T15:01:45.207+00:00

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

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
2,613 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
6,278 questions
{count} votes

Accepted answer
  1. ShaikMaheer-MSFT 28,101 Reputation points Microsoft Employee
    2023-01-03T05:33:34.057+00:00

    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.


0 additional answers

Sort by: Most helpful