Follow

Connect to a REST api with a CData data source

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

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

Here below how to gain access to GoRest

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.

Here below is how you do it, if the file contains numbers and not links. E.G

You need to change the original file from this.

mceclip4.png

To this

mceclip3.png

The changes are in the square box.

I added an while loop, where it adds then next page until there is no more. I also added a new number in the GET command, so it would increase for each iteration. When it is done it will hit the limit, which will make it stop and give you an amount of rows.

There is other ways to do it as well. If you for example wants to do it on the SWAPI api.

The content of an json link looks like this.

{
    "count": 61, 
    "next": "https://swapi.co/api/planets/?page=2", 
    "previous": null, 
    "results": [
        {
            "name": "Alderaan", 
            "rotation_period": "24", 
            "orbital_period": "364", 
            "diameter": "12500", 
            "climate": "temperate", 
            "gravity": "1 standard", 
            "terrain": "grasslands, mountains", 
            "surface_water": "40", 
            "population": "2000000000", 
            "residents": [
                "https://swapi.co/api/people/5/", 
                "https://swapi.co/api/people/68/", 
                "https://swapi.co/api/people/81/"
            ], 
            "films": [
                "https://swapi.co/api/films/6/", 
                "https://swapi.co/api/films/1/"
            ], 
            "created": "2014-12-10T11:35:48.479000Z", 
            "edited": "2014-12-20T20:58:18.420000Z", 
            "url": "https://swapi.co/api/planets/2/"
        }, 

So here there isn't only a page option, but the next field has the link to the next page.

Here I set it up like this.

mceclip1.png

 

So I add the value from the next field in the [_input.rows@next]. It will stop doing so when the _input.rows@next is empty.

Was this article helpful?
0 out of 0 found this helpful

0 Comments

Please sign in to leave a comment.