locked
Unable to use the Pagination Rules in REST sources in ADF RRS feed

  • Question

  • Hi,

    I am trying to use the Pagination rules in the ADF rest source to download all my pages data. My API looks something like this: www.xyz.com?page=0 and i get the response like this : 
    {content:[],

    totalPages:"150",

    page:0}
    Here is the pagination rule that i am trying to write to fetch the next page data

    QueryParameters.page = @add(int($.page),1) but this is reflecting an error saying UnsupportedConcept and showing invalid.

    Please tell if there is any way to increment the value of a response and hit the api again

    Thursday, September 19, 2019 8:12 AM

All replies

  • Not sure if you have gone though the below doc , it does talk about the pagination . 

    https://docs.microsoft.com/en-us/azure/data-factory/connector-rest#pagination-support

    Please do let us know if you are still facing the issue .


    Thanks Himanshu


    Friday, September 20, 2019 12:28 AM
  • Hi Himanshu,

    I already went through the document. It basically gives three options to us. Here they are:

    1. If the response contains next page url then it can be used to load it into the AbsoluteUrl key. So for this the pagination rule will look something like this "AbsoluteUrl":"$.paging.nextUrl
    2. If the response contains next page URL then it can be used to load it into the AbsoluteUrl key. So for this the pagination rule will look something like this "AbsoluteUrl":"$.paging.nextUrl
      If the response contains a value for some query parameter (page in our case, but here we do not have the next page value in a query parameter, rather we have the current page number and the total pages number in the response. Generally to fetch the QueryParameter, pagination rule will look something like this : "QueryParameter.page":"$.page" . So here I was trying to put some dynamic expression to increment the value of this page variable. so in the dynamic expression, I wrote @add(int($.page),1) but this reflects the error UnsupportedConcept and showing invalid. If we even write any normal expression lets say @add(1,1) then also I was getting the same issue.
    3. the third one is regarding header which is not useful in our case.

    Thanks

     



    Friday, September 20, 2019 12:51 AM
  • My sincere apologizes for the delay on my side . 

    I see that the doc takes into account the response where they have a property in the response which points to the next page and hence its easy , but we can have situation where thats not the case . I took the example 

    https://reqres.in/api/users?page=1

    https://reqres.in/api/users?page=2

    The response does not have a property for the next page and its here where it becomes tricky . In this example we do have the totalpages properties and that what we are going to use . 

    Logic is very simple , but since we have  variable limitation/restriction in ADF so the setting counter and conversions makes its complicated .

    1. Get the total no of pages , in my case its i called the first page and its gives that totalpages property ( i used webactivity) . 

    2. I use the Untill activity and loop through the total pages . 

    3.Inside the Untill loop I will call in the Copy activity and will pass the uri as

    https://reqres.in/api/users?page=counter . 

    counter values ( 1 through totalpages ) 


    {
    "name": "repro_RESTAPI_pagination",
    "properties": {
    "activities": [
    {
    "name": "Call the API to the get the total pages",
    "type": "WebActivity",
    "dependsOn": [],
    "policy": {
    "timeout": "7.00:00:00",
    "retry": 0,
    "retryIntervalInSeconds": 30,
    "secureOutput": false,
    "secureInput": false
    },
    "userProperties": [],
    "typeProperties": {
    "url": "https://reqres.in/api/users",
    "method": "GET",
    "linkedServices": [
    {
    "referenceName": "REST_pagination",
    "type": "LinkedServiceReference"
    }
    ],
    "datasets": [
    {
    "referenceName": "RestResource1",
    "type": "DatasetReference"
    }
    ]
    }
    },
    {
    "name": "Get The total page",
    "type": "SetVariable",
    "dependsOn": [
    {
    "activity": "Call the API to the get the total pages",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "userProperties": [],
    "typeProperties": {
    "variableName": "maxpage",
    "value": {
    "value": "@string(activity('Web1').output.total_pages)",
    "type": "Expression"
    }
    }
    },
    {
    "name": "Loop all the pages",
    "type": "Until",
    "dependsOn": [
    {
    "activity": "Get The total page",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "userProperties": [],
    "typeProperties": {
    "expression": {
    "value": "@equals(variables('counter') , variables('maxpage') )",
    "type": "Expression"
    },
    "activities": [
    {
    "name": "Add to the tempcounter",
    "type": "SetVariable",
    "dependsOn": [],
    "userProperties": [],
    "typeProperties": {
    "variableName": "tempcounter",
    "value": {
    "value": "@string(add(int(variables('counter')),1))",
    "type": "Expression"
    }
    }
    },
    {
    "name": "Swap the counter",
    "type": "SetVariable",
    "dependsOn": [
    {
    "activity": "Add to the tempcounter",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "userProperties": [],
    "typeProperties": {
    "variableName": "counter",
    "value": {
    "value": "@variables('tempcounter')",
    "type": "Expression"
    }
    }
    },
    {
    "name": "Copy Data2",
    "type": "Copy",
    "dependsOn": [
    {
    "activity": "Swap the counter",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "policy": {
    "timeout": "7.00:00:00",
    "retry": 0,
    "retryIntervalInSeconds": 30,
    "secureOutput": false,
    "secureInput": false
    },
    "userProperties": [],
    "typeProperties": {
    "source": {
    "type": "RestSource",
    "httpRequestTimeout": "00:01:40",
    "requestInterval": "00.00:00:00.010",
    "requestMethod": "GET"
    },
    "sink": {
    "type": "JsonSink",
    "storeSettings": {
    "type": "AzureBlobStorageWriteSettings"
    },
    "formatSettings": {
    "type": "JsonWriteSettings",
    "quoteAllText": true
    }
    },
    "enableStaging": false
    },
    "inputs": [
    {
    "referenceName": "RestResource1",
    "type": "DatasetReference",
    "parameters": {
    "page": {
    "value": "@variables('counter')",
    "type": "Expression"
    }
    }
    }
    ],
    "outputs": [
    {
    "referenceName": "Json1",
    "type": "DatasetReference"
    }
    ]
    }
    ],
    "timeout": "7.00:00:00"
    }
    }
    ],
    "variables": {
    "maxpage": {
    "type": "String",
    "defaultValue": "1"
    },
    "counter": {
    "type": "String",
    "defaultValue": "0"
    },
    "tempcounter": {
    "type": "String",
    "defaultValue": "0"
    }
    },
    "annotations": []
    }
    }

    Please do let me know how it goes . I will was able to iterate the pages and push data on a blob .


    Thanks Himanshu

    Wednesday, September 25, 2019 1:27 AM