Forum Discussion
Data Pipeline- Copy Activity - Data Source - Restful API - Pagination
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?
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.
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=startResponse:
{
"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-sdflkjfselj43Response:
{
"customers": [
{ .... customer objects .... }
],
"cursor": "975437-2d5fh-7n6td-wfy5-qxedrvtbgsy27"
}
Request 3: https://api.somecrmwebsite.com/v1/customer?cursor=975437-2d5fh-7n6td-wfy5-qxedrvtbgsy27Get Request:
https://api.somecrmwebsite.com/v1/customer?cursor=975437-2d5fh-7n6td-wfy5-qxedrvtbgsy27Response:
{
"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?
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
