Authentication with API profiles
  • Dark
    Light

Authentication with API profiles

  • Dark
    Light

Overview

APIs vary in the way they authenticate users. Some APIs need an API key in the request header, while other APIs require elaborate security to protect sensitive data, prove identity, and ensure the requests aren’t tampered with. In this document, you’ll learn more about authentication and how to apply authentication with API Profiles in the Matillion ETL client and using in API Query component. We will also discuss different types of Authorisation Matillion support.

Important Information

  • Every API Query Profile in Matillion ETL contains one or more RSD "files" or Query Profiles. Matillion Query Profile allows to convert the hierarchical XML or JSON data from an API endpoint into a tabular format that can be used by target platforms. Authentication for an API Query Profile is stored within the Query profiles.
  • You are expected to know how to create a JSON file and how to generate a Query profile in the Manage Query Profile interface. For detailed instructions please visit Manage Query Profile.
  • All API Profiles within Matillion ETL can be run using an API Query component in an Orchestration Job.
  • The OAuth Authentication method needs you to go through the Manage OAuth menu in the Matillion ETL client. For the detailed description on adding OAuth, please visit Manage OAuth.

The document is organized into following sections:

  1. Authentication at Manage Query Profile Interface
  2. Authentication at API Query Component
  3. Authentication Methods
Example: ExchangeRatesAPI

We'll be using ExchangeRatesAPI as an example to generate an RSD file in this guide. Open Exchange Rates provides a simple and portable JSON API with live and historical foreign exchange (forex) rates, via a simple and easy-to-integrate API, in JSON format. This can be accessed from a web browser by navigating to the URL: https://api.exchangeratesapi.io/latest.



Authentication at Manage Query Profile Interface

  1. Click Project Manage API Profiles Manage Query Profiles to open Manage Query Profiles interface window. Click to add a new Query profile.
  2. Manage Query Profiles

    Manage Query Profiles

    <li id="profilename">
      <p>In the <b>Add Query Profile</b> pop-up window, enter "ExchangeRateAPI" (as shown here) into the <b>Profile Name</b> field, and click <dfn class="green">OK</dfn>.</p>
      <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2750444/New+Images-2021/Generating+RSD+file-01-02.png" alt="Add API Profile">
      <p class="caption">Add API Profile</p>
    </li>
    <li id="APIprofile"><p>On returning to the <b>Manage Query Profiles</b> pop-up window, find "ExchangeRateAPI" in the API Profiles list, and click <dfn class="icon-settings"></dfn>.</p>
      <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2750444/New+Images-2021/Generating+RSD+file-01-03.png" alt="New File">
      <p class="caption">New File</p></li>
    <li>
      <p>On the <b>Configure Query Profiles</b> window, click <dfn class="green">New Endpoint</dfn>, to generate a new Query profile as an <code>.rsd</code> script using API endpoint URI.</p>
      <img class="mimg" src="https://matillion-docs.s3-eu-west-1.amazonaws.com/images/2750444/New+Images-2021/Generating-RSD-01-04.png" alt="New Endpoint">
      <p class="caption">New Endpoint</p>
    </li>
    
  3. On the next window, provide the Source details. You'll need to name the Query profile and add a description, if needed. Click Next. Source details

    Source details

  4. In the URI field, enter the URI of the API call you want to make. At this point, you can configure Authentication and add any required body and/or header parameters.

    To Add Authentication, please follow the steps below:

    1. Navigate to Auth tab and enabled it by clicking on the Disabled button. By default the Auth is Disabled.
    2. Select the type of Authentication method using the dropdown and provide the required details.
    3. Add Authentication

      Add Authentication

    After you finish parameter configurations, click Send, and you'll get the automated response from the endpoint in the "Response" tab.

    Once, response validated successfully, click Next.

    Please Note

    Detailed instructions on authentication methods will be discussed in the section Different Types of Authorisation of this guide.

  5. On the Response Configuration window, you might only want specific items such as those nested in an object of the JSON. The repeat element allows you to specify that object, so you can define your "xpath" from there.

    If you want your "xpath" to start from the beginning of your object, just click Set Repeating Element using right click on the tree view fields (/rates) into the "Repeat Element" field. Enable the Paging feature and select the required strategy from the dropdown, select the field from the tree view (using right click).

    Response Configuration

    Response Configuration

  6. If all the previous steps have been correctly configured, you should see a sample of the data in Data Preview tab. You can also review your configurations at the Config Review tab. When you click Finish, the details you have entered will be generated into an .rsd script. Data Preview

    Data Preview

  7. Returning to the Configure Query profile window, the file exchangeratesAPI.rsd will be open in tabular mode. Click Advanced Mode at the top of the screen, it will convert into the RSD file.

    Paste RSD file content

    Paste RSD file content

Important Information

  • The generated Query profile allows to edit file manually, you can add or delete any column, or can change the value of any field.
  • You may wish to adjust the fields returned, which you can do by editing the elements near the top of the RSD (lines 6 to 12 in the example above).

Once you will recieve Query profile as exchangerateAPI.rsd at Configure Query Profiles window. Please ensure, you check the response of the API endpoint at the bottom using Test button and, then click on the table name (exchangerateAPI).

Response Test

API Endpoint Response Test

Please Note

If you configure authentication at Manage Query Profile interface, the Connection Options will automatically get the required values. You can check at Manage Connection Options window.

Manage Connection Options

Manage Connection options



Authentication at API Query Component

Matillion ETL also allows authentication to be added via API Query component using "Authentication property" and some inputs in the component's Properties panel.

Once an API Query profile created and has been tested, confirmed to be working correctly and returning data with expected granularity, it's ready to use in the API Query component. This can be done as follows:

  1. Create a new Orchestration job.
  2. Drag an API Query component onto the job canvas.
  3. Click on the component icon to open the Properties panel.
  4. API Query Component

    API Query Component

  5. In the Basic/Advanced Mode, we recommend keeping the Mode of the properties as "Basic". In the "Basic" mode, you need to choose a data source and column. In "Advanced" mode you need to make specific SQL queries in the editor.
  6. Basic/Advanced Mode

    Basic/Advanced Mode

  7. Next, click ... and select the Authentication Method from the dropdown and click OK
  8. Authentication Method

    Authentication Method

    • Bearer Token: If you select Bearer Token, a new property named Bearer Token will be added. Click ... and provide the Token in the Store in Component field. You are allowed to use Password Manager to generate Tokens.
    • Authentication Method-Bearer Token

      Authentication Method-Bearer Token

    • User/Password(Basic): This authentication method enables Username and Password properties. Enter the required credentials using ....
    • Authentication Method-User/password

      Authentication Method-User/password

    • API Key:Value: The API Key:Value Authentication method will add Key Name, Key Value and Add Authorisation To properties.
    • Authentication Method-API Key:Value

      Authentication Method-API Key:Value

    • OAuth: To select an OAuth method, add a new property OAuth. Click ... and select the pre-configured OAuth from the dropdown. If not, then create a new OAuth and click Manage. Follow the steps mentioned before in theAuthentication Methods-OAuth section of this guide.
    • Authentication Method-OAuth

      Authentication Method-OAuth

    • Other: This option allows you to use an alternate authentication with connection option or you can select Other if no authentication is required to fetch the data.
  9. Next, click ... and select the Profile from the dropdown, then click OK . Please note, the Profile is the name of the API Query profile you created. Example: ExchangeRateAPI.
  10. API Query Component-Profile

    API Query Component-Profile

  11. In the Connection Options, you can specify the param attributes value and can dynamically add any param or change the param values. Click to add any parameters, select Other from the dropdown, and assign the values to the parameters. Please note that when you configure Connection Options property in the API Query component, its value will not be persisted.
  12. API Query Component-Connection Option

    API Query Component-Connection Option

    Please Note

    For detailed description on adding parameter to the API endpoint, please refer Using Parameters with API Profiles document.

  13. Select the valid Data Source from the dropdown. The Data Source is the "Endpoint Name". Example: exchangerateAPI
  14. API Query Component-Data Source

    API Query Component-Data Source

  15. Select the items or variables in Data Selection and click OK. The Data Selection are the items or variables you want the data to be collected from the endpoint.
  16. API Query Component-Data Selection

    API Query Component-Data Selection

  17. Configure the rest of the API Query component by providing a Target table name and select a valid Staging Area. The icon of the API Query component will change to green in color.
  18. API Query Component properties

    API Query Component properties

  19. Finally, you can run the component by right click on the API Query Component and then select Run Component. If all properties are configured correctly, the job will run successfully. You can view the details in the Tasks Info.
  20. API Query Component-Run Job

    API Query component-run job

  21. Next, you click Sample and then click Data. It should fetch the data as expected.
  22. API Query Component-Sample Data

    API Query component-sample Data


Authentication Methods

There are several methods for authorisation. The following are various types of API authorisation you might encounter within Matillion:

HTTP Basic Authentication (Basic Auth)

When using basic authentication over HTTPS, you should send authentication credentials with every request to the REST API. To include credentials in the HTTP header, you must supply a "username" and "password" that are connected into a string, using the format user:password.

User Interface-Basic Auth

User Interface-Basic Auth


Bearer Token

Bearer authentication (also called token authentication) is an HTTP authentication scheme that involves security tokens called bearer tokens.

You must send this token in the authorisation header when making requests to protected resources: Authorisation: Bearer <token>

User Interface-Basic Auth

User Interface-Basic Auth

API Key (API Key:Value)

An API key is a token that needs to be provided when making API calls which are protected to access.

You need to provide few details while configuring API Key authentication at the endpoint.

  • Key Name: Provide the a unique Key name into the field.
  • Key Value: Enter the Key value.
  • Add to:Select how to send the key either in the form of "Query Parameter" or "Header Parameter".
    • Query Parameter: GET /https://api.exchangeratesapi.io/latest?api_key=abcdef12345
    • Request Header: GET /https://api.exchangeratesapi.io/latest HTTP/1.1 X-API-Key: abcdef12345
User Interface-API Key Auth

User Interface-API Key Auth

OAuth

When importing data into a table from a third-party service, it's necessary to use OAuth as the authentication mechanism. Matillion ETL abstracts the OAuth setup for all OAuth APIs to make this process as easy as possible.

Matillion Query Profile UI allows you to authenticate API endpoint with OAuth and extract the data.

This process needs you to go through the Manage OAuth menu in the Matillion ETL client. For the detailed description on adding OAuth, please visit Manage OAuth documentation.

Please follow the instructions below to create a new OAuth.

Once you select OAuth as your authentication type, select a pre-configured OAuth from the dropdown.

OAuth

OAuth

If you DO NOT have any existing OAuths, you'll need to create a new OAuth API service within Matillion ETL client:

  1. Click Manage and you'll be directed to the "Manage OAuth" window. Click to add a new OAuth.
  2. Manage OAuth

    Manage OAuth

  3. Provide a unique name for your OAuth entry and select API as service from the dropdown.
  4. Add OAuth

    Add OAuth

  5. Returning to the Manage OAuth window, check the list of OAuths to ensure the new entry is listed and click .
  6. OAuth Settings

    OAuth Settings

  7. You need to configure OAuth settings using third-party authentication details. You'll get these details from the third-party service.
    • Grant Type: The type of request being made, either using a client-side obtained authorisation code, a refresh token, a JWT assertion, client credentials grant or another access token to downscope a token.
    • Auth URL: This is the URL to authorize a user by sending them through the third-party website and request their permission to act on their behalf. Example: https://account.box.com/api/oauth2/authorize/
    • Access Token URL: This URL is to request Access Token that enables a provider to verify that a request belongs to an authorized session. Example: https://api.box.com/oauth2/token/
    • Client ID: The Client ID of the application requesting an access token.
    • Client Secret: The Client secret of the application requesting an access token.
    • Client Auth: Select Client Auth from "Basic Authentication in header" or "Client Credentials in body".
    • OAuth Configuration

      OAuth configuration

  8. An Authorisation Link will be displayed which will complete your OAuth set up. Follow the link to authorise Matillion ETL.
  9. Authorisation Link

    Authorisation Link

  10. You should see "Authorization successful". Click Close. Please note if you get an error message, check all the provided details carefully when you retry.
  11. Authorisation successful

    Authorisation successful

    Select OAuth

    Select OAuth

Attachments