Skip to content

API Profiles - Parameters

Overview

Parameters are options you can pass to an API endpoint to influence the response, such as specifying the response format or the amount returned. There are several types of parameters: header parameters, path parameters, and query string parameters that are described in the Parameter types section.

The Matillion ETL Query Profile contains RSD files which convert the hierarchical XML or JSON data returned by 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 an API endpoint, how to pass parameters with query profiles in Matillion ETL, and how to use the Query Profile and 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 a JSON file and how to make an RSD file in the Matillion ETL Query Profile interface. For detailed instructions, read Manage Query Profiles.
  • API Query Profiles within Matillion ETL are used in conjunction with the API Query orchestration component.

Parameter types

Query

Query parameters attached to the end of the URI. They are appended to the URI by adding ? at the end of the URI followed by the parameters as key-value pairs with & separating each pair. Example: https://api.exchangeratesapi.io/latest?symbols=USD,GBP,INR&base=USD.

URI

Parameters contained within the path of the endpoint URI, before the query string (?) are called URI parameters. These are usually set within curly brackets. For the exchangerates example, we can replace the /latest portion of the URI with a parameter, {date-in}, and add a URI parameter with the Parameter Name "date-in" and the Value "2021-02-02". This will cause the API to retrieve exchange rates for that date. Our URI now looks like this: https://api.exchangeratesapi.io/{date-in}?symbols=USD,GBP,INR&base=USD.

These parameters are featured in the request header and are usually related to authorization. Our example does not need any header parameter to be added.

Variable types

Variables can be defined within API Profiles to allow more dynamic control over the API request and can be used in a variety of ways. Understanding what each variable type does is key to choosing the right one for the task.

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 'Other' connection options property in the API Query component. Its value, however, will not be persisted.

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 won't appear in connection options and its value can't be changed - i.e. it is "constant".

Input

Adding an input parameter allows you the option of using that parameter as an Input column in the Data Source Filter of the API Query component to build a more complex filter than could be specified in a URI query. Unlike a 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.

For an Input parameter to be available in the API Query component's data filter, you must configure the API Query component to add a PseudoColumns connection option, with the Value *=*. Be warey not to give your Input variable a unique name as it may override any data columns of the same name when the API Query loads endpoint data.

To test an endpoint which uses an input parameter, you must include a default value in the RSD file.

For Input parameters, you can select the connection parameter CustomUrlParams from the drop-down menu, and enter the parameter name and value attributes that you want to add to the URI. This is useful if you need to handle characters such as &, which have special meaning in the URI and therefore need to be encoded in parameter values. For example, add CustomUrlParams with the value Year=${testnumber}&Month=${testmonth} to generate the following URI:

https://api.exchangeratesapi.io?Year=${testnumber}%26Month=${testmonth}

Example: ExchangeRatesAPI

We will be using Open Exchange Rates API 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 Profiles to open the Manage Query Profiles dialog. Click + to add a new query profile.
  2. In the Add Query Profile dialog, enter "ExchangeRateAPI" into the Profile Name field and click OK.
  3. This will return you to the Manage Query Profiles dialog. Click the cog icon next to "ExchangeRateAPI". This will open the Configure Query Profile dialog.

  4. In the Configure Query Profile dialog, click New Endpoint to open the Configure Query Connector wizard.

    This wizard will take through a series of steps to produce an "rsd" script. Options you enter are used to write the basics of an rsd script. This script will be used by the API Query component to get data from the specified API and store it in your data warehouse.

  5. On the Source Details page of the Configure Query Connector wizard, enter an Endpoint Name to uniquely identify the endpoint, and optionally a Description for the endpoint. Then click Next.

  6. On the Endpoint Configuration page of the wizard, enter the URI of the API call you want to make in the Endpoint URI field, for example https://api.exchangeratesapi.io/latest. Select the http method to use with the call: GET or POST. Then complete the following tabs on this page of the wizard:

    • On the Auth tab, select and configure the authentication method the API call requires. The default here is Disabled (that is, no authentication).

    • On the Params tab, click + to add a parameter, and enter the following details:

      • Parameter Name: Enter a unique name for the parameter.

      • Value: The value you want to assign to the parameter.

        • This parameter value 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.

      • Parameter Type: Select one of the following types:

      • Variable Type: Select a variable type. The following screenshot shows our endpoint configured with parameters:

        Adding parameters details

    • On the Body tab you can enter any data which is to be passed in the body of a POST API call.

    • The Response tab will display the server response from the API call when you click Send. This can be used to test whether the configuration is correct and the connection to the API is working.
    • The Log tab displays diagnostic details of the API call.

    Once the returned JSON on the Response tab is validated, click Next.

  7. On the Response Configuration page of the wizard, you can configure the behavior of specific elements returned in the response. For example, which object your "xpath" will start from.

    If you want your "xpath" to start from a specific point, right-click the object in the tree view and click Set Repeating Element. This will place the object into the Repeat Element field.

    You can also enable the paging feature here and select the required paging strategy from the dropdown. Read Paging with the REST API Query profile for a full explanation of paging strategies.

    Once this is configured, click Next. 8. On the Review page of the wizard, you'll see a sample of the data retrieved in the Data Preview tab. You can also review your configurations on the Config Review tab, and review the action log on the Logs tab. 9. Click Finish, and the details you entered will be used to generate an .rsd script. You will be returned to the Configure Query Profile dialog. 10. The Configure Query Profile dialog will now show the newly created .rsd file, with same name as the endpoint you created, for example exchangeratesAPI.rsd. Click the file name, then click the Advanced Mode toggle to display the file content, which you can edit manually here. - You can add or delete any column, or change the value of any field. - You can adjust the fields returned by editing the <attr> elements near the top of the file. - To undo any changes you make, click Revert while the file is selected, or click Revert All to undo the changes to all .rsd files in the profile. - See Adding parameters in RSD files manually, below, for guidance on how to edit this file; however, we recommend that you only attempt this if you already have an understanding of RSD syntax. 11. Test the response of the API endpoint by clicking Test, then clicking the table name. A sample of returned data will be displayed for you to verify the reponse is as expected.

Adding parameters in RSD files manually

This section will discuss adding and editing parameters and parameter values directly in the .rsd file.

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

The format required 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]" />

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

<api:set attr="paramname#1" value="ExampleParameterName" />
<api:set attr="paramvalue#1" value="[_connection.ExampleParameterName]" />

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

<api:set attr="uri" value="https://api.exchangeratesapi.io/[_connection.date-in]">
When using input parameters you must add a default value to the parameter in the RSD file if you want to test the endpoint. Look for input parameters in this format:

<input name="Test" xstype="string" />

And add appropriate default values as follows:

<input name="Year" xstype="string" default="2022" />

After adding parameters to the RSD file, you can test the response result of the API endpoint. In our example, the result is now different, as we changed the query parameter symbols=USD,GBP. It will now only show the data for USD and GBP currencies for the specified date-in value.

Manage connection options

When adding parameters in the RSD file manually, you'll need to provide parameter attribute values through the Manage Connection Options dialog. Parameters you have added through the Configure Query Connector wizard will also be displayed in the Manage Connection Options dialog and can be edited here if needed.

To add parameters in the Manage Connection Options dialog:

  1. Click the cog icon next to New File in the Configure Query Profile dialog.
  2. In the Manage Connection Options dialog, click + to add a new connection option parameter.

  3. Select Other from the Parameter dropdown, and in the Value field enter the parameter name and value attributes. To assign multiple values for one parameter, separate values with ,. To add multiple parameters with different values, separate the parameters with ;. For example:

    symbols=USD,GBP;base=USD;date-in=2021-02-02

    For Input parameters, you can select the connection parameter CustomUrlParams from the dropdown, and enter the parameter name and value attributes that you want to add to the URI. This is useful if you need to handle characters such as &, which have special meaning in the URI and therefore need to be encoded in parameter values. For example, add CustomUrlParams with the value Year=${testnumber}&Month=${testmonth} to generate the following URI:

    https://api.exchangeratesapi.io?Year=${testnumber}%26Month=${testmonth}

    If you have added any Input parameters to your API Profile, you must also add a PseudoColumns parameter, with the Value *=*. This allows the environment variables to appear in the API Query component's Data Source Filter.

  4. Click OK.

Using parameters in an 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. In the Basic/Advanced Mode property, 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 an editor.
  5. Select the Authentication Method from the dropdown and click OK.
  6. Select the Profile from the dropdown, then click OK. The dropdown lists the names of all the API Query profiles you have created. If you haven't created any profiles, click Manage and create a profile as described in Setting parameters in the query profile interface section of this article.
  7. In the Connection Options, you can add any parameters and add or change parameter values, as described in Manage connection options. When you configure Connection Options in the API Query component, changes will not be persisted outside the specific component.
  8. Select a Data Source from the dropdown menu. The data source is the endpoint created in Setting parameters in the query profile interface.
  9. In Data Selection, select all of the data items you want to retrieve from the endpoint, then click OK.
  10. Configure the rest of the API Query component by providing a Target table name and selecting a valid S3 Staging Area.
  11. Run the component by right clicking the component on the job canvas and clicking Run Component. If all properties are configured correctly, the job will run successfully. You can view the details in the Tasks Info.
  12. Click the Sample tab and then click Data. The Sample tab should be populated with data retrieved from the API endpoint.

Dynamically applying parameters at runtime

You can 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 in the API endpoint URI used above, we can change the "base", "symbols", or "date-in" values.

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

You can always test the response of the API endpoint after changing the parameters by running the component and examining the Sample tab.

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 used in this article, [_connection.date-in] is referred to in the RSD and this is acceptable, as we are referring to that expression. However, the following example has a URI that passes information in square brackets that are not part of an expression:

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

To prevent Matillion ETL treating this as an expression and trying to evaluate it, these square brackets must be escaped, so the URI becomes:

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

If you use variables for those inputs (such as "fromdate" and "todate" instead of the literal date strings), 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]\\]">
© Copyright 2024 Matillion Ltd | Legal | Privacy Policy | Disclaimer | Modern Slavery Statement