Using parameters with API profiles
  • Dark
    Light

Using parameters with API profiles

  • Dark
    Light

Overview

Parameters are options you can pass to the endpoint (such as specifying the response format or the amount returned) to influence the response. There are several types of parameters: header parameters, path parameters, and query string parameters. Request bodies are similar to parameters but are not technically parameters.

The Matillion Query Profile contains RSD files, each of which converts the hierarchical XML or JSON data from an API endpoint into a rectangular format that can be used by target platforms.

In this article, you'll learn more about using parameters with the API endpoint, how to pass parameters with query profiles in Matillion ETL, and how to use the Query Profile in the API Query component to extract the data from the resource. We'll also discuss the different types of parameters Matillion ETL supports.

Note

  • You should know how to create JSON file and how to make an RSD file in the Matillion Query Profile interface. For detailed instructions read Manage Query Profiles.
  • All API Profiles within Matillion ETL can run using the API Query component in an orchestration job.

Example: ExchangeRatesAPI

We will be using ExchangeRatesAPI as an example to generate an RSD file in this article. Open Exchange Rates provides a simple, lightweight, and portable 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.



Setting parameters in the query profile interface

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

    Manage Query Profiles

  3. In the Add Query Profile pop-up window, enter "ExchangeRateAPI" (as shown below) into the Profile Name field and click OK.

    Add API Profile

    Add API Profile

  4. Return to the Manage Query Profiles pop-up window and click on "ExchangeRateAPI" in the API Profiles list. This will open the Configure Query Profile pop-up window. New File

    New File

  5. On the Configure Query Profile window, click New Endpoint to create a new Query profile using API endpoint URI.

    New Endpoint

    New Endpoint

  6. On the next window, provide the Source details. You'll need the Endpoint Name of what to call the table. You can also provide a description. Once done, click Next. Source details

    Source details

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

    To add parameters, please follow the steps below:

    1. Navigate to Params tab, and click to add a parameter.
    2. In the Parameter Name field, enter a parameter name.
    3. In the Value field, enter the value you want to assign to the parameter.
    4. Add Parameter

      Add Parameter

      Please Note

      • The parameter mentioned above is for testing and validation only.
      • If no value is passed to the parameter, it's considered to be a "Default" parameter.
      • In a function, if a parameter is not provided, its value becomes undefined.
    5. In the Parameter Type, select the type from Query, URI, & Header.
      • Header Parameter: These parameters are featured in the request header and are usually related to authorization. This example does not need any header parameter to be added.
      • Query Parameters: A set of parameters attached to the end of the URL is called Query Parameters. They are appended to the URL by adding '?' at the end of the URL. In addition to that, they are followed immediately with a key-value pair (Query Parameter). Example: symbols=USD,GBP,INR&base=USD
      • After adding query parameters, the endpoint URI will look like this:
        https://api.exchangeratesapi.io/latest?symbols=USD,GBP,INR&base=USD 
      • URI Parameters: Parameters within the path of the endpoint, before the query string (?). These are usually set within curly brackets. In the example we changed the /latest to /{date-in}. This will give exchange rates for the specified date, mentioned in the "Value" column. After adding the URI parameter, the endpoint URI will look like this:
        https://api.exchangeratesapi.io/{date-in} 
      Select Parameter Type

      Select Parameter Type

    6. In the Variable Type, select the type of variable for the parameter:
      • Connection: A connection is a generic parameter type used by Query components. Its name will be added to the Profile's Connection Options in the Query profile interface and when configuring the Connection Options property in the API Query component. Its value, however, will not be persisted.
      • Input: Adding an Input parameter allows you the option of using that parameter in the Data Source Filter of the API Query component to build a more complex filter than could be specified in a URL query. For example, the following would be possible if "type" had been added as an Input parameter:
        Data source filters
        The Input parameter is not added to the endpoint URI. Unlike a URI/Connection parameter, which filters the data during the call to the API, an Input parameter query is performed on the data after it has been returned by the URI call.
        Important: If using an Input parameter type, you must add the PseudoColumns parameter to the Connection Options as described below.
      • Constant: A Constant is a parameter that will have both its name and value persisted through the Query profile interface and into the API Query component. However, it will not appear in connection options and its value cannot be changed - i.e. it is "constant".

      Select Variables Type

      Select Variables Type

    The final endpoint URI will look like this:

    https://api.exchangeratesapi.io/{date-in}?symbols=USD,GBP,INR&base=USD Adding Parameter details

    Adding Parameter details

    Click Send, and you'll get the automated response from the endpoint in the "Response" tab.

    Check Response

    Check Response

    Once the returned JSON is validated, click Next.

  8. On the Response Configuration window, you may 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, rather than at the start of the document each time.

    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. Next, enable the Paging feature and select the required strategy from the dropdown. Select the field from the tree view (right click on the field), to populate the xpath, which points to where the data is in the JSON or XML document returned by the API.

    Response Configuration

    Response Configuration

  9. You'll see a sample of the data in Data Preview tab. You can also review your configurations through the Config Review tab.When you click Finish, the details you entered will be generated into an .rsd script. Data Preview

    Data Preview

  10. The Configure Query Profile window will show exchangeratesAPI.rsd in tabular mode. Clicking Advanced Mode at the top of the screen will convert into the RSD file, which can be modified manually.

    Paste RSD file content

    Paste RSD file content

Important Information

  • The generated RSD file allows you to edit files manually, add or delete any column, or 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).

You'll receive a generated Query profile as exchangerateAPI.rsd at Configure Query Profile window. Check the response of the API endpoint at the bottom using the Test button, then click on the table name (exchangerateAPI).

Response Test

API Endpoint Response Test

Please Note

If you configure Parameters in the Manage Query Profile interface, the Connection Options will fetch the param and attributes values automatically, which you can edit later, if needed.

If you have specified an Input parameter, you must add a PseudoColumns parameter (with no value) here.

Manage Connection Options

Manage Connection Options


Parameters in RSD file Manually

This section will discuss adding/editing param and param values directly in the RSD file.

Please Note

When adding parameters within the RSD file manually, you'll need to configure connection options and provide attribute values to the parameters in Manage Connection option.

Please check below for the required format for adding parameters in the RSD file.

Header Parameter

The required format to specify a Header parameter type in the RSD file is:

<api:set attr="Header:Name#" value="ExampleHeaderName" />
<api:set attr="Header:Value#" value="[_connection.ExampleHeaderName]" />
Query Parameter

The required format to specify a Query parameter type in the RSD file is:

<api:set attr="paramname#1" value="ExampleParamterName" />
<api:set attr="paramvalue#1" value="[_connection.ExampleParamterName]" />
Example in RSD: Adding Query Parameter in RSD

Adding Query Parameter in RSD-12

URI Parameter

The required format to specify a URI parameter type in the RSD file is adding [_connection.URIParamterName] at the end of the endpoint URI :

https://api.exchangeratesapi.io/[_connection.date-in]
Example in RSD: Adding URI Parameter in RSD

Adding URI Parameter in RSD

Manage Connection Option in Wizard

When adding parameters in the RSD file manually, you'll need to provide parameter attribute values through Manage Connection Option.

  1. Click Configure Query Profile.
  2. Click to add a new connection option parameter.
  3. Select Other from the dropdown and assign values to the attributes <Paramname>:<ParamValue>, seperated by a "," to assign multiple values for one attribute. Example: symbols=USD,GBP,INR,AUD
  4. You can add multiple param and param values in Other separated by "semicolon ;" Example: symbols=USD,GBP;base=USD;date-in=2021-02-02. Click OK and you'll be return back to the RSD file.

Manage Connection Options-specify parameter value

Manage Connection Options-specify parameter value


After adding parameters to the RSD file, you can test the response result of the API endpoint. This result will be different compared to before, as we changed the Query Parameter symbols=USD,GBP. It will only show the data for USD and GBP currencies for specified date-in value.


Test API Endpoint response-15

Test API Endpoint response-15




Using RSD Endpoint in API Query Component

Once an API Query profile (RSD file) is created and tested it's ready to be used 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 "Basic" mode, you need to choose a data source and column. In "Advanced" mode you need to make specific SQL queries in editor.
  6. Authentication Mode

    Authentication Mode

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

    Authentication Method

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

    Please Note

    If you haven't created any Query Profiles, you can create a new Query Profile using the Manage button, which will direct you to the Manage Query Profile window. Please follow the steps as mentioned in Parameters in Manage Query Profile Interface section of this guide.

  11. In the Connection Options, you can specify the param attributes value. You can also add any params or change the param values, as in Manage Connection Options while a Query Profile. 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

  13. Select the valid Data Source from the dropdown. The Data Source is the name of the "Endpoint" provided in the Source Details window. 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 wanted the data to be collected from 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 selecting a valid S3 Staging Area. Once all details are provided and validated as OK, the icon of the API Query component coverts to green.
  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


Dynamically applying parameters at runtime

You could add or change the parameter attribute values at any time in the API Query component and test the sample data. Make sure that the API endpoint is still fetching the expected data and giving granular results.

For example, following the above API endpoint URI, we want to change the "base" or "symbols" or "date-in" values.

You can change the parameter values or add any additional parameter dynamically at runtime in the API Query component through the Connection Option property.

For example, you can change the base from "USD" to "INR", change the date-in value, or change symbols.

After editing in Connection Option, it will look like this: After change in Parameters

After change in Parameters

You can always test the response of the API endpoint after changing the parameters. For example as shown below:

After change in Parameters-Result

After change in Parameters-Result


Escaping Square Brackets

In some cases, square brackets are required as part of an RSD (such as in a URI). Escaping these characters prevents Matillion ETL from recognising these strings as expressions. In the example above, [_connection.date-in] is referred to in the RSD and this is acceptable as-is since we are referring to that expression. However, below we have a URI that passes information in square brackets and these must be escaped.

http://compass.cosential.com/api/contacts/search?q=CreateDate:[20140101 TO 20141231]

Becomes:

<api:set attr="uri" value="http://compass.cosential.com/api/contacts/search?q=CreateDate:\\\\[20140101 TO 20141231\\\\]" />

If you use variables (fromdate and todate) for those inputs, then you need to escape the square brackets that are part of the URI but not those referring to your variables:

<api:set attr="uri" value="http://compass.cosential.com/api/contacts/search?q=CreateDate:\\\\[[_connection.fromdate] TO [_connection.todate]\\\\]" />