Obtaining an API token and passing it into a RSD script

Obtaining an API token and passing it into a RSD script


Overview

Some API profiles require a refresh token to be passed in as part of the header, these include the Volo API and the Hubspot API. Some of these refresh tokens expire regularly, even hourly, and will need to be refreshed as part of the Matillion ETL job. This document using an example endpoint to look at how to refresh an API token and how to pass this into an API profile.

Warning

  • The RSD syntax used by Matillion ETL has changed significantly from version 1.41. For the latest information, please refer to Paging with the REST API Query Component 2020.
  • Due to these changes, the syntax of examples used in this document and RSD files on Matillion ETL may differ slightly from current API standards—for example, <rsb: is now <api:.
  • Other updates include the need for users to setup input parameters as inputs rather than dummy columns, and only returning the date on Date data types as opposed to the datetime as previously.

Example

This example API requires an Authorisation Token to be passed through with every API call to authenticate the user. The Authorisation Token expires every hour, so in order to ensure Matillion ETL is making the API call with a valid token, a separate API call must be made to get the the token required.

This is done using the API token endpoint, which requires a "Bearer" Authorisation Token. This token is only accepted by the token endpoint. No other endpoint in the API, not even an API key required to query all other endpoints will accept this token.

  • An example response from this endpoint:

    {
     "token": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
    }
  • The RSD file used to access the endpoint:

    RSD file

  • Line 6 writes the token value is written into a column called token in the new table. Line 13 and 14 are passing the Basic Authorization as an Authorization Header and line 15 and 16 are passing through the API Key as a header.

  • In order to call another of the API’s endpoints—for example, the Sales Orders—a full authorisation token is required in the format Bearer xxxxxxxxxxxxxx. The basic authorisation token used for the token endpoint will not be accepted. To allow different token values to be passed to the Sales Orders RSD file, the Header Authorisation value is parameterised using a connection parameter in line 13. The API key is passed through in line 14 and 15.

    Header Authorisation

  • This API Profile can then be used in an Orchestration Job such as below. This job contains two API Query components with a Python Script component in the middle:

    Orchestration Job

  • The first API Query component calls the authorisation RSD to obtain the token and put the value into a table called auth_token. This table will only have one row of data in. Below we set up the component to take the token from the "auth" data source:

    First API Query component Properties

  • Next, the Python Script component is used to first edit the token, by adding 'Bearer ' before it, then the script, by putting the latest token into a Matillion ETL environment variable:

    Editing the token in Python Script

  • The final API Query component is used to query the Sales Orders RSD file, by passing the token from the variable value into the RSD as a connection option:

    Second API Query component Properties

  • The job can now be scheduled and the user can be confident that the API Query will not fail with an authentication error.

    Please Note

    Please find the job and API profiles used in this document attached.