title: Connecting to BigQuery from Microsoft Excel using ODBC description: Learn how to use the ODBC drivers for Google BigQuery to load query results into Microsoft Excel for analysis and visualization. author: tswast tags: BigQuery, Excel, ODBC date_published: 2017-01-26
Google BigQuery enables standard SQL queries to petabytes of data. But what if software you depend on doesn't support the BigQuery API? The BigQuery ODBC drivers enable you to connect tools that support the Open Database Connectivity (ODBC) API to BigQuery, such as Microsoft Excel.
- Installing the ODBC driver for Google BigQuery.
- Configuring the ODBC driver
- Loading query results into Microsoft Excel®
Before you begin
This tutorial assumes you are using the Microsoft Windows operating system.
Create a project in the Google Cloud Platform Console.
- Install Microsoft Excel 2016 for Windows.
This tutorial uses billable components of Cloud Platform including Google 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 more details about on-demand and flat-rate pricing. BigQuery also offers controls to limit your costs.
Downloading the driver
- Check whether your version of Excel is 32-bit or 64-bit.
- Download the latest version of the ODBC driver from the Simba Drivers for Google BigQuery page which matches your version of Excel.
- Run the ODBC driver installer.
Configuring the driver
- Run the ODBC Data Sources Administrator program as the Windows administrator.
- Select the System DSN tab.
- Configure the BigQuery driver.
- Provide credentials with user authentication. Follow the prompts to log in and authorize the driver to access the BigQuery API.
- Set the Project (Catalog) to your Google Cloud project ID.
Note that the installer writes a user guide to the installation directory (in
C:/Program Files/Simba ODBC Driver for Google BigQuery) which
contains more detailed instructions about how to configure the driver.
Running a query
Once the ODBC driver is configured, open Excel.
Opening the query dialog
- Go to the Data tab.
- Select New Query -> From Other Sources -> From ODBC.
- Choose Google BigQuery as the data source.
- Don't supply a username or password. Instead, select the connection type tab for Default or Custom.
Entering a query
- Select Advanced Options.
Enter your query in the SQL statement text box.
#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
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.