Posted by Tino Tereshko, BigQuery Technical Program Manager
Google BigQuery is a serverless fully-managed analytics data warehouse that frequently delivers features and upgrades without any downtime or burden on the user. For enterprise customers, the BigQuery team strives to deliver features that improve user productivity and interoperability, and make BigQuery even easier to use.
Today, the BigQuery team is announcing integration with Google Drive. You can now:
- Save query results directly to Google Sheets from the BigQuery UI
- Query files directly from Google Drive, without first loading them into BigQuery.
- Query Google Sheets spreadsheets from BigQuery as you edit them in Sheets!
Halfbrick Studios, makers of the popular mobile game Fruit Ninja, received a sneak peek of the new feature. Sam Gillespie, a data analyst from Halfbrick, said, “We can now combine the flexibility of Google Sheets with the raw power of BigQuery to share results and insights even faster. By linking a Google Sheet directly into BigQuery, we can update our data with new information using a familiar friendly interface. This feature allows us to learn from our data even faster and helps get data from analysts to our teams no matter where they are."
Use your Google Spreadsheets as tables in BigQuery
You can create tables in BigQuery that reference your Google Sheets spreadsheets.
I use Google Sheets to keep track of musicians I’m currently interested in.
Quality of my musical tastes aside, I would prefer to get a list of the most popular songs by these artists based on public playlist data in BigQuery. I happen to change my preferences quite frequently. I want it that way.
I use BigQuery’s new table create UI to define a BigQuery table that actually reads my Google Sheets spreadsheet of preferred artists.
Now that the Sheets-backed table is defined, I can query this table against a list of playlists to find out the most popular songs.
Suppose we just want to have fun, and break our promise to never give up on Rick Astley in favor of Cyndi Lauper. We simply update our Google Sheets spreadsheet.
And we rerun the SQL query in BigQuery. Since the table “artists” reads directly from our spreadsheet, our preference for Cyndi Lauper is registered in BigQuery seamlessly.
Time after time, we can make changes within our Google Sheets spreadsheet, and BigQuery will automatically pick up the changes next time we run a query against the spreadsheet!
Save query results to Google Sheets
All users should see a “Save to Google Sheets” button in the BigQuery user interface. Clicking on this button will save query results to a Google Sheet and will prompt you to open that Google Sheet.
Aside from BigQuery - Google Drive integration, we announced a number of other updates at GCP NEXT, including:
To read more about these features, take a look at the Federated Data Sources documentation.
- Dropping the price of Long Term Storage by 50%
- The Capacitor storage engine, which accelerates many queries 10x or more
- The Poseidon data ingest and egress engine, which accelerates data loads by 5x
- Alpha of Table Partitions
As always, Google BigQuery brings you these features seamlessly — without any downtime and without any user action or configuration. This is the way fully managed is meant to be.