How to load Open Exchange Rates into BigQuery with OWOX Data Marts
Open Exchange Rates to Google BigQuery in OWOX Data Marts: copy an App ID, create a Data Mart, choose the Open Exchange Rates connector, paste the App ID and publish. Then add a trigger and the rates refresh on their own.
Ad platforms bill in different currencies, and a spend report that adds dollars to euros is wrong before anyone reads it. The fix is a table of daily exchange rates in Google BigQuery that your SQL can join to.
OWOX Data Marts loads that table from Open Exchange Rates and keeps it current. You need one App ID, which Open Exchange Rates gives out on its free plan. This guide walks through the setup on OWOX Cloud.
What you need
- An OWOX Cloud account. Sign up if you do not have one.
- A Google BigQuery storage in OWOX Data Marts. Add it under Storages, or create it while you set up the Data Mart. More on Google BigQuery as a storage.
- An Open Exchange Rates account. The free plan is enough to start.
Step 1: Get the App ID
Sign in or sign up at openexchangerates.org.
Open App IDs in the left-hand menu.
Enter a name and click Generate New App ID. Copy the App ID.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
Step 2: Create the Data Mart
Click New Data Mart, give it a title, choose your Google BigQuery storage and click Create Data Mart. If you have no storage yet, pick Create new storage in the same dropdown.
Step 3: Choose the Open Exchange Rates connector and enter the credentials
Step 3: Choose the Open Exchange Rates connector and enter the credentials
On the Data Setup tab, set Input Source to Connector and click Setup Connector. Choose Open Exchange Rates in the list and click Next.
Fill in the settings:
- App ID: the one you just copied.
- Currency Symbols: optional. Enter the currency symbols you need, such as GBP. The list of symbols is in the Open Exchange Rates documentation.
Rates are quoted against the US dollar by default.
Step 4: Choose what to load
There is one thing to load here: historical exchange rates, one row per currency per day.
Keep the default fields or add more. The key fields stay selected.
Name the dataset and the table. Both are created on the first run if they do not exist yet.
Click Save.
Step 5: Publish and run
Click Publish & Run Data Mart. The first run loads rates from the first day of the previous month.
To load older history, use Manual Run on the same screen and choose the dates. Run History shows every run and its status.
Step 6: Keep it fresh
Open Triggers, add a trigger, choose Connector Run and set how often it runs: daily at a time and time zone you pick, weekdays, hourly or every few hours. Click Create trigger.
From here the table updates on its own.
What to do with the data
Join the rates to your spend tables on date and currency, and convert every amount to one currency before you sum it. With rates in the same dataset as your ad data, that is one join in the query that builds your report.
OWOX Data Marts loads the rates and publishes what you build on them. The conversion SQL stays yours, so you decide which rate applies to which day.
Not on BigQuery?
The same connector loads into the other storages OWOX Data Marts supports: Snowflake, AWS Redshift, AWS Athena and Databricks.
OWOX Data Marts is also open source. If you run it yourself, the same App ID works.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
Is an Open Exchange Rates App ID free?
Open Exchange Rates has a free plan, and its App ID is enough to start loading rates. Limits and paid plans are listed on the Open Exchange Rates site.
Which currencies are loaded?
Enter the symbols you need in the Currency Symbols setting, such as GBP. The list of supported symbols is in the Open Exchange Rates documentation.
Is the Open Exchange Rates to BigQuery connector free?
The connector is open source and free to run yourself with the open-source edition of OWOX Data Marts. You pay Google for BigQuery storage and queries. The same connector is available on OWOX Cloud, where there is no server to maintain.
Where does the data get stored in BigQuery?
In the dataset and table you name in the last setup step. Both are created on the first run if they do not exist yet.
Is the data import limited to USD as the base currency?
USD is the default base currency. Which base currencies you can use depends on your Open Exchange Rates plan. You can import conversion rates to any supported currency.
How often do the exchange rates refresh?
As often as the trigger you add on the Triggers tab: daily at a time you choose, on weekdays, hourly or every few hours. You can also start a run by hand at any time with Manual Run.
