API Profiles - Authentication

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.

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

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 pop-up window, enter "ExchangeRateAPI" (as shown here) into the Profile Name field, and click OK.
3. On returning to the Manage Query Profiles pop-up 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:
   a. Navigate to Auth tab and enabled it by clicking on the Disabled button. By default the Auth is Disabled.
   b. 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 Authorisation Types of this article.

7. 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 dropdown, select the field from the tree view (using right click).

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

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

• 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 <attr> 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). 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.

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

• Bearer Token: If you select Bearer Token, a new property named Bearer Token will be added. Click the ellipsis button and provide the Token in the Store in Component field. You are allowed to use Password Manager to generate Tokens.
• User/Password(Basic): This authentication method enables Username and Password properties. Enter the required credentials using ellipsis button.
• API Key:Value: The API Key:Value Authentication method will add Key Name, Key Value and Add Authorisation To properties.
• OAuth: To select an OAuth method, add a new property OAuth. Click the ellipsis button 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 the Authentication Methods - OAuth section.
• 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.

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 detailed description on adding parameter to the API endpoint, please refer Using Parameters with API Profiles document.

8. Select the valid Data Source from the dropdown. The Data Source is the "Endpoint Name". Example: exchangerateAPI
9. 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.
10. 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.
11. 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.
12. Next, you click Sample and then click Data. It should fetch the data as expected.

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.

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>

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

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.