Forum Discussion
Data Pipeline- Copy Activity - Data Source - Restful API - Pagination
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?
I've got a simmulair issue, and struggling quite a lot. Did you manage to get this to work?
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
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?
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.