Rest API URL with offset & limit how to build pipeline fetching all data without duplication & all in one file

Question

Rest API URL with offset & limit how to build pipeline fetching all data without duplication & all in one file

Shreyash Choudhary 126

Hi all.
i have a scenario i have api url with offset & limit ,need to fetch all data in one single file without duplication, limit reamins same need to increase offset like 0 offset & limit 10 ,11 offset & limit 10,21 offset & limit 10 and so on,

there is no end condition in api as far i know how to build pipleline for this and how to increase offset and how to stop when api dont have data ,

i have gone through microsoft support docs but nothing usefull.

Api only send data and no header.

also one issue i noticed is i have a data column coming from rest api , that columns contains "A" or sometimes empty when i fetching data this column is not coming so i need to manualy map it in mapping, this is the problem /issue i faced

Accepted answer

2 additional answers

Your answer

Answer 1

MartinJaffer-MSFT 26,236

Hello @Shreyash Choudhary ,
Thanks for the question and using MS Q&A platform.

As I understand, you need to use Copy Activity REST API connector, and want help with pagination. Your situation sounds just like this example, where each requests increments on fixed amount, and the stopping number is not known.

My concern is an off-by-one error. By your example,

offset | records retrieved  
0 | 0,1,2,3,4,5,6,7,8,9,10 -- 11 records  
11 | 11,12,13,14,15,16,17,18,19,20 -- 10 records  
21 | 21,22,23,24,25,26,27,28,29,30 -- 10 records

did you mean offset should really start on 1?

also one issue i noticed is i have a data column coming from rest api , that columns contains "A" or sometimes empty when i fetching data this column is not coming so i need to manualy map it in mapping, this is the problem /issue i faced

I don't understand, could you please give a couple examples?

Please do let me if you have any queries.

Thanks
Martin

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
- If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Shreyash Choudhary 126 Reputation points

2022-12-21T06:43:26.183+00:00

Hi, @MartinJaffer-MSFT

what i observed is, api is giving data in json output

[ { "report": [
{
"column1":"value_string",
"column2":"value_string",
"column3":"value_string",
"column4":"value_string",
"column5":"A"
},
{
"column1":"value_string",
"column2":"value_string",
"column3":"value_string",
"column4":"value_string",
"column5":""
},
] } ]

this column 5 with value "A" & empty is sowing in preview when i am doing mapping its not coming there, that's the problem! similar issue is with other column which are EMPTY or NULL , this column are also not coming in mapping so loss of data is happening. my sink is delimited file also tried with sql db same problem , (i have enabled staging in datalake as it requires), can you please help me with this issue.

this is the issue, when column value is empty, in mapping that column is getting avoided, its resulting in loss of data.

Thanks
Shreyash
MartinJaffer-MSFT 26,236 Reputation points

2022-12-21T20:44:58.327+00:00

@Shreyash Choudhary

This behavior is most often seen when the column is missing, that is when "column5" is not present. If "column"5" is present by has empty/null value, it should be coming through. I think.

Since "column5" is not appearing in the automatic import mapping, we can add it manually. Below I have a screenshot of how. To make it relevant to yours, the collection reference should be $[0].report I think. Adding this column should work even if the data is empty string. I'm not sure about null though.

Here is some additional clarification about when the mapping should start with $ and when it shouldn't. In the above image, column1,column2,column3,column4column5, are all udner the collection reference, while stataus,message,Column_1 are not. Below is the same mapping in advanced editor.

Note status,message, and Column_1 start with $. The $ is used to describe the path to the element from the document root. Without the $ the mapper looks anywhere for the column name. This is necessary when a collection reference is involved. Otherwise you get the properties of only a single element from an array.

Answer 2

Hi @MartinJaffer-MSFT ,
thanks for the reply with so many details,

sometime column has value, sometimes coming as null, when i previewed on source side it looks like what i provided above in 1st comment, now my question what to do use advanced editor and $ to put columns manually which are not coming to solve this issue? or something else

when i provided without advanced editor i need to put columns manually and when i run the pipeline column 5 which i given in example which have sometimes small string and sometimes coming empty, that column is coming as full null means data loss.

what should i do now??

Answer 3

Shreyash Choudhary 126

Hi @MartinJaffer-MSFT ,

What if API sends inconsistent data like if some columns don't have values, then Api didn't send that columns & in next records if Api have data means column does not null , in this case when i tried to do manual mapping of columns output file doesn't contain that column data.

Problem is with Inconsistent Api data or anything else, facing data loss due to this!

Thanks & regards
Shreyash Choudhary

MartinJaffer-MSFT 26,236 Reputation points

2023-01-05T16:51:21.42+00:00

Hmm, could try in Mapping Data Flow, as that allows for more schema drift, and some clever expressions could do coalesce to assign a default value it none is found in data.

In terms of just copy activity, I think if we provide no mapping and sink to a file without schema first, everything should be copied as is. Then do another copy from the file to your sql, with a schema assigned to the file dataset, to force picking all values.

@Shreyash Choudhary

Share via

Rest API URL with offset & limit how to build pipeline fetching all data without duplication & all in one file

2 additional answers

Your answer