how collect more than 1000 records useing Post method with REST API on ADF

Question

how collect more than 1000 records useing Post method with REST API on ADF

Kathe, Siddharth S (TCS) 1

I am trying to kusto query data from ADF using Copy Data Activity. for source I am using Rest API connector, which is collecting only 1000 records as page size limit. when I query data using web activity with $skiptoken it is showing next 1000 records. how to consolidate all the records in one csv file.

1 answer

Your answer

Answer 1

ShaikMaheer-MSFT 38,546 Microsoft Employee Moderator

Hi @Kathe, Siddharth S (TCS) ,

Thank you for posting query in Microsoft Q&A Platform.

If I understand correctly, your API supports pagination, and you would like to load all pages data to Sink storage in to one csv file. Please correct me if I am wrong.

Copy activity, REST connector as source will support pagination, you can leverage paginations techniques to query all page's data from API and load to Sink storage.

Below are few useful resources to help with pagination in Copy activity.
How t p Perform Pagination in ADF using REST API - Here it explained about tweaking Sink settings to get all responses in to single file.
Pagination support in Azure data factory
Copy data from REST API which sends response in Pages using Azure data factory

For some reason if you are getting each page data into each file separately then try to use another copy activity on top of folder in which all files available and use wild card option to take all files and under sink use dataset for sink storage to load in to single file.

Hope this helps. Please let me know if any further queries.

Please consider hitting Accept Answer button. Accepted Answers help community as well.

Kathe, Siddharth S (TCS) 1 Reputation point

2022-12-12T10:15:37.427+00:00

Thanks for the replay on my query.
I know the pagination option in Copy activity. but here I am using POST Request method not a GET.
in POST method next page output we get in $skiptoken, until the $skiptoken is null.

Below Kusto query activity I am performing through POST Request method.
https://learn.microsoft.com/en-us/rest/api/azureresourcegraph/resourcegraph(2021-03-01)/resources/resources?tabs=HTTP#code-try-0

And this was already done through powershell script by using "do while", but not sure how to do this in ADF

do {
if ($null -eq $skipToken){
$queryResult = Invoke-RestMethod -Method POST -Uri $uri -Body $VMInventory -headers $authHeader -ContentType "application/json"
$data = $data + $queryResult.data;
}
else{
$VMInventory = @"
{
"query": "resources | where type =~ 'microsoft.compute/virtualMachines'",
"options": {
"`$skipToken": "$skipToken"
}
}
"@
$queryResult = Invoke-RestMethod -Method POST -Uri $uri -Body $VMInventory -headers $authHeader -ContentType "application/json"
$data = $data + $queryResult.data;
}
$skipToken = $queryResult.'$skipToken';
}
while ($null -ne $skipToken);

Share via

how collect more than 1000 records useing Post method with REST API on ADF

1 answer

Your answer