Google Cloud Big Data and Machine Learning Blog

Innovation in data processing and machine learning technology

BigQuery integrates with Google Drive

Friday, May 6, 2016

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.



To read more about these features, take a look at the Federated Data Sources documentation.

Aside from BigQuery - Google Drive integration, we announced a number of other updates at GCP NEXT, including:
  • 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.

  • Big Data Solutions

  • Product deep dives, technical comparisons, how-to's and tips and tricks for using the latest data processing and machine learning technologies.

  • Learn More

12 Months FREE TRIAL

Try BigQuery, Machine Learning and other cloud products and get $300 free credit to spend over 12 months.

TRY IT FREE

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.