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.
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.
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:
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.
Data Source Filter
You can additionally add a filter to the data you are bringing through into BigQuery using the Data Source Filter.
Note: The “Limit” setting is applied before the Data Source Filter is applied.
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.
Next, you configure how you would like to load the selected data into BigQuery.
Specify a Target Table in which to load the data from Open Exchange Rates:
Specify a Cloud Storage Staging Area in which to stage the data before loading into the defined Target Table in BigQuery.
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.
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:
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.
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.