Forum Discussion
Data Pipeline- Copy Activity - Data Source - Restful API - Pagination
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?
9 Replies
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:
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.
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=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 .... }
]
}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?
