Before you attempt this you need to add the REST provider to your list of available sources.
Go through this guide before you start.
- REST Information
- CData Setup
- Getting the data
- Advanced Features
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.
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.
You have two data types of data.
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.
I also used row scan depth = 0 to be sure the whole document gets read.
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
It gives me 20 rows from page 1 and needs a access token to work.
Here is how I set it up.
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,
then add a location in the schema area,
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.
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.
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.
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
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.
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.
The file itself looks like this if you open it in a web browser.
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:set attr="uri" value=[BaseURI]&page=[_input.rows@next]" />
<api:set attr="URI" value="[BaseURI]"/>
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)]" />
This is enough to make it work.
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.
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"/>
<!-- 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:set attr="URI" value="http://data.fixer.io/api/[_input.datein]?access_key=522b683569a91b66be6be214027b47bf"/>
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:set attr="URI" value="https://<rest url>/api/Collection"/>
And custom url parameters can also be mixed with pagination as well.