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.

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

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.

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.

1. Check whether your version of Excel is 32-bit or 64-bit.
2. Download the latest version of the ODBC 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.

1. Run the ODBC Data Sources Administrator program.

   

2. Select the User DSN tab.

3. Click the Add button.

   

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

   

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

   

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

   

2. Click the Sign in button.

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

   

4. Copy the authorization code to your clipboard.

   

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

   

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.

   

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.

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.

   

2. Click OK to finish configuring the driver.

After the ODBC driver is configured, open Excel.

1. Go to the Data tab.

2. Select New Query > From Other Sources > From ODBC.

   

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.

1. Select Advanced Options.

2. Enter your query in the SQL statement text box.

   

   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.

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

• Follow the BigQuery quickstart to explore the web UI and write your own queries.
• Explore more BigQuery public datasets.
• Learn how to load your own data into BigQuery.
• Reference the Simba ODBC driver installation and configuration guide.