Solved

Using a nested REST API in combination with paging

2 years ago
February 15, 2023
17 replies
1086 views

jacqueline.hofmeijer
Contributor
10 replies

Hi,

I am having some trouble in using the nested REST API in combination with paging. The extensive description on how to use the nested call in the RSD-file was very helpfull. The only thing I cannot get working is the paging at the first level.

Two different cases, but I think the same underlying issue. In the first case the pageoffset is used as a parameter, in the other one it uses a pagetoken (the nextmarker in a blobstorage). In the output, I do see that paging is applied, but the problem is that it starts at the first page again. I seems that when using the input- and output files the correct information about the next page is not being picked up by the api-call.

I have tried the call without nesting (so only on the first level) and without qualifiying the parameters and then the paging is working correctly, but when I switch to in and out again, I get false results.

Does anybody recognize this isse and solved it somehow?

Kind regards,

Jacqueline Hofmeijer

Best answer by jacqueline.hofmeijer

Hi @Thomas Lind,

For now I have worked around the issue by using the incremental loading but accept the limitations of the paging option. I have just created extra execution steps to load the data multiple times, but this way I won't be sure the data is fully loaded.

So new suggestions are very welcome.

Kind regards,

Jacqueline

View original

Did this topic help you find an answer to your question?

Thomas Lind
Community Manager
1017 replies
2 years ago
February 16, 2023

Hi Jacqueline

We had something regarding this @Jan Stroeve may know about it.

In essence this is how they did it. They added the two rows along with the pagination option.

<api:set attr="EnablePaging" value="true" />
<api:set attr="pagetokenpath" value="/json/pagination/cursor" />
<api:set attr="pagetokenparam" value="cursor" />

Do you have the field with the token as a field in the response, or is it in a header value?

jacqueline.hofmeijer
Author
Contributor
10 replies
2 years ago
February 16, 2023

Hi Thomas,

I am also using these parameters, for the blob storage it is defined like this:

<api:set attr="EnablePaging" value="TRUE" />
<api:set attr="pagetokenpath" value="/EnumerationResults/NextMarker" />
<api:set attr="pagetokenparam" value="marker" />
The value NextMarker is returned as a field in the response body.

When defined like this it is working fine, and I do not have to script anything to have the paging working.

I get problems with this when I set it up like this:

<api:set attr="filein.EnablePaging" value="TRUE" />
<api:set attr="filein.pagetokenpath" value="/EnumerationResults/NextMarker" />
<api:set attr="filein.pagetokenparam" value="marker" />

…

<api:script method="GET">
<api:set attr="method" value="GET"/>
<api:call op="xmlproviderGet" in="filein" out="fileout">
<api:push"/>
</api:call>
</api:script>

Thomas Lind
Community Manager
1017 replies
2 years ago
February 16, 2023

Hi Jacqueline

That was also the issue for Jan.
What CData suggested was this.

Create two individual RSD files one that is doing the nesting call like this:

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->
  <api:info title="detailedInfo" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1" other:queryslicercolumn="nested_id">
    <attr name="ID"        xs:type="string"   readonly="false"                            other:xPath="/json/data/name"      />
    <!-- You can modify the name, type, and column size here. -->
    <attr name="nested_id" xs:type="string"   readonly="false" other:filter="{nested_id}" other:xPath="/json/data/nested_id" />
    <attr name="Name"      xs:type="string"   readonly="false"                            other:xPath="/json/data/name"      />
    <attr name="Date"      xs:type="datetime" readonly="false"                            other:xPath="/json/data/date"      />
    <attr name="Number"    xs:type="decimal"  readonly="false"                            other:xPath="/json/data/number"    />
  </api:info>

  <api:set attr="DataModel" value="DOCUMENT" />
  <api:set attr="URI" value="https://api.rest.com/v2/detailedInfo?nested={nested_id}" />
  <api:match pattern="*nested_id*" type="glob" value="[_query.criteria]">
    <api:else>
      <api:set attr="URI" value="https://api.rest.com/v2/detailedInfo?nested=001" />
    </api:else>
  </api:match>
  <api:set attr="JSONPath" value="$.data" />

  <api:set attr="EnablePaging"   value="TRUE"                           />
  <api:set attr="pagetokenpath"  value="/EnumerationResults/NextMarker" />
  <api:set attr="pagetokenparam" value="marker"                         />

  <!-- The GET method corresponds to SELECT. Here you can override the default processing of the SELECT statement. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
  <api:script method="GET">
    <api:set attr="method" value="GET"/>
    <api:call op="jsonproviderGet">
      <api:push/>
    </api:call>
  </api:script>

</api:script>

And one that has a list of the nested values could look like this.

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->
  <api:info title="values" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">
    <attr name="id"     xs:type="string" readonly="false" other:xPath="/json/data/id"     />
    <attr name="name"   xs:type="string" readonly="false" other:xPath="/json/data/name"   />
    <attr name="status" xs:type="string" readonly="false" other:xPath="/json/data/status" />
  </api:info>

  <api:set attr="DataModel" value="DOCUMENT" />
  <api:set attr="URI" value="https://api.rest.com/v2/values" />
  <api:set attr="JSONPath" value="$.data" />

  <api:set attr="EnablePaging"   value="TRUE"                           />
  <api:set attr="pagetokenpath"  value="/EnumerationResults/NextMarker" />
  <api:set attr="pagetokenparam" value="marker"                         />

  <!-- The GET method corresponds to SELECT. Here you can override the default processing of the SELECT statement. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
  <api:script method="GET">
    <api:set attr="method" value="GET"/>
    <api:call op="jsonproviderGet">
      <api:push/>
    </api:call>
  </api:script>

</api:script>

Then you have a REST data source that looks at a folder where these two RSD files are located and you create a Query table to do the nesting with. Like so.

SELECT ID, Nested_Id, Name, Date, Number FROM REST.detailedInfo
WHERE Nested_Id IN (SELECT ID FROM REST.values)

This should make it work despite having to do a nested call alongside this method.

Notice that I use a Query Slicer column and a nested field to make it able to be used for this.

jacqueline.hofmeijer
Author
Contributor
10 replies
2 years ago
February 16, 2023

Okay, hoped that it would be a bit more straightforward, but I will give it a try in my rsd-files.

Thomas Lind
Community Manager
1017 replies
2 years ago
February 16, 2023

Well they also provided some message about that.

If the customer absolutely needs the logic in one RSD file (i.e. so they can run a simple unfiltered SELECT *), then that will require manual logic using rows@next. In that case, please let me know and I can try to customize the RSD file accordingly.

So I think that may be more difficult actually.

Let me know if you get stuck.

jacqueline.hofmeijer
Author
Contributor
10 replies
2 years ago
February 20, 2023

I have tried to get this method working in my rsd-files but unfortunately it has no result in both my cases. Could it be that it has to do with the fact that the variables are part of the URI and not just parameters on the query side of the request?

Also not sure if I understand how to use the “nested_id” in the script. When executing the datasource that is using a json connection the log is showing the right (nested) api-call of that specific line, but the data is just not transferred to a table in the sql-database.

The other datasource is using a csv-provider and in that case the parameter remain NULL and thus giving a error code.

Regarding the rows@next, I had already tried that method but then I still have the same issue that the mechanism of filein/fileout does not seem to be able to transfer the right values.

Thomas Lind
Community Manager
1017 replies
2 years ago
February 21, 2023

Hi Jacqueline

We should maybe do a session where you show me how far you are and what issues you get. I may have some ideas and if not I can gather as much necessary data to ask for help with this.

When would it fit you?

I am available most days from 09-15 UTC +01:00

jacqueline.hofmeijer
Author
Contributor
10 replies
2 years ago
February 21, 2023

Hi Thomas,

That would be great. Tomorrow or Thursday around 11:00?

Thomas Lind
Community Manager
1017 replies
2 years ago
February 22, 2023

Hi Jacqueline

I have sent an invite.

Christian Hauggaard
Community Manager
1063 replies
2 years ago
March 8, 2023

Hi @jacqueline.hofmeijer did you manage to resolve the issue? If so we would very much appreciate it if you could please describe the solution. If not, please let us know if we can help further

gijs
Starter
1 reply
1 year ago
March 22, 2023

Hi, I've had a similar use case when calling a nested API call in a different ETL-tool. The following rsd script first reads Azure AD groups via the Graph API and then retrieves the group members via a nested Graph API call. Hopefully this example will help you.

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <api:info title="Members" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">
    <!-- You can modify the name, type, and column size here.  -->
    <attr name="GroupId"           xs:type="string"  readonly="false" other:xPath="groupId"            />
    <attr name="GroupDisplayName"  xs:type="string"  readonly="false" other:xPath="groupDisplayName"   />
    <attr name="GroupDescription"  xs:type="string"  readonly="false" other:xPath="groupDescription"   />
    <attr name="UserId"            xs:type="string"  readonly="false" other:xPath="userId"             />
    <attr name="UserDisplayName"   xs:type="string"  readonly="false" other:xPath="userDisplayName"    />
    <attr name="UserPrincipalName" xs:type="string"  readonly="false" other:xPath="userPrincipalName"  />
    <!-- Attributes are used for passing to child API call -->
    <attr name="Id"                xs:type="string"  readonly="false" other:xPath="id"                 />
    <attr name="DisplayName"       xs:type="string"  readonly="false" other:xPath="displayName"        />
    <attr name="Description"       xs:type="string"  readonly="false" other:xPath="description"        />
  </api:info>

  <api:set attr="groupin.DataModel" value="DOCUMENT" />
  <api:set attr="groupin.JSONPath" value="/value" />
  <api:set attr="groupin.URITemplate" value="https://graph.microsoft.com/v1.0/groups?$select=id,displayName,description" />
  <api:set attr="groupin.ElementMapPath#" value="/value/id" />
  <api:set attr="groupin.ElementMapName#" value="group_id" />
  <api:set attr="groupin.EnablePaging" value="TRUE" />
  <api:set attr="memberin.DataModel" value="DOCUMENT" />
  <api:set attr="memberin.EnablePaging" value="TRUE" />
  <api:set attr="memberin.JSONPath" value="/value" />
  <api:set attr="memberin.URITemplate" value="https://graph.microsoft.com/v1.0/groups/{groupId}/members?$select=id,displayName,userPrincipalName" />

  <api:script method="GET">
    <api:set attr="groupin.URI" value="[groupin.URITemplate]" />
    <!-- Get the groups -->
    <api:call op="jsonproviderGet" in="groupin" out="groupout">
      <api:set attr="memberin.groupId" value="[groupout.id]" />
      <api:set attr="memberin.groupDisplayName" value="[groupout.displayName | allownull()]" />
      <api:set attr="memberin.groupDescription" value="[groupout.description | allownull()]" />
      <api:set attr="memberin.URI" value="[memberin.URITemplate | replace('{groupId}', [memberin.groupId])]" />
      <!-- Get the members of the group -->
      <api:call op="jsonproviderGet" in="memberin" out="memberout">
        <api:set attr="out.userId" value="[memberout.id]" />
        <api:set attr="out.userDisplayName" value="[memberout.displayName]" />
        <api:set attr="out.userPrincipalName" value="[memberout.userPrincipalName]" />
        <api:set attr="out.groupId" value="[memberin.groupId | allownull()]" />
        <api:set attr="out.groupDisplayName" value="[memberin.groupDisplayName | allownull()]" />
        <api:set attr="out.groupDescription" value="[memberin.groupDescription | allownull()]" />
        <api:push item="out"/>
      </api:call>
    </api:call>
  </api:script>
</api:script>

Thomas Lind
Community Manager
1017 replies
1 year ago
March 22, 2023

gijs wrote:

Hi, I've had a similar use case when calling a nested API call in a different ETL-tool. The following rsd script first reads Azure AD groups via the Graph API and then retrieves the group members via a nested Graph API call. Hopefully this example will help you.

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <api:info title="Members" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">
    <!-- You can modify the name, type, and column size here.  -->
    <attr name="GroupId"           xs:type="string"  readonly="false" other:xPath="groupId"            />
    <attr name="GroupDisplayName"  xs:type="string"  readonly="false" other:xPath="groupDisplayName"   />
    <attr name="GroupDescription"  xs:type="string"  readonly="false" other:xPath="groupDescription"   />
    <attr name="UserId"            xs:type="string"  readonly="false" other:xPath="userId"             />
    <attr name="UserDisplayName"   xs:type="string"  readonly="false" other:xPath="userDisplayName"    />
    <attr name="UserPrincipalName" xs:type="string"  readonly="false" other:xPath="userPrincipalName"  />
    <!-- Attributes are used for passing to child API call -->
    <attr name="Id"                xs:type="string"  readonly="false" other:xPath="id"                 />
    <attr name="DisplayName"       xs:type="string"  readonly="false" other:xPath="displayName"        />
    <attr name="Description"       xs:type="string"  readonly="false" other:xPath="description"        />
  </api:info>

  <api:set attr="groupin.DataModel" value="DOCUMENT" />
  <api:set attr="groupin.JSONPath" value="/value" />
  <api:set attr="groupin.URITemplate" value="https://graph.microsoft.com/v1.0/groups?$select=id,displayName,description" />
  <api:set attr="groupin.ElementMapPath#" value="/value/id" />
  <api:set attr="groupin.ElementMapName#" value="group_id" />
  <api:set attr="groupin.EnablePaging" value="TRUE" />
  <api:set attr="memberin.DataModel" value="DOCUMENT" />
  <api:set attr="memberin.EnablePaging" value="TRUE" />
  <api:set attr="memberin.JSONPath" value="/value" />
  <api:set attr="memberin.URITemplate" value="https://graph.microsoft.com/v1.0/groups/{groupId}/members?$select=id,displayName,userPrincipalName" />

  <api:script method="GET">
    <api:set attr="groupin.URI" value="[groupin.URITemplate]" />
    <!-- Get the groups -->
    <api:call op="jsonproviderGet" in="groupin" out="groupout">
      <api:set attr="memberin.groupId" value="[groupout.id]" />
      <api:set attr="memberin.groupDisplayName" value="[groupout.displayName | allownull()]" />
      <api:set attr="memberin.groupDescription" value="[groupout.description | allownull()]" />
      <api:set attr="memberin.URI" value="[memberin.URITemplate | replace('{groupId}', [memberin.groupId])]" />
      <!-- Get the members of the group -->
      <api:call op="jsonproviderGet" in="memberin" out="memberout">
        <api:set attr="out.userId" value="[memberout.id]" />
        <api:set attr="out.userDisplayName" value="[memberout.displayName]" />
        <api:set attr="out.userPrincipalName" value="[memberout.userPrincipalName]" />
        <api:set attr="out.groupId" value="[memberin.groupId | allownull()]" />
        <api:set attr="out.groupDisplayName" value="[memberin.groupDisplayName | allownull()]" />
        <api:set attr="out.groupDescription" value="[memberin.groupDescription | allownull()]" />
        <api:push item="out"/>
      </api:call>
    </api:call>
  </api:script>
</api:script>

@gijs
Nice one, I use it as well.

jacqueline.hofmeijer
Author
Contributor
10 replies
1 year ago
March 24, 2023

Hi @gijs,

Thanks for your reaction but unfortunately that does not solve my problem.

I already got some connections working in the way you described, but the challenge for me lies in the combination of:

nested calls
paging
incremental loading.

The option Thomas provided in using a query slicer does work for using nested call in combination with paging (after a bit of customizing) but not for the incremental loading at top level. And I need this for performance issues.

Thomas Lind
Community Manager
1017 replies
1 year ago
October 17, 2023

Hi @jacqueline.hofmeijer

Have you resolved this issue currently?

Otherwise I may have some new suggestions for you to try.

jacqueline.hofmeijer
Author
Contributor
10 replies
Answer
1 year ago
October 24, 2023

Hi @Thomas Lind,

So new suggestions are very welcome.

Kind regards,

Jacqueline

Thomas Lind
Community Manager
1017 replies
1 year ago
October 24, 2023

Can you send the files to me and how the incremental procedure currently looks?

Don’t add them here unless they are without private data.

Christian Hauggaard
Community Manager
1063 replies
1 year ago
November 7, 2023

Hi @jacqueline.hofmeijer do you have an update for Thomas’ questions above? Thanks!

Reply

Rich Text Editor, editor1

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

Cookie settings

We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.

Basic
Functional

Normal
Functional + analytics

Complete
Functional + analytics + social media + embedded videos

Reply

Related topics

Notified if Execution Package does not run on Scheduleicon

TimeXtender 6429.1

How to define schedules for each environmenticon

Multiple CSV files with History (19.11.7.64)icon

Scheduling Execution Packages in TimeXtender Orchestration

Most helpful members this week

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

Cookie policy

Cookie settings