Blog| Matillion ETL for Amazon Redshift

Using the Open Exchange Rates Query component in Matillion ETL for Amazon Redshift

Matillion uses the Extract-Load-Transform (ELT) approach to delivering quick results for a wide range of data processing purposes: everything from customer behavior analytics, financial analysis, and even reducing the cost of synthesizing DNA.

The Open Exchange Rates Query component presents an easy-to-use graphical interface, enabling you to pull data from the Open Exchange Rates API directly into Amazon Redshift. This component is very popular with our customers to source live and historical currency exchange rate data, allowing for currency conversions of data loaded into Amazon Redshift from additional external sources.

The connector is completely self-contained: no additional software installation is required. It’s within the scope of an ordinary Matillion license, so there is no additional cost for using the features.

 

Open Exchange Rates Query component in Matillion ETL for Amazon Redshift

 

Authentication

Before using the Open Exchange Rates Query component, users are required to link to their Open Exchange Rates account via an App ID. This is the authentication required by Open Exchange Rates to utilize their APIs. See our article that describes how to Create a New Open Exchange Rates App ID.

 

Open Exchange Rates Query component in Matillion ETL for Amazon Redshift - Authentication

 

Once an App ID has been created, you can directly copy it into the App ID field of the Open Exchange Rates Query component or use it like a password through the Password Manager.

Data Source

Next, you can choose the data you want to load into Amazon Redshift from the Data Source drop down. This is a list of the data sources available from Open Exchange Rates:

 

Open Exchange Rates Query component in Matillion ETL for Amazon Redshift - Data Source

 

After choosing the data source, the next step is to choose the desired columns from the data source in the Data Selection. This will form the new table which is created in Amazon Redshift.

 

Open Exchange Rates Query component in Matillion ETL for Amazon Redshift - Data Selection

 

Data Source Filter

You can additionally add a filter to the data you are bringing through into Amazon Redshift using the Data Source Filter.

 

Open Exchange Rates Query component in Matillion ETL for Amazon Redshift - Data Source Filter

 

Note: The “Limit” setting is applied before the Data Source Filter is applied.

Connection Options

The component has additional connection parameters you may want to explore. However, none of the Connection Options are mandatory and the Open Exchange Query component usually gives you sensible defaults. Should you wish to explore your Connection Options, further details on the options are available here.

Data Target

Next, you configure how you would like to load the selected data into Amazon Redshift.

 

Open Exchange Rates Query component in Matillion ETL for Amazon Redshift - Data Target

 

Specify a Target Table in which to load the data from Open Exchange Rates:

 

Open Exchange Rates Query component in Matillion ETL for Amazon Redshift - Target Table

 

Specify an S3 Staging Area in which to stage the data before loading into the defined Target Table in Amazon Redshift.

 

Open Exchange Rates Query component in Matillion ETL for Amazon Redshift - S3 staging area

 

Basic/Advanced Mode

The Open Exchange Rates Query component offers an “Advanced” mode instead of the default “Basic” mode. In Advanced mode, you can write a SQL-like query over all the available fields in the data model. This is automatically translated into the correct API calls to retrieve the data requested.

 

Open Exchange Rates Query component in Matillion ETL for Amazon Redshift - Basic Advanced Mode

 

Once you set Advanced mode, a SQL Query option becomes available and which you can update with your desired SQL Query:

 

Open Exchange Rates Query component in Matillion ETL for Amazon Redshift - SQL Query

 

Open Exchange Rates Query component in Matillion ETL for Amazon Redshift - SQL Query 2

 

Running the Open Exchange Rates Query component in Matillion ETL for Amazon Redshift

Once you have configured the minimum required properties, the outline color of the component will change to Green. At this point, you may test the component by right-clicking on the component and choose Run Component.

 

Open Exchange Rates Query component in Matillion ETL for Amazon Redshift - Run component

 

This will execute just that component and load any available data into the specified target table in Amazon Redshift.

Transforming the Data

Once you have brought through the required data from the Open Exchange Rates Query component into Amazon Redshift, you can use the data in a Transformation job to join with other data in Amazon Redshift.

 

Open Exchange Rates Query component in Matillion ETL for Amazon Redshift - transform data

 

Open Exchange Rates Query component in Matillion ETL for Amazon Redshift - Transformation

 

Useful Links

Want to try the Open Exchange Rates Query component in Matillion ETL for Amazon Redshift? Arrange a free demo, or start a 14-day free trial.