Rest API recursive get Request in Azure synapse pipeline

Question

Rest API recursive get Request in Azure synapse pipeline

Bhoopathiraju Deepthi 66

I need to copy the data from a rest API end point using azure synapse. By default the response limit is set to 1000. i need to make a recursive get request using one of the response header value in the request until all the response is copied.Any suggestions how to do that please?

Bhoopathiraju Deepthi 66 Reputation points

2021-07-29T10:45:37.633+00:00

Below is the pipeline I have where I am able to fetch the response header in a variable. In order to get all the response output , I need to use the value from that variable and loop the copy activity until all the response from JSON file is returned.
MartinJaffer-MSFT 26,161 Reputation points

2021-07-30T04:30:29.82+00:00
Hello @Bhoopathiraju Deepthi and welcome to Microsoft Q&A.

I see you are trying to do a manual form of pagination (as opposed to the pagination built into the REST dataset).

It looks like you have most parts set up, feting the authentication token, and the initial conditions. What is missing, is a loop.

Depending upon how the pagination works, there are a few implementations to choose from.

If the pagination tells you the total number of records, and the number of records per page, then we can make use of a forEach loop without sequential. This works by using the range() function to generate a series of page numbers or record offsets, and use this series as the items for the loop to iterate over. You may need to store the series in an array type variable.

If you do not know the total amount of pages, and the results only tell you whether there is another page after the current, then we should use an until/while loop. You may have run into the issue where a set variable activity cannot self-reference ( X = X + 1). There is a trick to getting around this, but can only be used in sequential situations. A bit of shuffling.

Y = X

X = Y + 1

Let me know which case is applicable to you, and I can share more details and pictures.
Bhoopathiraju Deepthi 66 Reputation points

2021-07-30T05:07:27.123+00:00

Hi @MartinJaffer-MSFT , Thanks for your inputs.

My scenario is :

In the get response I don't see total pages or results per page value. But it is giving only count of total items and but then just giving 500-1000 items while copying. Not sure how to store the number of items copied to loop in.

Also FYI, below is the condition on the API endpoint in order to loop in and how the response will be returned using one of the object token value to continue the get request.

I am fetching that specific object token value in the variable from web activity but not sure how to use that loop the request.
Also here are the objects my body and response header having.
MartinJaffer-MSFT 26,161 Reputation points

2021-07-30T22:08:40.847+00:00
Ah, Thank you for the very detailed response and definitions @Bhoopathiraju Deepthi . That was most enlightening. From this I understand:

The x-showpad-scroll-id may or may not change with each call. It has nothing to do with which page you are on. The paging is kept server-side.

As the paging is kept server-side, we only get to reach each page once.

We can control the maximum number of records in each request / page.

The combination of not sending a page-specific identifier, knowing the total number of records, and having control over the records per request, allows for some unique opportunities.

Both sequential, and parallel methods will work for this one. Implementation details to follow.
Bhoopathiraju Deepthi 66 Reputation points

2021-07-31T16:42:10.733+00:00

Hi @MartinJaffer-MSFT

We can control the maximum number of records in each request / page. - How to do that ?

The combination of not sending a page-specific identifier, knowing the total number of records, and having control over the records per request, allows for some unique opportunities. --How can I get how many records are still left and continue the request until the end?

Both sequential, and parallel methods will work for this one. Implementation details to follow-- You mean any specific logic or activity we have to get that ?
MartinJaffer-MSFT 26,161 Reputation points

2021-08-02T20:31:25.227+00:00

Sorry my response didn't come earlier. It seems my message got eaten.

Also, I realized something I overlooked. You want to copy the data, not just fetch it using Web activity. The difference this makes, is that in web activity we can look at the response directly, whereas most of the Copy connectors do not let you make decisions as the data comes. This is important because the details you shared simply say when there is no more records, an empty response is returned. No other indicator of completion.

I think we can make this work with REST connector.

In the pagination section of the copy activity with REST as source,

Name: Headers.x-showpad-scroll-id
Value: Headers.x-showpad-scroll-id

This should work if the header is empty when there are no more records.

If the headers is not empty when there are no more records, then we need to get creative. I thought of adding a bogus request header whose value is part of the first result data. This way, when there is no result to get from, the next request header can't be filled and ADF stops.

Name: Headers.bogus
Value: $.response.items[0].myProperty
(replace myProperty with the name of some piece of data)

Answer accepted by question author

1 additional answer

Your answer

Bhoopathiraju Deepthi 66 Reputation points

2021-07-29T10:45:37.633+00:00

Below is the pipeline I have where I am able to fetch the response header in a variable. In order to get all the response output , I need to use the value from that variable and loop the copy activity until all the response from JSON file is returned.
MartinJaffer-MSFT 26,161 Reputation points

2021-07-30T04:30:29.82+00:00

Hello @Bhoopathiraju Deepthi and welcome to Microsoft Q&A.

I see you are trying to do a manual form of pagination (as opposed to the pagination built into the REST dataset).

It looks like you have most parts set up, feting the authentication token, and the initial conditions. What is missing, is a loop.

Depending upon how the pagination works, there are a few implementations to choose from.

If the pagination tells you the total number of records, and the number of records per page, then we can make use of a forEach loop without sequential. This works by using the range() function to generate a series of page numbers or record offsets, and use this series as the items for the loop to iterate over. You may need to store the series in an array type variable.

If you do not know the total amount of pages, and the results only tell you whether there is another page after the current, then we should use an until/while loop. You may have run into the issue where a set variable activity cannot self-reference ( X = X + 1). There is a trick to getting around this, but can only be used in sequential situations. A bit of shuffling.

Y = X

X = Y + 1

Let me know which case is applicable to you, and I can share more details and pictures.
Bhoopathiraju Deepthi 66 Reputation points

2021-07-30T05:07:27.123+00:00

Hi @MartinJaffer-MSFT , Thanks for your inputs.

My scenario is :

In the get response I don't see total pages or results per page value. But it is giving only count of total items and but then just giving 500-1000 items while copying. Not sure how to store the number of items copied to loop in.

Also FYI, below is the condition on the API endpoint in order to loop in and how the response will be returned using one of the object token value to continue the get request.

I am fetching that specific object token value in the variable from web activity but not sure how to use that loop the request.
Also here are the objects my body and response header having.
MartinJaffer-MSFT 26,161 Reputation points

2021-07-30T22:08:40.847+00:00

Ah, Thank you for the very detailed response and definitions @Bhoopathiraju Deepthi . That was most enlightening. From this I understand:

The x-showpad-scroll-id may or may not change with each call. It has nothing to do with which page you are on. The paging is kept server-side.

As the paging is kept server-side, we only get to reach each page once.

We can control the maximum number of records in each request / page.

The combination of not sending a page-specific identifier, knowing the total number of records, and having control over the records per request, allows for some unique opportunities.

Both sequential, and parallel methods will work for this one. Implementation details to follow.
Bhoopathiraju Deepthi 66 Reputation points

2021-07-31T16:42:10.733+00:00

Hi @MartinJaffer-MSFT

We can control the maximum number of records in each request / page. - How to do that ?

The combination of not sending a page-specific identifier, knowing the total number of records, and having control over the records per request, allows for some unique opportunities. --How can I get how many records are still left and continue the request until the end?

Both sequential, and parallel methods will work for this one. Implementation details to follow-- You mean any specific logic or activity we have to get that ?
MartinJaffer-MSFT 26,161 Reputation points

2021-08-02T20:31:25.227+00:00

Sorry my response didn't come earlier. It seems my message got eaten.

Also, I realized something I overlooked. You want to copy the data, not just fetch it using Web activity. The difference this makes, is that in web activity we can look at the response directly, whereas most of the Copy connectors do not let you make decisions as the data comes. This is important because the details you shared simply say when there is no more records, an empty response is returned. No other indicator of completion.

I think we can make this work with REST connector.

In the pagination section of the copy activity with REST as source,

Name: Headers.x-showpad-scroll-id
Value: Headers.x-showpad-scroll-id

This should work if the header is empty when there are no more records.

If the headers is not empty when there are no more records, then we need to get creative. I thought of adding a bogus request header whose value is part of the first result data. This way, when there is no result to get from, the next request header can't be filled and ADF stops.

Name: Headers.bogus
Value: $.response.items[0].myProperty
(replace myProperty with the name of some piece of data)

Answer 1

MartinJaffer-MSFT 26,161

To calculate how many pages, and generate an array to iterate over:

Variables:

In your case, totalrecords would get replaced by the count from the web activity you showed me.
@{activity('web1').output.response.count}
The perpage would be 500, as that is the default in documentation. Or whatever you choose it to be.

Make page array:

@Rover (0,div(int(variables('totalrecords')),int(variables('perpage'))))
number of pages = totalrecords / perpage. This makes that many elements in an array.

Iterate over pagearray to keep track of how many times:

ShaikMaheer-MSFT 38,636 Reputation points Microsoft Employee Moderator

2021-08-24T07:20:04.163+00:00

Hi @Bhoopathiraju Deepthi ,

We didn't hear back. Is above solution works for you? Kindly share updates or any concerns. Thank you.
Bhoopathiraju Deepthi 66 Reputation points

2021-08-24T08:15:03.597+00:00

Hi @ShaikMaheer-MSFT , I am trying to implement it.. will update you soon.

Thank You!
ShaikMaheer-MSFT 38,636 Reputation points Microsoft Employee Moderator

2021-08-27T06:06:44.817+00:00

Hi @Bhoopathiraju Deepthi ,

Is above approach helps you? If yes, could you please confirm? So that we can make that as answer. Thank you.
ShaikMaheer-MSFT 38,636 Reputation points Microsoft Employee Moderator

2021-09-01T11:31:36.097+00:00

Hi @Bhoopathiraju Deepthi ,

Just checking, Is above approach helps you? If yes, could you please confirm? So that we can make that as answer. Thank you.
Bhoopathiraju Deepthi 66 Reputation points

2021-09-02T05:40:57.573+00:00

HI @ShaikMaheer-MSFT

thanks for checking on this, it worked. I am just trying with different use cases and testing so couldn't confirm.

Thanks for detailed explanation and steps.
ShaikMaheer-MSFT 38,636 Reputation points Microsoft Employee Moderator

2021-09-02T07:14:11.093+00:00

Hi @Bhoopathiraju Deepthi ,

Thank you for confirming that above approach worked for you. Could you please mark is as Accepted Answer. Accepted answers helps community as well. Thank you.

Answer 2

Ziani_Az 21

Hi @ShaikMaheer-MSFT ,
How create and update statistics in Azure Synapse Analytics,
Thank you

0 comments

Share via

Rest API recursive get Request in Azure synapse pipeline

1 additional answer

Your answer