Tutorial

TimeXtender REST data source

4 months ago
8 January 2024
1 reply
586 views

Userlevel 6

+5

Thomas Lind
Community Manager
710 replies

With the TimeXtender REST data source, you can gain access to REST APIs. Many APIs have a unique setup and use a specific behavior, this article describes the settings to connect to various APIs.

Connect to REST APIs with various authentication methods such as Header options, Bearer Tokens, OAuth, and similar. If the REST API contains multiple resources such as users, posts, and comments you can use it to connect to each one as individual endpoints, by providing the base URL. The data source offers pagination options to connect to each page of an endpoint. In some cases, endpoints require the input of a date or an ID from another endpoint. Configure dynamic values within the data source to handle this requirement.

In the bottom are some real examples showing how to connect to some APIs everyone should have access to. Additionally they show how to use specific options about authentications and how to use dynamic values.

Content

TimeXtender REST Data Source Settings

Data source

Add a data source in the Portal and select the TimeXtender REST data source. Add a name and description for the data source.

Connection Settings

Add the base URL for the API to connect to. The base URL would be the bold part of the following URL https://gorest.co.in/public/v2/users. The remainder (i.e. /public/v2/users) is the part that will be used for the Users Endpoint.

If necessary, setup retries and timeouts for the API calls

Authentication

Setup Authentication by selecting one of the following options from the dropdown: None, Basic, Bearer, OAuth2, and OAuth refresh token. Some authentication options such as APIKey header need to be set up as a header instead.

None - No authentication will be done with this section
Basic - Authenticate using a username and password
Bearer - Authenticate using a non-expiring Bearer Token
OAuth2 - Authenticate using OAuth 2.0 to gain access
You will need to apply an Access URL that returns the token. Then you need to apply how the Send request is done as either Credentials in the body or a Basic authentication header. You also need to choose how the Authorization token is returned, either in a Request header or in a Request URL. You can apply parameters by clicking on the Add button. These could be client_Id and client_secret. You have an option to set the value as Is sensitive to make it obscured when it is previewed.
OAuth refresh token - Use this option when a refresh token is needed to maintain access
This is a similar setup to the OAuth2 option from above, the difference is that you can set a Refresh token working directory path to store these in.

Advanced

This section contains advanced options including Request limits, Caching, Headers and Pagination

Request limits

Some APIs have limits on the amount of calls being allowed and this setting helps to manage this. You can set limits for requests per second, minute, hour, or day.

Caching

The caching options decides how the downloaded data is handled. If In Memory is selected, all the downloaded data will be kept in memory. Processing, such as XSLT will also happen in memory. If the downloaded data is large, the machine can run out of memory. If this happens, switch to File type caching.

Besides saving memory, storing it in a file helps identify the output being returned and assists in troubleshooting when completing the initial setup.

Adding a cache path as shown above will store all the files in a folder named after the data source.

Headers

Add all the headers the API requires to return the desired output.

Many APIs have limits on how many rows are returned and this option will help ensure all rows are returned.

Parameters - Add the necessary parameters such as page or page size.
Set the type of parameter that contains the value. It can be XPath which will be similar to \meta\pagination\page or Header which could be links-next. If any of the parameters are not found, the pagination will stop.
SQL Expression - Create a SQL query that returns some value. Similar to above this requires setting up a parameter action to handle what this returns. Add a Connection string to the server and enter the username and password. Set SQL or Snowflake as the target type to run the query against.
Built in value options - There are two options to help with increasing a value to use for pagination. These are {TX_NextPage} and {TX_CurrentPage}. They both are usable as the value option in query parameters such as page parameter equal to {TX_NextPage} or in a SQL query where you increase the current value with a number such as SELECT {TX_CurrentPage} * 1000 AS offsetValue. On a given page where you used both option for some reason and {TX_NextPage} is equal to 2 then {TX_CurrentPage} would be equal to 1. There is two guides in the bottom of this guide that shows how to use either one.
Parameter action - You have some options that can be set to handle what is returned from a parameter and a SQL Expression.
- Don’t do anything
- Add as query parameter - Select this to add the value such as page number to the URL
- Replace post body - Select this to configure how the post pody shall be like on all subsequent requests. This is done on each endpoint. It is enabled generally, but you need to configure the "replace post body" for each endpoint you got).
- Replace URL - This will replace the current URL with the returned value
Here is how a Parameter action looks when using the query parameter and the URL is returned from either a parameter or a SQL expression

Endpoints

An endpoint is a specific part of the API that is being accessed. As explained in the Base URL section, it will be what is after the base. Most APIs will have a list of these documented somewhere so you can know what options are available.

The data source will not work if you do not have an endpoint added.

When you set the path of an endpoint, for example api/users, the name will automatically be applied as users. You can then edit the name afterwards to make it be Users or All users.

Click Add endpoint to further configure the endpoint

Query Parameters

This is an option to add additional values that are applied to the URL such as an access_key value or similar.

It will be applied like ?access_key=<value> and if you have more than one parameter with & signs between each.

Table flattening

A table flattening is an XSL Transformation that can be used to transform (most notably to flatten) the data returned from the API.

You can add a XSL document in the field and the name it has will become a table.

The flattening helps getting fields from other areas into a table that would normally not have them.

If you turn on Caching to point at a folder, the endpoint will generate a folder with a Data_.xml file that could look like this.

<?xml version="1.0" encoding="utf-8"?>
<TX_Autogenerated_Root>
	<TX_Autogenerated_Element>
		<success>true</success>
		<timestamp>1704797163</timestamp>
		<base>EUR</base>
		<date>2024-01-09</date>
		<rates>
			<AED>4.018342</AED>
			<AFN>77.001036</AFN>
			<ALL>104.154508</ALL>
            ...
		</rates>
	</TX_Autogenerated_Element>
</TX_Autogenerated_Root>

Using the above as a guide, a small example of what an XSL setup could look like could be this.

<xsl:stylesheet version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml" indent="yes" />
  <xsl:template match="/">
    <xbi_root>
      <xsl:apply-templates select="/TX_Autogenerated_Root/TX_Autogenerated_Element/rates" mode="rows" />
    </xbi_root>
  </xsl:template>
  <xsl:template match="/TX_Autogenerated_Root/TX_Autogenerated_Element/rates" mode="rows">
    <Rates>
      <xsl:element name="ALL">
        <xsl:value-of select="ALL" />
      </xsl:element>
      <xsl:element name="DKK">
        <xsl:value-of select="DKK" />
      </xsl:element>
      <xsl:element name="EUR">
        <xsl:value-of select="EUR" />
      </xsl:element>
      <xsl:element name="GBP">
        <xsl:value-of select="GBP" />
      </xsl:element>
      <xsl:element name="date">
        <xsl:value-of select="../date" />
      </xsl:element>
      <xsl:element name="timestamp">
        <xsl:value-of select="../timestamp" />
      </xsl:element>
    </Rates>
  </xsl:template>
</xsl:stylesheet>

The top level tag is xsl:stylesheet. Below this there is a xsl:output tag, which states what the result will be. The next is a xsl:template tag in which has a match attribute. The match part / is pointing to the the top level. Further down in this a xbi_root tag is applied which specifies what the root element will be for the data. This has a xsl:apply-templates tag that points at the XPath for where the data will be located. Below this there is a second xsl:template tag which contains a specific <table name> tag, in this case it will be Rates that is the name. In this tag all the elements of the table is applied the xsl:element contains the name attribute and the xsl:value-of contains the select attribute that shows what data it adds. If you look at the XML file setup from above you can see that the two bottom elements, date and timestamp aren’t inside the rates tag. They are applied from above by using the ../ in front of their name.

Apply the above in the XSL field to use it give it the same name as what the table will be called. The name of the flattening does not have to equal the table name. The XSL itself will as mentioned above give the name of the table. Theoretically an XSL can output multiple tables. However it makes it easier if it is the same when only one table is to be generated.

Check the Only list flattened tables option to ensure that only this XLST result will be returned.

We have added a tool to generate the above documents, so you don’t have to work with files. This tool is an simple drag and drop operation, you only need a json/xml snippet of the file structure you connect to.

Table builder user manual for the TimeXtender REST Data source

Endpoint Advanced Settings

HTTP method - Set this to Get, Post or Put to decide how the call is being done. When this is set to Post you will have to add how the post body will look.
Perform exhaustive meta data scan - If this is checked, all pages and all dynamic values (if any) are executed during metadata operations. If not, only the first page and first dynamic values are executed.
Use endpoint name as schema name - This feature will add the name of the endpoint as the schema name. This is beneficial when the data source returns data with the same format despite using different endpoint paths. Be aware that if this is checked after the initial synchronize task has been done it will generate a new table in the data source.

Dynamic values

This can be used to loop over a set of values. Each row in the dynamic values will be one REST call (or many, if there are multiple pages). They can be used by referencing a column name in curly brackets, for example: {MyColumn}. The dynamic values can be used in Query Parameters (value), Post Body, Headers (name and value), Pagination (SQL Expression, Replaced URL and Replaced Post Body), Endpoint Path, and Table Flattening (Xslt).

Below is a simple example of how to use a SQL query to set dynamic values, where yesterday's date is used as an endpoint value.

Notice the path is the name of the endpoint in curly brackets {yesterday} as long as my SQL query returns this name it will be applied.

If a date range is needed (i.e. a start and end date with individual values) it could be done similarly, by ensuring that two dates are returned by the SQL query.

You can also change the Dynamic values source to be From endpoint table instead. Here you choose a already added data source endpoint. Similar to when using a query a part stored in curly brackets {} will be what it attempts to change. Here it is important that you add a field that exists in the endpoint you point at.

Here below I point at a All jobs endpoint that has a table called All Jobs_TX_Root with two fields Id and Name. So in my setup I have added {Id} as the value to change. You need to be sure it is in the same case setting as well, adding id or ID would not work.

Override pagination

This is similar to the data source pagination section, only here it is done to override the existing setup.

Override caching

This is similar to the data source caching section, only here it is done to override the existing setup.

Override headers

This can be used to override existing headers to values that are set for this endpoint.

Real examples to use for TimeXtender REST data source

Dynamic value from SQL query

Here is an article showing how you can connect to an specific API called Fixer IO. The guide have many specific examples which uses some of the options explained. Specifically it uses SQL queries and XSL in the setup to gain the most from the API.

Connect to Fixer IO

Dynamic value from endpoint table

Here is an article showing how you can connect to our own TimeXtender API. The guide have many specific examples which uses some of the options explained. Specifically it shows how to use dynamic values from another endpoint and it shows how to apply a header for authorization.
Connect to TX API

Applying pagination, request limits and using a bearer token for authentication

Here is an article showing how you can connect to a free API called GoREST. The guide have many specific examples which uses some of the options explained. Specifically it shows how to set up Pagination based on a header, how to use request limits and how to apply a bearer token for authentication.
Connect to GoREST

Using OAuth 2.0 client authentication and doing token based pagination

Here is an article showing how you can connect to the Microsoft Graph API. The guide have many specific examples which uses some of the options explained. Specifically it shows how to set up Pagination based on a URL located in the result and how to use OAuth 2.0 Client authentication.
Connect to Graph API

Using {TX_NextPage} for pagination when no next or last page option is known

Here is an article showing how you can connect to the openbrwerydb API. The guide have many specific examples which uses some of the options explained. Specifically it shows how to set up Pagination based on page and page size when no last of next page options is known.
Connect to openbrewerydb

Using {TX_CurrentPage} for pagination when when no last offset or next starting point is known

Here is an article showing how you can connect to the soda.demo.socrata.com API. The guide have many specific examples which uses some of the options explained. Specifically it shows how to set up pagination bases on a offset and limit when no info about what the last offset value will be.
Connect to soda socrata

1 reply

P

Userlevel 3

+1

This is huge! GREAT JOB!