Using Parameters With API Profiles

Using Parameters With API Profiles


Overview

This document builds on an earlier article "Manage API Profiles", and describes how to include parameters to an API Profile that you have created.

The Matillion API 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. This document demonstrates how to pass parameters into the RSD file and how to use those parameters within a job in Matillion. There are two ways to achieve this – using input parameters or using connection parameters.

Warning

  • The RSD syntax used by Matillion ETL has changed significantly from version 1.41. For the latest information, please refer to Paging with the REST API Query component 2020.
  • Due to these changes, the syntax of examples used in this document and RSD files on Matillion ETL may differ slightly from current API standards—for example, <rsb: is now <api:.
  • Other updates include the need for users to setup input parameters as inputs rather than dummy columns, and only returning the date on Date data types as opposed to the datetime as previously.

Input Parameters with Exchange Rates from Fixer.io

https://fixer.io is a JSON API which returns the current and historical currency exchange rates as published by the European Central Bank. For historical exchange rates, see here.

We parameterise the URI in the RSD file to loop through a series of dates to give us the correct exchange rate for dates in one table. To do this, we need to set up 2 parameters:

  • A Matillion Environment Variable
  • A parameter value in the RSD file

We will use the Fixer API to demonstrate how to set up these parameters.

  1. First create the Matillion Environment Variable using Manage Environment Variables in the Project Menu. Name the variable currencyDate and give it a sensible default value for the environment you are working in:

    Edit Environmental Variables

    Edit Environmental Variables

  2. Create a new API profile in using Manage API Profiles in the project menu. Name the profile Fixer:

    Configure API Profile

    Configure API Profile

  3. Copy the contents of the attachment exchr.rsd.txt in to the API profile. This API profile is bringing back the base currency, the date and the exchange rates to USD, GBP and AUD.

  4. In line 15 we are specifying a parameter called datein as part of the URI. The required format to specify a parameter in the RSD file is [_input.<parameter_name>] (where <parameter_name> is the name of your parameter).

  5. In line 11 we are creating a dummy column for this parameter so we can use the parameter in the API component. This parameter must then be linked to the Matillion Environment Variable in the API query component.

  6. In a new Orchestration Job, add the API Query component.

  7. In the Properties, change the mode to Advanced and select the Fixer Profile we have just created.

    API Query Properties

    API Query Properties

  8. We can link the parameters in the where clause in the SQL Query. Copy the below code into the properties box. This tells the component to set the datein input parameter which is used in the RSD file to be the value of the Matillion environment variable ${currencyDate}.

    SELECT base_currency, date, exchange_rate_to_usd, exchange_rate_to_gbp, exchange_rate_to_aud FROM exchr
          WHERE datein = '${currencyDate}'
    Edit SQL Query Properties

    Edit SQL Query Properties

  9. Configure the rest of the API Query component by specifying a valid S3 bucket and a Target Table to write the data to.

  10. The currencyDate parameter values can then be set using an iterator in Matillion to get a table with exchange rates for many dates. In order to do this, because the API Query component is destructive and rewrites the table each time, we recommend writing a small Transformation Job to copy the contents of the table into a permanent stage table.

    Copy contents of table

    Copy contents of table

  11. This Transformation Job should be called immediately after the API Query component in the Orchestration Job.

    Calling Transformation Job

    Calling Transformation Job

  12. A Fixed Iterator can now be used in a new Orchestration Job to pass different values into the currencyDate variable.

    Adding Fixed Iterator component

    Adding Fixed Iterator component

  13. In the Fixed Iterator properties, select the currencyDate variable and give some values to iterate around.

    Fixed Iterator Properties

    Fixed Iterator Properties

  14. When this job is run it will now call the Fixer API for all of the dates specified and the exchange rates will be saved in the table specified. Multiple parameters can be defined in here by separating them in the where clause with an AND.

    Edit SQL Query Properties

    Edit SQL Query Properties


Connection Parameters

Connection Parameters have a very similar principle to the input parameters in that they have to be defined as a Matillion environment variable and also in the RSD file in the API configuration. The format of the parameter in the RSD is:

[_connection.<parameter_name>]

This can be used in the RSD file in the same way that the input parameters are used:

Configure API Profile

Configure API Profile

Line 12 shows the datein parameter as a connection setting.

Please Note

There is no need for dummy columns with this form of parameterisation.

The contents of this RSD file are available in the attached file exchr_connection.rsd. This parameter can then be used in the API Query component in the Basic mode. The parameter should be set to the Matillion Environment Variable in the Connection Options, using the Other parameter as shown below:

Edit Connection Parameters

Edit Connection Parameters

Again multiple parameters can be defined by separating them in the Other using a semicolon:

Please Note

The Other connection property could formally be replaced by ProfileSettings however this has been depreciated. Although ProfileSettings no longer appears on the list, it will still work if manually entered.

Edit Connection Parameters

Edit Connection Parameters


Escaping Square Brackets

In some cases, square brackets are required as part of an RSD (such as in a URI). It is important that the user escape such characters to prevent Matillion ETL from recognising these strings as expressions. In the example above, [_connection.datein] 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

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

If we were to use variables (fromdate and todate) for those inputs, then we would have to escape the sqaure brackets that are part of the URI but not those referring to our variables:

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