API Profiles - Authentication
    • Dark
      Light

    API Profiles - Authentication

    • Dark
      Light

    Article Summary

    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 the different types of authorization that Matillion ETL supports.

    • Every API Query Profile in Matillion ETL contains one or more RSD files or Query profiles. Matillion Query profiles convert the hierarchical XML or JSON data from an API endpoint into a tabular format that can be used by the 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 requires use of the Manage OAuth menu.

    Authentication at Manage Query Profile Interface

    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.

    1. Click Project → Manage API Profiles → Manage Query Profiles to open Manage Query Profiles interface window. Click the + button to add a new Query profile.
    2. In the Add Query Profile dialog, enter "ExchangeRateAPI" (as shown here) into the Profile Name field, and click OK.
    3. On returning to the Manage Query Profiles dialog window, find "ExchangeRateAPI" in the API Profiles list, and click the cogwheel icon for settings.
    4. On the Configure Query Profiles window, click New Endpoint, to generate a new Query profile as an .rsd script using API endpoint URI.
    5. On the next window, provide the Source details. You'll need to name the Query profile and add a description, if needed. Click Next.
    6. 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:
    7. Navigate to Auth tab and enabled it by clicking on the Disabled button. By default the Auth is Disabled.
    8. Select the type of authentication method using the dropdown and provide the required details.

    After you finish parameter configurations, click Send, and you'll get the automated response from the endpoint in the "Response" tab. Detailed instructions on authentication methods will be discussed in the section Authorization methods.

    1. 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.

    2. 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 Pagination Strategies feature and select the required strategy from the dropdown then select the field from the tree view (using right click).

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

    4. 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 and it will convert into the RSD file.

    5. You check the response of the API endpoint at the bottom using Test button and, then click on the table name (exchangerateAPI). If you configure authentication at the Manage Query Profile interface, the required values will be present in the connection options for this profile.


    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 has been created and is confirmed to be working correctly, 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. 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.
    5. Next, click the ellipsis button and select the Authentication Method from the dropdown and click OK. See the authentication methods section for more information.
    6. Next, click the ellipsis button 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.
    7. 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.

    For a detailed description on adding parameters to the API endpoint, please refer to the API Profiles - Parameters article.

    1. Select the valid Data Source from the dropdown. The Data Source is the "Endpoint Name". Example: ExchangerateAPI.
    2. Select the items Data Selection that you want to retrieve and click OK.
    3. Configure the rest of the API Query component by providing a Target table name and selecting a valid Staging Area. The icon of the API Query component will change to green in color.
    4. Finally, you can run the component by right-clicking the API Query Component and then selecting Run Component. If all properties are configured correctly, the job will run successfully. You can view the details in the Tasks Info.
    5. Next, click Sample and then click Data. It should fetch the data as expected.

    Authentication Methods

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

    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.

    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 authorization header when making requests to protected resources:

    Authorization: Bearer <token>

    You can use the Password Manager to store tokens.

    API Key:Value

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

    • Key Name: Provide the 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

    OAuth

    The Matillion Query Profile UI allows you to authenticate with an API endpoint with OAuth and extract the data. To register an OAuth in Matillion ETL, go through the Manage OAuth menu.

    Attachments