Data Pipeline- Copy Activity - Data Source - Restful API - Pagination

[TFE_techsupport]

In Microsoft fabric, for the data pipeline, when using the copy activity, how to use pagination rules when the ability to paginate is dependent on the URL and the child node result from the JSON from the API, for example, the URL to paginate the API result for the URL "https://api.somecrmwebsite.com/v1" is "https://api.somecrmwebsite.com/v1/customer?cursor=start" to start the API cursor extraction to get the first JSON data result set and to paginate through using the API's cursor, the JSON data set provides a child node called "cursor" with a guid value, this value must be presented such as  "https://api.somecrmwebsite.com/v1/customer?cursor=345452-344j-23jhj-sdu4-sdflkjfselj43"?  Also a follow up question, should I be using Azure Data Factory for this type of operation instead, if so can Azure Data Factory support it based on my question?

[TFE_techsupport]

@v-cboorla-msft 
Yes, I am looking for how to use the pagination rules in Microsoft fabric for the data pipeline using the copy data activity from an API but the implementation of pagination configuration for the API that I am trying to access does not use the standard pagination style and it was because of the 2 Microsoft Pagination documentation URLs as you have provided, that was the reason for me asking my question because I referred to them first, before posting to the forum.

In Microsoft fabric, for the data pipeline, when using the copy activity, how to use pagination rules when the ability to paginate is dependent on the API's pagination using a cursor engine instead to iterate through a recordset to provide JSON records instead of standard pagination hat are in the Microsoft Documentation from the URLs you provided.

Notice the example as I have provided below. Notice the initial request to the API as it starts the Pagination cursor engine for the URL "https://api.somecrmwebsite.com/v1". In the first request, the API call is "https://api.somecrmwebsite.com/v1/customer?cursor=start" and this request returns a JSON result that includes a child node called "cursor" and the value to child node key is used in API request #2 to get the a JSON result, which again, returns a JSON result that includes a child node called "cursor" and the value to child node key is used in API request #3 and so until there are no more JSON children nodes with "cursor".

Request 1: https://api.somecrmwebsite.com/v1/customer?cursor=start,

Get Request:
https://api.somecrmwebsite.com/v1/customer?cursor=start

Response:
{
"customers": [
{ .... customer objects .... }
],
"cursor": "345452-344j-23jhj-sdu4-sdflkjfselj43"
}

Request 2: https://api.somecrmwebsite.com/v1/customer?cursor=345452-344j-23jhj-sdu4-sdflkjfselj43,

Get Request:
https://api.somecrmwebsite.com/v1/customer?cursor=345452-344j-23jhj-sdu4-sdflkjfselj43

Response:
{
"customers": [
{ .... customer objects .... }
],
"cursor": "975437-2d5fh-7n6td-wfy5-qxedrvtbgsy27"
}

Request 3: https://api.somecrmwebsite.com/v1/customer?cursor=975437-2d5fh-7n6td-wfy5-qxedrvtbgsy27

Get Request:
https://api.somecrmwebsite.com/v1/customer?cursor=975437-2d5fh-7n6td-wfy5-qxedrvtbgsy27

Response:
{
"customers": [
{ .... customer objects .... }
]
}

How do I implement the correct pagination configuration solution using Microsoft Fabric Data Pipelines and/or Azure Data Factory?

[v-cboorla-msft]

Hi @TFE_techsupport 

Apologies for the delay in response.

Thank you for the information provided.

Please refer to this link REST API Call Pagination

Hope this helps. Please let me know in case of any queries.

[ParthShah7]

Hi @v-cboorla-msft 

I have seen your reply on link you mentioned to refer, however in my case within the api page the syntax of next ( here : more ) paginated page is written as

"more" : "albums?limit=25&after=MTAxNTExOTQ1MjAwNzI5ND="

 Hence I need to input some syntax in box next to AbsoluteURL drop-down 

Can you help me by mentioning what to mention there, as in your case full URL was mentioned in $.next you kept the mentioned box blank 

[TFE_techsupport]

@v-cboorla-msft 

I don't think you have an understanding of what I trying to say through post.  The URL you provided was to a stackoverflow discussion on how to use the AbsoluteURL when the JSON child node holds the Absolute URL for the next page in the recordset through REST API.  This is NOT what I am trying to do.

Here's what I am trying to do.

how to use the pagination rules in Microsoft fabric for the data pipeline using the copy data activity from an API but the implementation of pagination configuration for the API that I am trying to access does not use the standard pagination style and it was because of the 2 Microsoft Pagination documentation URLs as you have provided, that was the reason for me asking my question because I referred to them first, before posting to the forum.

In Microsoft fabric, for the data pipeline, when using the copy activity, how to use pagination rules when the ability to paginate is dependent on the API's pagination using a cursor engine instead to iterate through a recordset to provide JSON records instead of standard pagination hat are in the Microsoft Documentation from the URLs you provided.

Notice the example as I have provided below. Notice the initial request to the API as it starts the Pagination cursor engine for the URL "https://api.somecrmwebsite.com/v1". In the first request, the API call is "https://api.somecrmwebsite.com/v1/customer?cursor=start" and this request returns a JSON result that includes a child node called "cursor" and the value to child node key is used in API request #2 to get the a JSON result, which again, returns a JSON result that includes a child node called "cursor" and the value to child node key is used in API request #3 and so until there are no more JSON children nodes with "cursor".

Request 1: https://api.somecrmwebsite.com/v1/customer?cursor=start,

Get Request:
https://api.somecrmwebsite.com/v1/customer?cursor=start

Response:
{
"customers": [
{ .... customer objects .... }
],
"cursor": "345452-344j-23jhj-sdu4-sdflkjfselj43"
}

Request 2: https://api.somecrmwebsite.com/v1/customer?cursor=345452-344j-23jhj-sdu4-sdflkjfselj43,

Get Request:
https://api.somecrmwebsite.com/v1/customer?cursor=345452-344j-23jhj-sdu4-sdflkjfselj43

Response:
{
"customers": [
{ .... customer objects .... }
],
"cursor": "975437-2d5fh-7n6td-wfy5-qxedrvtbgsy27"
}

Request 3: https://api.somecrmwebsite.com/v1/customer?cursor=975437-2d5fh-7n6td-wfy5-qxedrvtbgsy27

Get Request:
https://api.somecrmwebsite.com/v1/customer?cursor=975437-2d5fh-7n6td-wfy5-qxedrvtbgsy27

Response:
{
"customers": [
{ .... customer objects .... }
]
}

Notice in my screenshot for the source of the Copy Activity, I have the relative URL that uses a pipeline expression builder to build out the relative URL in addition the basic URL stated above.

The expression is @{concat('customer?cursor=', if(equals('$.cursor', ''),'start' ,'$.cursor' ),'&updatedAt=',concat('gt:',formatDateTime(addDays(utcNow(),-40),'yyyy-MM-dd')))}

This expression successfully start the cursor (ELASTIC SEARCH) but does not paginate through the rest of the records.  It only returns me 50 records when there should be over 5,900 records.  When I place the same exact expression in the "ABSOLUTEURL" pagination text box it fails because of the pagination rule value, and if I attempt to change the expression as so "

@{concat('customer?cursor=', if(equals('$.cursor', ''),'start' ,'{mycursor}' ),'&updatedAt=',concat('gt:',formatDateTime(addDays(utcNow(),-40),'yyyy-MM-dd')))}

"  and add "{mycursor}" in the pagination with value type "Body" and value "cursor", it fails on the same error as just listed.  "Failure happened on 'Source' side. ErrorCode=RestInvalidPaginationRule,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Invalid PaginationRule, RuleKey='AbsoluteUrl', RuleValue='customer?cursor=$.cursor&updatedAt=gt:2023-09-13',Source=Microsoft.DataTransfer.ClientLibrary,'"

How do I get my expression to properly paginate through the REST API JSON recordset using ELASTIC SEARCH as listed above using Microsoft Fabric Data Pipelines and/or Azure Data Factory?

[LuukTijssen2]

I've got a simmulair issue, and struggling quite a lot. Did you manage to get this to work?

[TFE_techsupport]

Unfortunately, no I did not. I did end up using Power Query M to develop the solution I needed. Power Query M formula language reference - PowerQuery M | Microsoft Learn

[frithjof_v]

If the native pagination functionality is not a feasible alternative in this case (I wouldn't know, and I don't have the option to test it as I don't have read access to an API that uses cursor), perhaps it's possible to execute API call activities within an Until loop (or a ForEach loop with the Sequential box checked).

In that case, I think you would need a string variable, you could call it varCursor and set the default value to "start".

Then use this pipeline variable varCursor to build the relative path (or query parameters, if relevant) for your API request.

Then inside each iteration of the loop, after receiving the API response, set the pipeline variable (varCursor) equal to the cursor property of the API response. 

Then this updated varCursor value will be used in the API request in the next iteration.

I haven't tested it, but it sounds like something which might work.

@TFE_techsupport  I'm curious how to solve it in Power Query M. If you don't mind, could you please describe your solution?

[LuukTijssen2]

I ended up building a 'until' loop with seperated copy actions and now pagination... preforms way worse but it's working.

In hindsight a self-executing powerquery function couldn've done a better job, but as i've got what i needed i'll do that next time.

[v-cboorla-msft]

Hi @TFE_techsupport 

Welcome to Microsoft Fabric Community.

As I understand that you are looking for how to use the pagination rules in Microsoft fabric for the data pipeline using the copy data activity from an API.

Could you please provide me more detailed information about your ask so that I can help you better?

Meanwhile please refer the documents below for more information:

Pagination rules

Implementing Pagination with the Copy Activity in Microsoft Fabric

I hope this information is helpful. Please do let us know if you have any questions.