Blog| Matillion ETL for BigQuery

Using the Open Exchange Rates Query component in Matillion ETL for BigQuery

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 in Matillion ETL for BigQuery presents an easy-to-use graphical interface, enabling you to pull data from the Open Exchange Rates API directly into BigQuery. 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 BigQuery 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 BigQuery

 

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.

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

 

Open Exchange Rates Query component in Matillion ETL for BigQuery - Authentication

 

Data Source

Next, you can choose the data you want to load into BigQuery 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 BigQuery - 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 BigQuery.

 

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

 

Data Source Filter

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

 

Open Exchange Rates Query component in Matillion ETL for BigQuery - Data 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 BigQuery.

 

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

 

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

 

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

 

Specify a Cloud Storage Staging Area in which to stage the data before loading into the defined Target Table in BigQuery.

 

Open Exchange Rates Query component in Matillion ETL for BigQuery - Cloud Storage

 

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 BigQuery - Advanced Mode

 

Once you set the component to Advanced, a SQL Query option becomes available and you will be able to update it with your desired SQL Query:

 

Open Exchange Rates Query component in Matillion ETL for BigQuery - Advanced SQL

 

Open Exchange Rates Query component in Matillion ETL for BigQuery - SQL Statement

 

Running the Open Exchange Rates Query component in Matillion ETL for BigQuery

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. This will execute just that component and load any available data into the specified target table in BigQuery.

 

Open Exchange Rates Query component in Matillion ETL for BigQuery - Run Component

 

Transforming the Data

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

Open Exchange Rates Query component in Matillion ETL for BigQuery - Transform Data

 

Open Exchange Rates Query component in Matillion ETL for BigQuery - Sample Data

 

Useful Links

Open Exchange Rates Query in Matillion ETL for BigQuery
Open Exchange Rates Data Model
Open Exchange Rates 3rd Party OAuth

 

Want to try the Open Exchange Rates Query component in Matillion ETL for BigQuery? Request a free 1-hour demonstration, or start a 14-day free trial.