Edit on GitHub
Report issue
Page history

Connect to BigQuery from Microsoft Excel using ODBC

Author(s): @tswast ,   Published: 2017-01-26

Tim Swast | Developer Programs Engineer | Google

Contributed by Google employees.

BigQuery enables standard SQL queries to petabytes of data. But what if software that you depend on doesn't use the BigQuery API? The BigQuery ODBC drivers enable you to connect tools, such as Microsoft Excel, that use the Open Database Connectivity (ODBC) API to BigQuery.

Objectives

  • Installing the ODBC driver for BigQuery
  • Configuring the ODBC driver
  • Loading query results into Microsoft Excel

Before you begin

This tutorial assumes that you're using the Microsoft Windows operating system.

  1. Create a project in the Cloud Console.

  2. See the blog post on getting started with the BigQuery free tier or video for more detailed instructions.

  3. Install Microsoft Excel 2016 for Windows.

Costs

This tutorial uses billable components of Google Cloud, including BigQuery.

Use the Pricing Calculator to estimate the costs for your usage.

The first 1 TB per month of BigQuery queries are free. See the BigQuery pricing documentation for details about on-demand and flat-rate pricing. BigQuery also offers controls to limit your costs.

Downloading the driver

  1. Check whether your version of Excel is 32-bit or 64-bit.
  2. Download the latest version of thevODBC driver from the Simba Drivers for BigQuery page that matches your version of Excel.
  3. Run the ODBC driver installer.

The installer writes a user guide to the installation directory (for example, C:/Program Files/Simba ODBC Driver for Google BigQuery), which contains more detailed instructions about how to configure the driver. These instructions are also available in the Simba ODBC installation and configuration guide.

Configuring the driver

  1. Run the ODBC Data Sources Administrator program.

    Run ODBC data sources administrator program

  2. Select the User DSN tab.

  3. Click the Add button.

    Click add

  4. Select the Simba ODBC driver for Google BigQuery from the data source dialog box and click the Finish button.

    Select driver

  5. Set a Data source name (DSN), such as "BigQuery", "BigQuery64", or "BigQuery32".

    Set a data source name

Authenticating the driver

  1. Choose User authentication in the OAuth mechanism selection box.

    Choose User authentication

  2. Click the Sign in button.

  3. Grant the ODBC driver permissions to run queries on your behalf by clicking the Allow button.

    Click allow

  4. Copy the authorization code to your clipboard.

    Copy the code

  5. Paste the code into the Confirmation code text box.

    Paste the code

  6. Click the Refresh token text box. The ODBC driver will automatically fill in this text box by making an API request containing the confirmation code you provided.

    Get a refresh token

Configuring advanced options

  1. Click Advanced options to configure the ODBC driver further.

  2. If the BigQuery Storage API is enabled on the billing project used by the driver, check High-throughput API box for better performance when downloading query results.

  3. Add a comma-separated list of projects in the Additional projects text box to view datasets in projects outside of the billing account, such as the Google Cloud public datasets hosted in the bigquery-public-data project.

  4. Click OK to finish configuring advanced options.

Advanced ODBC options

Finish configuring the driver

  1. Choose the Google Cloud project ID to use as the billing project for queries by clicking the arrow on the Catalog (project) selection box.

    Choose a billing project

  2. Click OK to finish configuring the driver.

Running a query

After the ODBC driver is configured, open Excel.

Opening the query dialog

  1. Go to the Data tab.
  2. Select New Query > From Other Sources > From ODBC.

    Query from ODBC in Excel screenshot

  3. Choose Google BigQuery as the data source.

  4. Don't supply a username or password. Instead, select the connection type tab for Default or Custom.

Entering a query

  1. Select Advanced Options.
  2. Enter your query in the SQL statement text box.

    Enter SQL statement screenshot

    As an example, query the USA names public dataset for the most popular names during the baby boomer generation.

    #standardSQL
    SELECT
      name, SUM(number) as total_number
    FROM
      `bigquery-public-data.usa_names.usa_1910_2013`
    WHERE
      year >= 1946
      AND year <= 1964
    GROUP BY
      name
    ORDER BY
      total_number
    DESC
    
  3. Click OK. When the query completes, you will have a new sheet with about 7,400 rows.

Next steps

With the driver you installed, you can connect any application which supports the ODBC API to Google BigQuery.

Submit a tutorial

Share step-by-step guides

Submit a tutorial

Request a tutorial

Ask for community help

Submit a request

View tutorials

Search Google Cloud tutorials

View tutorials

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.