Using Parameters With API Profiles

Overview

This document builds on an earlier article "Writing 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.

 

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.

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:

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

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.

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.

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

In a new Orchestration job, add the API Query Component.

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

We can link the parameters in the Where clause in the SQL Query:

Below is the code to copy into the properties box:

SELECT base_currency, date, exchange_rate_to_usd, exchange_rate_to_gbp, exchange_rate_to_aud FROM exchr

WHERE datein = '${currencyDate}'

This is telling 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}. Configure the rest of the API Query component by specifying a valid S3 bucket and a Target Table to write the data to.

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.

This transformation job should be called immediately after the API Query Component in the Orchestration job.

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

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

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.

 


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:

Line 12 shows the datein parameter as a connection setting.
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:

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


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.
 

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]\]" />}}
 

Conclusion

We have looked at two ways to parameterise API Queries. This can be done using an input parameter in the RSD file which is then set using the where clause in the SQL in Advanced mode or using a connection parameter in the RSD which is set via the Connection Options in the API Query component.