📦Backup your data using BigQuery
Objective: in this guide we will learn how to configure BigQuery in order to create daily data backups. This might prove useful if you want to keep an history of your tools data, like your CRM or finance software.
Prerequisite: in oder to follow this guide, you will need to have an admin access to the google.
Backup a table using BigQuery :
1. Enable the BigQuery data transfer API
The first thing we will need to do is enable the BigQuery data transfer API. This allows to schedule queries on a regular basis.
In order to activate the service:
Head over to the API page https://console.cloud.google.com/apis/api/bigquerydatatransfer.googleapis.com/metrics
Click on "Enable API"
There might be some propagation delay before this service is seen as activated for Google BigQuery
2. Create a new dataset in BigQuery
Now we will need to create a new dataset in BigQuery:
Open the BigQuery Console
Create a new dataset in your desired project
Give it a name, for example if we want to backup our
jaffle_shop
dataset, we will name itjaffle_shop_backup
Set the region to the same one as your initial table
Click on create
Create a table in the new dataset
Give it a name, for example if we want to backup our
customer
table, we will name itcustomer_history
Click on create
3. Write the backup query
Let's write the query that we will use to backup our table:
In the BigQuery console, open the table you want to backup
Click on "Query"
You should now see a Query looking like the following
Edit it in order to remove the limit, select every columns in our table and add a new column named
snapshot_ts
that will be populated with the backup timestamp. The final query should look like
4. Schedule the backup
Now we will add our query to the scheduler:
Click on Schedule -> Create a new scheduled query
Give it a name
Set the schedule according to your needs
Set the dataset and table to the ones created during step 2
Set Append to table under Destination table write preference: this will tell BigQuery to create a full backup of the table each day
Click on save 💾
5. Verify the query execution
Now that our query is scheduled, we should check that it's running correctly:
Open the schedule query panel from the left menu bar: https://console.cloud.google.com/bigquery/scheduled-queries?project=whaly-temp-migration-guide
Check that your query is listed in the summary table
If you see any error, follow google debugging info to fix them
Last updated