How does the offset pagination work in the API Query Profile?

Matillion ETL
1

I am successfully making api calls and the data is being inserted into snowflake. However, the pagination does not appear to be working. I have the limit set to 5000. 4999 records are inserted each time, but it is not continuing on to the rest of the pages that the API is responding with.

 

Is there something I must do in the Matillion job itself? Do i need to create parameters for the offset in the Matillion job? Do I need to use a loop iterator over the API Query component?

 

Thanks in advance for your help,

Wayne

2

Do you have docs for the api your connecting to?

3

Is there any response to this thread please. I'm also facing the same issue. Not sure how the pagination works for Offset method. I've an API which provides totalrecordcount and the offset method works different like below

Say for E.g. for the given filter condition API renders 100 records and I set LIMIT as 50 then I need to hit the API twice with following OFFSET and LIMIT as below

TO pull first 50 records --> OFFSET=0&LIMIT=50

To pull next 50 records --> OFFSET=1&LIMIT=50

In all these two API calls the totalrecordcount will be rendered as 100. So I tried editing the API profile in Advanced mode and tried changing the HTTP request on below block highlighted striked line to check if this is working but still its fetching only the first 50 records and exiting. Not sure If I'm following appropriate steps to iterate over the API call for offset methon on my use case. Can someone help me with this pls.@MatillionProductTeam​

<api:script method="GET">

<api:set attr="method" value="GET"/> <!-- HTTP request type -->

<api:set attr="method" value="GET"/>

<api:set attr="nextOffset" value="[_input.rows@next | add([limit])]" />

<api:set attr="nextOffset" value="[_input.rows@next | add(1)]" />

<api:check attr="totalCount">

<api:check value="[totalCount | isgreater([nextOffset])]">

<api:set attr="rows@next" value="[nextOffset]" />

</api:check>

</api:check>

<api:call op="jsonproviderGet">

4

Finally figured out an workaround on this. Seems like Matillion API Query RSD profile is not formatted in appropriate way to loop through the pagination. I've made a small tweak in RSD profile by placing the <api:call op="jsonproviderGet"> tag ahead of calculation for offset value it worked. Below highlighted line was framed CHECK conditions and I've moved this before and it worked. Not sure if this is appropriate but I've got it worked for my case.

@wayne.richard​ , Please have a check on this and try to edit the API Query profile in Advanced mode for making the below changes. Hope this helps.

@MatillionProductTeam​ , Please confirm if this workaround is fine.

<api:script method="GET">

<api:set attr="method" value="GET"/> <!-- HTTP request type -->

<api:call op="jsonproviderGet">

<api:set attr="nextOffset" value="[_input.rows@next | add([limit])]" />

<api:check attr="totalCount">

<api:check value="[totalCount | isgreater([nextOffset])]">

<api:set attr="rows@next" value="[nextOffset]" />

</api:check>

</api:check>

<api:push/>

</api:call>a

</api:script>

</api:script>