Pagination issue in Azure Data Factory

Kunal Kumar Sinha 171

I'm trying to fetch data from API where the result is split over multiple pages, The response from API is something as below.

{ "story_count": 212, "next": "?page=2", "previous": null, "results": [{

I tried using the rule as QueryParameters['next'] and value as $['next'] but the copy gets in infinite loop and fetches data only from first page.
I'm stuck how to fetch data for second and many more pages to come till I get next value as NULL, please help many thanks !

Kunal Kumar Sinha 171 Reputation points

2020-10-29T06:51:24.437+00:00

My API Url is - https://www.XXXX.com/api/e/v1/insights?page=1&tags_id=783_23075&start_date=2020-04-09
and the response something as below and is split over multiple pages till the value for next is Null

{
"story_count": 214,
"next": "?page=2",
"previous": null,
"results": [
{
I tried using QueryParameters.page and value as $.next but the copy activity keeps on running & I'm getting multiple response only for 1st page.
HarithaMaddi-MSFT 10,136 Reputation points

2020-10-29T08:27:21.623+00:00

Hi @Kunal Kumar Sinha ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

There are known limitations in using pagination in data factory and workaround commonly suggest is to loop through the total pages by reading the total page count before. Below are the references that explain the design with screenshots, please let us know if this approach helps.

reference1
reference2,
unable-to-use-the-pagination-rules-in-rest-sources-in-adf

Looking forward for your response.
Kunal Kumar Sinha 171 Reputation points

2020-10-29T09:36:23.3+00:00

Hi @HarithaMaddi-MSFT Thank you for your response. I understand your point here about the limitations of the pagination in data factory. The problem is that my API response does not return the number of pages it has, Can you please let me know how i can proceed in that case. Many Thanks!
HarithaMaddi-MSFT 10,136 Reputation points

2020-10-29T11:55:47.253+00:00

Hi @Kunal Kumar Sinha ,

Thanks for sharing the update. Can you please share data factory name, region and pipeline run id with us to further investigate and come back to you with solution.
Kunal Kumar Sinha 171 Reputation points

2020-10-29T12:46:20.257+00:00

Hi @HarithaMaddi-MSFT ,

The API response that I'm getting is as below where next states it has next page as 2 and so on. if it does not have next page the value for next becomes null.
{
"story_count": 212,
"next": "?page=2",
"previous": null,
"results": [
]
}
I've to implement to fetch the data for all the pages till next value is null for which i tried using in built functionality of pagination with key as QueryParameters['next'] and value as $['next'] but it gets into infinite loop and fetches duplicate data for only first page. Also can you access the ADF as you have asked the name, region?
HarithaMaddi-MSFT 10,136 Reputation points

2020-10-29T15:06:25.597+00:00

Hi @Kunal Kumar Sinha ,

We will not be able to access your data factory but we can see more detailed logs from our servers that might help us to investigate further. Please suggest if those can be shared.

Thanks for your patience!
Kunal Kumar Sinha 171 Reputation points

2020-10-29T16:00:02.74+00:00

Hi @HarithaMaddi-MSFT

ADF - acemwpdatradf01
Location - North Europe
Pipeline Name - pagination_test
Run id - 8f4032cb-5345-40ad-9cef-e6f0c9ff848b

Thanks,
Kunal
HarithaMaddi-MSFT 10,136 Reputation points

2020-10-30T08:37:45.397+00:00

Thanks @Kunal Kumar Sinha for sharing the details. I reached out to product team and will get back to you once we hear back from them.

Thanks for your patience!
Kunal Kumar Sinha 171 Reputation points

2020-11-02T05:08:32.65+00:00

Hi @HarithaMaddi-MSFT ,

Can you please let me know, if you got any update.

Thanks,
Kunal
HarithaMaddi-MSFT 10,136 Reputation points

2020-11-02T06:21:52.147+00:00

Thanks @Kunal Kumar Sinha for checking back. I just reached out to Product team and got an update that they are yet to look into it. I will keep you posted on the update as soon as I hear from them.

Thanks for your patience again!

Accepted answer

HarithaMaddi-MSFT 10,136 Reputation points

2020-11-02T13:51:30.15+00:00
Thanks @Kunal Kumar Sinha for waiting. I got an update from our team that it is not possible to implement this using the current response from API and one approach is API response to be modified as below, then pagination rules can be used in below format

{ "story_count": 212, "next": "2", "previous": null, "results": [] }

And the pagination rules should be:

QueryParameters['page']: $['next']

Another approach is to implement the manual pagination in which data can be fetched till the value for next becomes null.

Please let us know if this helps in resolving the issue.
Please sign in to rate this answer.

1 person found this answer helpful.
HarithaMaddi-MSFT 10,136 Reputation points

2020-11-03T07:04:10.393+00:00

Hi @Kunal Kumar Sinha ,

We have not received a response from you. Please suggest if the above workaround suggested from our team is helpful in implementing the requirement, let us know for further queries and we will be glad to assist.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Kunal Kumar Sinha 171 Reputation points

2020-11-03T07:25:57.373+00:00

Hi @HarithaMaddi-MSFT ,

I tried the suggestion provided but it's not working and it keeps on running and fetches the data only for the first page in each run. The suggested answer has different API response in which the next value is 2 but in actual the value is "?page=2". Please see below.

API Response is in below format.

{
"story_count": 212,
"next": "?page=2",
"previous": null,
"results": [
{

Pipeline Run Id - 599d2b69-a2ee-4312-ba14-7b0edf77862d

Thanks,
Kunal

HarithaMaddi-MSFT 10,136 Reputation points

2020-11-03T07:30:07.087+00:00

Thanks @Kunal Kumar Sinha for sharing the details. Unfortunately, without getting desired API response format, ADF will not be able to read the page details. Workaround was suggested by internal team as above. If this feature would help in implementing the requirement, I would recommend raising an idea in feedback forum which is closely monitored by data factory product team and will be considered for implementation in future releases.

Thanks for your understanding!

Kunal Kumar Sinha 171 Reputation points

2020-11-03T07:34:16.127+00:00

I'm implementing the manual pagination approach in which I'm trying to fetch the data till the value for next becomes null. Thank you @HarithaMaddi-MSFT for all your support and help !

HarithaMaddi-MSFT 10,136 Reputation points

2020-11-03T07:36:56.52+00:00

Thanks @Kunal Kumar Sinha for sharing the details.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Kunal Kumar Sinha 171 Reputation points

2020-11-03T07:40:55.863+00:00

Hi @HarithaMaddi-MSFT ,

The provided solution is not working for me so can I upvote and accept it ?

Thanks,
Kunal

HarithaMaddi-MSFT 10,136 Reputation points

2020-11-03T07:49:36.1+00:00

Thanks @Kunal Kumar Sinha for posting the query. Yes the approach suggested is one possible workaround and it was not feasible in your current requirement. I have added manual approach in the answer, please suggest if that helps in accepting the answer as the community members can use any of the above approaches according to their requirement.

Kunal Kumar Sinha 171 Reputation points

2020-11-03T07:52:32.54+00:00

Hi @HarithaMaddi-MSFT , I have accepted the answer and as always, thanks for all your help & support ! Meanwhile I've successfully tested the manual approach. :)

HarithaMaddi-MSFT 10,136 Reputation points

2020-11-03T08:08:34.89+00:00

@Kunal Kumar Sinha - Glad to know that it worked successfully.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Pagination issue in Azure Data Factory

0 additional answers

Your answer