API Profiles - Authentication

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.

Note

Strictly, Authentication is the process of verifying who a user is, while Authorization is the process of verifying what a user has access to. In this article, the terms may be used interchangeably.

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 via the Query Profile

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.

Click Project → Manage API Profiles → Manage Query Profiles to open Manage Query Profiles interface window. Click the + button to add a new Query profile.
In the Add Query Profile dialog, enter "ExchangeRateAPI" (as shown here) into the Profile Name field, and click OK.
On returning to the Manage Query Profiles dialog window, find "ExchangeRateAPI" in the API Profiles list, and click the cogwheel icon for settings.
On the Configure Query Profiles window, click New Endpoint to generate a new Query profile as an .rsd script using the API endpoint URI.
On the next window, provide the Source details. You'll need to name the Query profile and add a description, if needed. Click Next.
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, follow these steps:
1. Navigate to the 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 drop-down 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 Authentication methods.
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 Pagination Strategies feature and select the required strategy from the drop-down then select the field from the tree view (using right-click).
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.
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.
You can check the response of the API endpoint at the bottom using the 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 via the 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:

Create a new Orchestration job.
Drag an API Query component onto the job canvas.
Click on the component icon to open the Properties panel.
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.
Click the ellipsis button ... and select the Authentication Method from the drop-down and click OK. Read the authentication methods section for more information.
Click the ellipsis button ... and select the Profile from the drop-down, then click OK. Note, the profile is the name of the API Query profile you created. Example: ExchangeRateAPI.
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. 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, refer to the API Profiles - Parameters article.
Select the valid Data Source from the drop-down. The data source is the "Endpoint Name". Example: ExchangerateAPI.
Select the items that you want to retrieve from the data source, and click OK.
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.
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.
Next, click Sample and then click Data. It should fetch the data as expected.

Authentication methods

There are several methods for authentication. The following are various types of API authentication 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, use the Manage OAuth process.

OAuth stands for "Open Authorization", and is a protocol for online authorization designed to allow websites, browser-based applications, server-side web applications, native/mobile apps, and connected devices to access resources hosted by other web apps on behalf of a user. Matillion ETL uses the industry-standard OAuth 2.0 protocol, which provides consented access and restricts actions of what the client app can perform on resources on behalf of the user, without ever sharing the user's credentials.

In OAuth exchanges, the application making the access request is called the client (this will be Matillion ETL), the server containing the data to be queried is called the resource server, and the server providing authentication is called the authorization server.

OAuths use an access token, which is a secure key Matillion ETL will use to communicate with the API endpoint. Matillion ETL must obtain a client id and client secret from the authorization server to identify and authenticate itself when requesting an access token. The process for obtaining these will entirely depend on the authorization server, which will typically provide some form of portal that you must navigate to find these details. Read Manage OAuth for more details.

An OAuth access token will often have a specific scope, which defines which resources the Matillion ETL client will be granted access to. Acceptable scope values, and which resources they relate to, are dependent on the resource server.

Matillion ETL supports both "two-legged" and "three-legged" OAuth processing. Two-legged OAuth processing involves three parties: Matillion ETL (the OAuth client), the authorization server, and the resource server. Matillion ETL initiates a request with the authorization server and receives an access token, which it uses to access protected resources on the resource server. Three-legged OAuth processing additionally requires a resource owner to give Matillion ETL access to the resource server without sharing credentials.

The process a client follows to get authorization is called a grant. Different OAuth grant types address different access scenarios, and Matillion ETL supports the following grant types:

Client Credentials: The authorization server provides an access token after Matillion ETL is authenticated with the client id and secret obtained when the OAuth was created.
Authorization Code: The authorization server returns a single-use authorization code to Matillion ETL, and this is then exchanged for an access token. The exchange happens securely on the server side.

For security reasons, access tokens expire after a short duration. For this reason, some authentication servers will also issue a refresh token. Unlike access tokens, refresh tokens normally have long expiry times and may be exchanged for new access tokens when the latter expires. If a refresh token is issued, it will be securely stored on the Matillion ETL instance.