Accessing and Querying Historical Data in BigQuery

Aug 26

Welcome to Notes from BenDesk: Ben is our resident Freshdesk captain and manager of all help@ inquiries.We'rebringing you interesting inquiries from his inbox each month to help share learnings across our community.

Question of the Month: Is it possible to access and query historical data in BigQuery?

Bendesk Answer: With BigQuery, you have a few ways to query historical data that was previously available in your project!

Why is this feature useful? For instance, if you want to see how many rows were in a table yesterday or track changes to a specific row over the past week, you can use historical querying to find out! Just remember, this feature only works for data from the last 7 days.

1. Using System Time Travel: BigQuery’s System Time Travel feature allows you to access data from any point in the last 7 days without making permanent changes to the table. It’s a quick and easy way to analyze historical data if you need to adjust and refine your queries on the fly. To use it, simply specify a timestamp like this:

SELECT *
FROM `mydataset.mytable`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY);

2. Using gcloud CLI: The gcloud command-line tool lets you restore a table from a specific point in time. Currently, this feature is available only through the command line. To create a new table from a backup with a one-hour offset, you can use the following command:

bq cp mydataset.mytable1@-3600000 mydataset.table1_restored

Alternatively, you can specify a Unix timestamp in milliseconds for a fixed point in time:

bq cp mydataset.table1@1624046611000 mydataset.table1_restored

3. Contacting CTA Support: If you’re having issues and need help accessing historical data, CTA is here to help! CTA has internal processes built to roll back a BigQuery dataset or table to a time within the last 7 days. All you need to do is contact help@techallies.org to get that process started!

For more details on accessing historical data, you can take a look at Google’s documentation here.

Ben Deverman