Azure Data Factory2's pagination rule (for Rest API)

Question

I am trying to get more than 500 rows of data using Rest API, and I am not sure how to express properly using Azure Data Factory 2.

From my understanding, I need to put something here:
Bottom screenshot is just an example from other post.

This is URL of Rest API:
https://data.cms.gov/provider-data/api/1/datastore/sql?query=%5BSELECT%20%2A%20FROM%203497679f-5984-584e-a7cd-2f8641e5e0f0%5D%3B

I am not sure what to put inside the Value box.
I have total 15,315 rows of data, and I know Azure Data Factory version 2 came out with Pagination rule, so I am trying to utilize it.

What is a proper approach to get the whole 15,315 rows of data?

Also, I am trying to insert into Azure database directly instead of downloading into csv file. Is it possible with the outcome of the Rest API link?
https://data.cms.gov/provider-data/api/1/datastore/sql?query=%5BSELECT%20%2A%20FROM%203497679f-5984-584e-a7cd-2f8641e5e0f0%5D%3B

The reason I am asking this is I am not sure whether this is JSON format and it is better to download into csv file first and map it or if there is a way to directly import into Azure SQL database.

Thanks for help

Answer 1

Hi @JustinDoh-1889,

Ah, I see a difference in the query formation in dynamic expression:

I see that you have used this expression which is causing the issue : @concat('datastore/sql?query=%5BSELECT%20%2A%20FROM%203497679f-5984-584e-a7cd-2f8641e5e0f0%5D%5BLIMIT%20*500%20OFFSET%20',dataset().offset,'%5D')

Could you please try this and let us know how it goes: @concat('datastore/sql?query=%5BSELECT%20%2A%20FROM%203497679f-5984-584e-a7cd-2f8641e5e0f0%5D%5BLIMIT%20500%20OFFSET%20',dataset().offset,'%5D')

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Hi @JustinDoh-1889,

Apologies for the delayed response and thank you so much for the feedback regarding the GIF presentation by @HimanshuSinha-msft .

• "offest" instead of "offset" --> This seems to be a typo, not intentional :)
• As per the error message provided, it seems like the value of @item() is of string type, but the function mul expects parameters of ineger/number/decimal type which is why you are receiving this issue. To overcome this issue, you can use a conversion function int('<value>') which would help resolve this.
• You expression should look like offset = @mul(int(item()), 500). If this throws any error please try this offset = @string(mul(int(item()), 500))

Hope this helps. Please do let us know if you still face any issues or have further queries.

We look forward to your confirmation.

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

Answer 3

Hello @JustinDoh-1889 ,
Thanks for the ask and using the Microsoft Q&A platform .

The paging logic is different from ine API provider then other . In your case they are using the offset in lieu of the paging . Please read about this here . So what this means is that you will have to use a foreach loop and then read the data .

updated on 7/14

We can implement this using a Foreach Loop and and setting the iteration of 31 , as shown below .

The next intend to do pass the offset dynamically to the below query , please pay special attention to the dataset.offest ( I am paramterizing the same so the for each iteration value changes .

@markus.bohland@hotmail.de ('datastore/sql?query=%5BSELECT%20%2A%20FROM%203497679f-5984-584e-a7cd-2f8641e5e0f0%5D%5BLIMIT%20500%20OFFSET%20',dataset().offest,'%5D')

The above update goes in the Source of the copy activity which is inside the FE loop .

Since the source dataset is pamaterized it looks something like

On the Sink side I have tested with the SQL Azure and I was able tio see 15,000 + records .

The below should also help you with the implementation .

Please do let me know how it goes .
Thanks
Himanshu
Please do consider clicking on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Answer 4

@HimanshuSinha-msft (or other followers)
First of all, thank you so much for your in-depth help on this issue. I was very impressed with the gif (?) file with all processes you have illustrated. Very nice of you!

I was trying to replicate with animated images you provided, but somehow, I am getting an error. I think I spent at least three hours to figure out this issue, but I could not resolve the issue. I noticed one thing though. It appears that you used "offest" instead of "offset". I am not sure if that was intentional or not.

So, I am getting an error, and that is where it puts a "Value" for "Additional expressions".
If you/one can suggest a solution, please let me know so I could try again. Thanks!

Base URL: https://data.cms.gov/provider-data/api/1/
Relative URL or Add dynamic content:
@markus.bohland@hotmail.de ('datastore/sql?query=%5BSELECT%20%2A%20FROM%203497679f-5984-584e-a7cd-2f8641e5e0f0%5D%5BLIMIT%20*500%20OFFSET%20',dataset().offset,'%5D')

Here are screenshots: