Proper Pagination Format for Rest API with Azure Data Factory

Question

Proper Pagination Format for Rest API with Azure Data Factory

Ryan James 36

I'm trying to use the Azure Data Factory to copy data from a REST API into an Azure SQL Database.

The next page is returned as a Link Header formatted as below:

; rel="next"

How do I format this into the Pagination Rules in the copy data activity?

Thanks,

MartinJaffer-MSFT 26,161 Reputation points

2021-01-07T21:19:46.833+00:00

Hello @Ryan James and welcome to Microsoft Q&A.

The sample you provided looks like either xml or html (because it starts with </ ... > ). The REST dataset type expects JSON response. Pagination aside, are you getting the data OK?

This pagination type should be used with AbsoluteUrl as the pagination key
Ryan James 36 Reputation points

2021-01-07T21:30:20.977+00:00

Hey @MartinJaffer-MSFT , according to Postman the NextLink format is actually RFC 5988, ( I could be wrong on that ).

The rest of the output from the API is in Jason, but the headers don't appear to be, so the data is coming in just fine. I was hoping to figure a way to parse this header response, but I've been unable to thus far.
MartinJaffer-MSFT 26,161 Reputation points

2021-01-11T23:36:20.563+00:00

@Ryan James the pagination options for REST are very limited. I am not certain the pagination rules will be able to parse this.
I know how to make a pipeline dynamic expression to parse this, but, last I checked, the pagination rules did not allow dynamic expression.

If the pagination rules cannot handle this, then another option is implementing a custom pagination mechanism. This typically consists of placing the copy activity in a loop. Each iteration adjusts the headers / parameters / URL. Are you open to this?
Ryan James 36 Reputation points

2021-01-12T01:01:04.397+00:00

@MartinJaffer-MSFT I would be open to hearing about. It can parse the returned link? There's millions of rows here, hundred of millions in a couple of cases. Appreciate the help!
MartinJaffer-MSFT 26,161 Reputation points

2021-01-13T00:33:58.097+00:00

@Ryan James , Your API , does it happen to be public, something I could do a few test runs on? I understand if it is not. It would just make things easier to reproduce on my side.
Ryan James 36 Reputation points

2021-01-13T00:57:33.443+00:00

@MartinJaffer-MSFT unfortunately it's not, it's a subscription data service. I can send you some screenshots and the developer access and notes, but I can't share the login info.
vince 6 Reputation points

2021-01-19T18:45:26.897+00:00
I am having the exact same issue, and oddly enough it looks like I'm using the exact same API (@Ryan James : DI V2?).

@MartinJaffer-MSFT the vendor's API uses RFC-5988/RFC-8288 web-linking headers to handle pagination. For example:

Link: </permits?action=next&api10=ne%28nil%29&api10=ne%28UNKNOWN%29&deleteddate=nil&next_page=permitid+%3C+2068234&pagesize=10>; rel="next"

The relative URL value is contained within the first angle-brackets-block: </permits?...>; ... and the link type is contained in the rel tag -- in this case it's a link to the next page.

The problem with using the value in the activity pagination rule is that it seemingly naively uses the entire string rather than just plucking the relative URL.

I haven't been able to find a way to handle this with dynamic content, nor have I been able to find a way to parse the response headers in another activity/loop.
Ryan James 36 Reputation points

2021-01-19T22:39:03.657+00:00

Yes, @vince I'm working with DIV2 API too. Haven't made any breakthroughs on fixing this yet.

2 answers

Your answer

MartinJaffer-MSFT 26,161 Reputation points

2021-01-07T21:19:46.833+00:00

Hello @Ryan James and welcome to Microsoft Q&A.

The sample you provided looks like either xml or html (because it starts with </ ... > ). The REST dataset type expects JSON response. Pagination aside, are you getting the data OK?

This pagination type should be used with AbsoluteUrl as the pagination key
Ryan James 36 Reputation points

2021-01-07T21:30:20.977+00:00

Hey @MartinJaffer-MSFT , according to Postman the NextLink format is actually RFC 5988, ( I could be wrong on that ).

The rest of the output from the API is in Jason, but the headers don't appear to be, so the data is coming in just fine. I was hoping to figure a way to parse this header response, but I've been unable to thus far.
MartinJaffer-MSFT 26,161 Reputation points

2021-01-11T23:36:20.563+00:00

@Ryan James the pagination options for REST are very limited. I am not certain the pagination rules will be able to parse this.
I know how to make a pipeline dynamic expression to parse this, but, last I checked, the pagination rules did not allow dynamic expression.

If the pagination rules cannot handle this, then another option is implementing a custom pagination mechanism. This typically consists of placing the copy activity in a loop. Each iteration adjusts the headers / parameters / URL. Are you open to this?
Ryan James 36 Reputation points

2021-01-12T01:01:04.397+00:00

@MartinJaffer-MSFT I would be open to hearing about. It can parse the returned link? There's millions of rows here, hundred of millions in a couple of cases. Appreciate the help!
MartinJaffer-MSFT 26,161 Reputation points

2021-01-13T00:33:58.097+00:00

@Ryan James , Your API , does it happen to be public, something I could do a few test runs on? I understand if it is not. It would just make things easier to reproduce on my side.
Ryan James 36 Reputation points

2021-01-13T00:57:33.443+00:00

@MartinJaffer-MSFT unfortunately it's not, it's a subscription data service. I can send you some screenshots and the developer access and notes, but I can't share the login info.
vince 6 Reputation points

2021-01-19T18:45:26.897+00:00

I am having the exact same issue, and oddly enough it looks like I'm using the exact same API (@Ryan James : DI V2?).

@MartinJaffer-MSFT the vendor's API uses RFC-5988/RFC-8288 web-linking headers to handle pagination. For example:

Link: </permits?action=next&api10=ne%28nil%29&api10=ne%28UNKNOWN%29&deleteddate=nil&next_page=permitid+%3C+2068234&pagesize=10>; rel="next"

The relative URL value is contained within the first angle-brackets-block: </permits?...>; ... and the link type is contained in the rel tag -- in this case it's a link to the next page.

The problem with using the value in the activity pagination rule is that it seemingly naively uses the entire string rather than just plucking the relative URL.

I haven't been able to find a way to handle this with dynamic content, nor have I been able to find a way to parse the response headers in another activity/loop.
Ryan James 36 Reputation points

2021-01-19T22:39:03.657+00:00

Yes, @vince I'm working with DIV2 API too. Haven't made any breakthroughs on fixing this yet.

Answer 1

MartinJaffer-MSFT 26,161

@Ryan James , @vince
I heard back from the product group. Unfortunately this specific scenario is not supported at this time.

To help this scenario be supported in the future, please request / upvote it in the feedback forum.

You can use a Custom Activity to run your own custom code and save the results. Then you can use a Copy Activity to load the results to the destination of your choice.

Other alternatives include Function App or possibly Logic App.

vince 6 Reputation points

2021-01-22T15:31:45.023+00:00

@MartinJaffer-MSFT thanks!
Ryan James 36 Reputation points

2021-02-04T21:57:52.187+00:00

@vince I've now got a working ADF model that uses the Batch process and a modified Python script. If you are interested, I'm happy to share it.
Matt Symes 1 Reputation point

2021-07-26T13:19:35.863+00:00

RyanJames-9249 - yes please, that would be helpful. Just about to have to code the same so be great to slurp from the pool of prior art!
Ryan James 36 Reputation points

2021-07-26T14:04:00.023+00:00

@Matt Symes can you send me a direct message or provide an email and I can get you some docs.
InduK 1 Reputation point

2021-09-16T03:40:14.4+00:00

@Ryan James /@Matt Symes I am facing the same issue with ADF can you please share your working ADF model if it is resolved for you
Preeti 1 Reputation point

2022-07-13T23:50:49.693+00:00

@MartinJaffer-MSFT - This post is probably quite old. But wanted to check if this scenario is supported now. Although it says here in this document that ADF supports it by default, it does not seem to be working for us. https://learn.microsoft.com/en-us/azure/data-factory/connector-rest?tabs=data-factory

Answer 2

MartinJaffer-MSFT 26,161

@Preeti

RFC 5988 is supported now. In Pagination rules, there is an option for it. When creating a new Copy activity, this option is added. (see picture below). However if you remove the option, then it will not be used.

Vesa-Matti Teppo 11 Reputation points

2022-08-11T15:19:38.537+00:00

Looks like this new pagination rule using RFC5988 works nicely, when there is only one link section in the header. If there are multiple link sections, data factory is not working properly, especially when first link section contains something else than rel="next" relation type.

For example, this is ok for data factory:
Link: <https://example.org/>; rel="self",
<https://example.org/page2>; rel="next"

But this kind of header link combination is not working:
Link: <https://example.org/>; rel="self"
Link: <https://example.org/page2>; rel="next"

The latter example is not in the RFC5988 documentation, but it is in the RFC8288 documentation which obsoletes RFC5988. In my opinion, data factory should support both ways. We are struggling with one API which uses multiple link sections and, of course, we really wanted to use this new feature instead parsing link for next page other ways.

Hopefully product team is able to fix this as soon as possible.
MartinJaffer-MSFT 26,161 Reputation points

2022-08-23T18:33:46.27+00:00

I have created a feedback item for this "Update RFC5988 to RFC8288"
ADO 15352504

@Vesa-Matti Teppo

You can also add your feedback in the Factory Studio
Minh Tran 0 Reputation points

2023-02-20T08:22:33.73+00:00

did RFC8288 got updated?
Valentin Duhamel 1 Reputation point

2023-08-04T08:20:36.73+00:00

Hi @MartinJaffer-MSFT , any update on this? I'm facing same issue

Share via

Proper Pagination Format for Rest API with Azure Data Factory

2 answers

Your answer