Now in BigQuery: batch queries and a connector for Excel

By Ryan Boyd, Developer Advocate for Cloud Data Services

Businesses and developers are using BigQuery to solve a wide variety of use cases – from optimizing advertising campaigns, to spotting inventory shortfalls, to understanding customer behavior. Accommodating these varied use cases requires BigQuery to be flexible, both for the developers integrating applications with the API and for the analysts running ad-hoc queries. Today we’ve made it more flexible by adding batch queries and a connector for Microsoft Excel.

Batch priority queries

BigQuery was designed for ad-hoc, iterative analytics on millions-to-billions of rows of data. When you’re diving into your data to gain insights, you want your queries to run in seconds rather than waiting minutes or hours. Sometimes our customers don’t need these fast responses when they’re running nightly jobs to update dashboards or reports, but want to use the same BigQuery technology and underlying datasets for these queries. We’ve now added batch pricing to accommodate these developers, allowing them to run their queries at a significantly lower cost.

Here’s how to set the priority to ‘batch’ when submitting a new query via the Google APIs Client Library for Java:

    Job job = new Job();
    JobConfiguration config = new JobConfiguration();
    JobConfigurationQuery queryConfig = new JobConfigurationQuery();
    config.setQuery(queryConfig);

    job.setConfiguration(config);
    queryConfig.setQuery(querySql);
    queryConfig.setPriority("BATCH");

    com.google.api.services.bigquery.Bigquery.Jobs.Insert insert =
     bigquery.jobs().insert(projectId, job);

Batch queries will execute between 30 minutes and 3 hours after they are submitted. See more information in our Developers Guide.

BigQuery Connector for Excel

Spreadsheets are a popular tool for analysts, executives and and developers to explore data. Last year we launched the ability for users of Google Spreadsheets to execute BigQuery queries using the Google Apps Script integration. Today, we’re launching the BigQuery Connector for Excel, which allows Microsoft Excel users to do the same with the ‘External Data’ functionality built into the product. Once the BigQuery results are in Excel, you can easily make pivot tables, create charts and integrate it with data from other sources. If you’re interested, you can try it right now!

Let us know what you think of these new features and what else you’d like to see in the roadmap by reaching out on Google+. We’ll also be holding office hours this Friday at 10 AM PDT on Google Developers Live to talk about these new features and answer any questions you have about BigQuery.

Microsoft and Excel are registered trademarks of Microsoft Corporation


Ryan Boyd is a Developer Advocate, focused on cloud data services. He's been at Google for 6 years and previously helped build out the Google Apps ISV ecosystem. He published his first book "Getting Started with OAuth 2.0" with O'Reilly.

Posted by Scott Knaster, Editor