Follow

Connect to a REST API

Before you attempt this you need to add the REST provider to your list of available sources.

Go through this guide before you start.

Add a CData data source

Contents

REST Information

A lot of companies stores data or gives access to data via a REST API. There are two setups in general JSON and XML. How these works can be seen in the JSON and XML cdata guides.

Normally you connect to a web page URI with some link of some sort. Like this https://swapi.co/api/people/, but you can also connect directly to a json/xml file.

CData Setup

Add a CData data source and point to the REST provider.

The first step in setting up a CData connector should always be to click the ? in the top right corner.

Sp_rgsm_lstegn.PNG

REST guide from CData

You have two data types of data.

XML

In general it is recommended to use JSON, but some XML rest providers exists.

The setup is the same, you add the URI or link to an XML file and synchronize. You can remove the access token to the Custom URL Params if you want, but doesn't need to do it.

mceclip2.png

I also used row scan depth = 0 to be sure the whole document gets read.

JSON

Most REST api's use JSON. You can use most of the features also explained in the JSON CData guide.

So here are a list of good online api's to test on.

  • SWAPI - The Star Wars api
  • REQRES - Test your front-end against a real API
  • GoREST - Online FREE REST API
  • dummy - Dummy Rest API example
  • fixer.io - Online Currency data

Most of these have more or less the same setup, but done in different ways.

Here below I have gained access to GoRest. Go to the web page to get the access_token necessary.

Here is the uri i want to use and its contents

mceclip1.png

It gives me 20 rows from page 1 and needs a access token to work.

Here is how I set it up.

mceclip3.png

I added the access token to the Custom URL Params field, and you can add more options, you just need to know the code and set it equal to something, e.g. page=5 you just need to split it with a ; sign.

Getting the data

After this synchronize and you should be able to see the table. If you want to point to other tables in the api, e.g. posts instead of users. You can make it point to both tables.

First change Generate Schema Files to OnUse,

mceclip5.png

then add a location in the schema area,

mceclip6.png

then click OK to close the menu and synchronizes the data source.

This generates a RSD file in the folder location. Open the file and rename the title to users and the file itself to users.

mceclip7.png

Now you go into the data source and changes the URI to point to posts instead of users, clicks on OK to close the menu and synchronizes again.

mceclip8.png

Now a new rsd file is generated with the posts info, so change its name and title as well. You can continue doing this until all tables are chosen. When there is no more, you can remove the uri and change the generate schema files to never.

mceclip9.png

Advanced features

Automatically getting the correct data types

As standard CData will scan the first 100 rows and determine what data types these fields are. Sometimes this gives an error because the 101 row contained a longer string than was present in the previous rows. The solution simply is to change the Row Scan Depth field to a higher number. If you set it to 0 it will parse the whole file. Also in regards to JSON/XML files, this field doesn't only solve the issue of data type, but also what tables are available. If the file contains more than 100 rows in total these will not be added. So a good idea is to work out what the maximum amount of rows that are contained in the file and then add that or set it to 0 to be sure.

Parsing hierarchical data

Is exactly the same as in JSON or XML respectively. So you use the Data Model feature to choose what should be done with the data.

Using JSON Format

This is the same as in the JSON guide, the only difference is that you need to manually type in LDJSON to the field to use that option.

Pagination

If your api only shows a set amount of rows in a default extraction, you will need to add this to get all of the rows.

The first step is to generate rsd files for all the URI's that you want all rows of. See Getting the data.

There are many ways to do it.

But here below is how you do it, if we use the same GoREST provider as in the section about generating an rsd file.

You need to change the original file from this.

mceclip1.png

To this

mceclip5.png

The file itself looks like this if you open it in a web browser.

mceclip3.png

First I will add an input row in the field area it will by default be 1.

<input name="rows@next" default="1" />

Then set an attribute containing the uri, then choose the repeat element as an xpath and set the rsd file to use paging.

 <api:set attr="BaseURI" value="https://gorest.co.in/public-api/users?_format=json&access-token=U7-PD71mEXzfmdDm3uvpUTWzfDgZWsZTxRW1"/>
<api:set attr="RepeatElement" value="/json/result/" />
<api:set attr="EnablePaging" value="TRUE" />

Then I am going to map the pageCount field and the currentPage field from the _meta area into mappaths. You can make as many as you need.

<api:set attr="elementmappath#1" value="/_meta/currentPage"/>
<api:set attr="elementmapname#1" value="currentPage" />
<api:set attr="elementmappath#2" value="/_meta/pageCount" />
<api:set attr="elementmapname#2" value="pageCount" />

Additionally we are also going to use a page field, which you can't see in the web browser, but it does exist.

In the GET command, that already were there originally, I have added an check loop. Here it adds the next page until there is no more. When that happens it will go to the else part and add that. It is essentially the default page.

<api:script method="GET">
<api:check attr="_input.rows@next">
<api:set attr="uri" value=[BaseURI]&page=[_input.rows@next]" />
<api:else>
<api:set attr="URI" value="[BaseURI]"/>
</api:else>
</api:check>

The last bit is a call check, which uses an if statement. This is to avoid errors from adding an non existing page to the url. It uses the two mappaths to check if the current page is larger then the max page.

 <api:call op="jsonproviderGet" output = "output">
<api:if attr="output.currentPage" value="[output.pageCount]" operator="lessthan">
<api:set attr="output.rows@next" value="[output.currentPage | add(1)]" />
</api:if>
<api:push item="output"/>
</api:call>
</api:script>

This is enough to make it work.

Use parameters

Some REST providers also needs an parameter to work and it can be that you want the parameter to be something dynamic, or something you apply in TimeXtender instead of in the RSD file.

Using fixer.io we can pull out data, but if we want to get data from a specific day the uri would look something like this.

http://data.fixer.io/api/2020-03-15?access_key=522b683569a91b66be6be214027b47bf

So it doesn't work without a date added after the api/. I want it to show current date or similar. I generate an RSD file for the source and adds two changes. Here is the RSD script.

<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="Currencies" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">
<!-- You can modify the name, type, and column size here. -->
<attr name="base_currency" xs:type="string" other:xPath="base"/>
<attr name="date" xs:type="string" other:xPath="date"/>
<attr name="historical" xs:type="boolean" other:xPath="historical" />
<attr name="exchange_rate_to_USD" xs:type="string" other:xPath="rates/USD"/>
<attr name="exchange_rate_to_GBP" xs:type="string" other:xPath="rates/GBP"/>
<attr name="exchange_rate_to_AUD" xs:type="string" other:xPath="rates/AUD"/>
<attr name="timestamp" xs:type="integer" other:xPath="timestamp"/>
<input name="datein" xs:type="string" default="2019-01-01"/>
</api:info>
<!-- 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:check attr="_input.datein">
<api:set attr="URI" value="http://data.fixer.io/api/[_input.datein]?access_key=522b683569a91b66be6be214027b47bf"/>
</api:check>
<api:call op="jsonproviderGet">
<api:push/>
</api:call>
</api:script>
</api:script>

The two changes are <input> and <api:check>

The input sets the datein equal an specifiec date, or the default value. The default is necessary, or the file will fail.

The check is similar to what was done on pagination, only it will note iterate any numbers, but only get the value added.

Having this file, it is possible to use it a couple of ways.

You can use it in a query like so.

Or in a data selection rule like so. Remember that you can't choose the datein field in a list you will have to type it manually.

 

If the rest provider only works if you provide a filter like from date and to date you can also add custom url parameters and use them the same way. Such as this.

<input name="from" xs:type="string" default="2019-01-01"/>
<input name="to" xs:type="string" default="2020-01-01"/>

<rsb:set attr="_connection.CustomUrlParams" value="FromDate=[_input.from]&ToDate=[_input.to]" />

<api:script method="GET">
<api:check attr="_input.from">
<api:set attr="URI" value="https://<rest url>/api/Collection"/>
</api:check>
<api:call op="jsonproviderGet">
<api:push/>
</api:call>
</api:script>

And custom url parameters can also be mixed with pagination as well.

Use parameters to add info from other rest page

Sometimes you will have one page that needs to be filled with data from its source. Such as an Post and its comments, or employer and employees.

Using our own zendesk system we can do such a thing. The following will give you posts.

https://timextender.zendesk.com/api/v2/community/posts.json

To get comments you need to use the ID field and add it as a step after posts. Like what the following shows.

https://timextender.zendesk.com/api/v2/community/posts/360041862792/comments.json

So the url is link/post/{Id}/comments.

To make this dynamic, so you won't have to add all comments as manual data sources we will need to use three of the previous guides in one. RSD files pagination, and parameters.

Step 1 - generate rsd files

First we create a REST CData data source and points it to the posts.json link, sets generate schema files to OnUse and synchronizes. Then we change the URI field to point at the comments.json link and synchronizes again.

Now we have two default rsd files.

Posts.rsd

mceclip1.png

Comments.rsd

mceclip0.png

Step 2 - Add pagination to the files

First we are going to change the posts file so it can do so. We use the same method of pagination as explained earlier, except here the currentPage and the pageCount elementmappath were changed to page and count instead.

<api:set attr="DataModel" value="DOCUMENT" />
 <api:set attr="BaseURI" value="https://timextender.zendesk.com/api/v2/community/posts.json" />
 <api:set attr="RepeatElement" value="/json/posts" />

<api:set attr="EnablePaging" value="TRUE" />
 <api:set attr="elementmappath#1" value="/json/count" />
 <api:set attr="elementmapname#1" value="count" />
 <api:set attr="elementmappath#2" value="/json/page"/>
 <api:set attr="elementmapname#2" value="page" />

<!-- 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">
 <!-- Check if next page. The next page URI will be in _input.rows@next -->

<api:check attr="_input.rows@next">
 <api:set attr="uri" value=[BaseURI]?per_page=1&page=[_input.rows@next]" />
 <api:else>
 <api:set attr="URI" value="[BaseURI]?per_page=1&page=1"/>
 </api:else>
 </api:check>

<api:call op="jsonproviderGet" output = "output">
 <api:if attr="output.page" value="[output.count]" operator="lessthan">
 <api:set attr="output.rows@next" value="[output.page | add(1)]" />
 </api:if>
 <api:push item="output"/>
 </api:call>
 </api:script>

We add the count and page as the options we use to know how many pages to iterate over.

We will do something different in the Comments file besides using pagination.

<input name="rows@next" default=1 desc="Identifier for the next page of results. Do not set this value manually."/>
<input name="PostId" xs:type="string" other:filter="{PostId}" />
</api:info>

<api:set attr="DataModel" value="DOCUMENT" />
<api:set attr="BaseURI" value="https://timextender.zendesk.com/api/v2/community/posts/{PostId}/comments.json" />
<api:set attr="RepeatElement" value="/json/comments" />

We add an input field as explained in the Use Parameters section. The difference is that it is utilized in the BaseURI value in an {} area.

Now when you attempt to preview the comments table it will mention that it needs to have the {PostId} filled. To do so add a custom data selection rule that looks like so.

[PostId] IN (SELECT [id] FROM REST.Posts)

mceclip2.png

This will make the Comments table iterate over each PostId that gets added to the IN statement.

Links and files

All the rsd files I have used in these are added as attachments and here is a list of connection strings for the various parts. They can be copied in to a connection string field in an REST CData provider and work with very little customization.

  • Generate rsd schema file connectionstring: custom url params="_format=json&access-token=U7-PD71mEXzfmdDm3uvpUTWzfDgZWsZTxRW1";generate schema files=OnUse;location="C:\Flat Files\GoREST";row scan depth=0;uri=https://gorest.co.in/public-api/users
  • Pagination connectionstring: Use the same as for the above
  • Parameters connectionstring: custom url params="access_key=522b683569a91b66be6be214027b47bf";location="C:\Flat Files\Fixer"
  • Advanced parameters connectionstring: generate schema files=OnUse;location="C:\Flat Files\Zendesk";readonly=True;row scan depth=0;uri=https://timextender.zendesk.com/api/v2/community/posts.json
Was this article helpful?
1 out of 1 found this helpful

0 Comments

Article is closed for comments.