Connect Excel to Google BigQuery [Mac]

Download the ODBC/JDBC driver for Mac OS

1. Download the macOS (dmg).
   

   

   
   
   
   
   Troubleshooting

   

   If you run into the "SimbaODBCDriverforGoogleBigQuery.pkg can't be opened because it is from an unidentified developer" error, go through these following steps. 

   
   Press OK to close out of the error popup. 

   

   Use command(⌘) + Space to bring up Spotlight and search Security & Privacy.
   

   

   
   
   Go to the General tab and click Open Anyway.
   

   

   
   
   
   Open it. You ARE sure you want to open it. Please, don't doubt me ever again. 

   

2. Once you have SimbaODBCDriverforGoogleBigQuery installer up, go through the installation process and choose all the default settings. 

   

   
   
   
3. Close the installer when finished. 
   

   

Download ODBC Manager app

ODBC Manager will be used to set up the Simba ODBC driver and set up a DSN

1. Download the ODBC Manager. 
   

   

   
   
   
2. Once you have ODBC Manager installer up, go through the installation process and choose all the default settings. 
   

   

   
   
   
3. Close the installer when finished. 
   

   

Download iODBC Manager app

iODBC Manager is used by Excel to access the ODBC drivers.
iODBC Manager seems to have issues setting up the ODBC driver and DSN so both of these ODBC manager apps (ODBC Manager and iODBC Manager) are necessary.

1. Download the mxkozzzz.dmg package.
   

   

   
   
   
2. Once you have iODBC Manager installer up, go through the installation process and choose all the default settings.
   

   

   
   
   
3. Close the installer when finished.
   

   

Adding a Driver to ODBC Manager

1. Use command(⌘) + Space to bring up Spotlight and search for ODBC Manager.
   

   

   
   
   
2. Go to the Drivers tab
   1. The Simba ODBC Driver for Big Query should automatically be added into your drivers
      

      

      
      

Troubleshooting

If the Simba ODBC Driver for BigQuery isn't automatically added, then we need to do some tweaking. Lettuce start. 

1. Download the simba.zip file and then unzip it to get a folder called simba with a subfolder, googlebigqueryodbc. 
   simba.zip
   
   
2. Copy the googlebigqueryodbc folder and replace the folder googlebigqueryodbc in /Library/simba.
   How to get to Library directory: command(⌘) + shift + G, type /Library/simba, then press Go
   
   

   

   
   
   

3. Use command(⌘) + space to bring up Spotlight and search for Terminal.
   And use this command to open the file obdcinst.ini:

   Open obdcinst.ini

   BASH

   sudo nano /Library/ODBC/odbcinst.ini

   

   
   
   It should look like this: 

   

   
   
   

4. Use your arrow keys to navigate through the file. 
   Copy/paste this under the ODBC Drivers section.

   Simba Driver Installation Definition

   BASH

   Simba ODBC Driver for Google Bigquery = Installed

   
   Copy/paste this at the end of the file.

   Simba Driver Path

   CODE

   [Simba ODBC Driver for Google Bigquery]
   Driver = /Library/simba/googlebigqueryodbc/lib/libgooglebigqueryodbc_sbu.dylib
   Setup  =

   
   It should look like this: 

   

   
   
   
5. Save the file using the commands:
   Try to exit file: control(⌃) + X
   Confirm Yes to save changes: Y
   Exit: Enter(↵)

Get a Refresh Token

1. Go to BigQuery Client Tools, Allow access, and copy your BigQuery code. 
2. Open Terminal
   1. command(⌘) + space to bring up Spotlight and search for Terminal

   2. Paste this command into Terminal, replace BIGQUERY_CODE with your's that you got from step 1 and press enter.

      Make New Refresh Token

      BASH

      sh /Library/simba/googlebigqueryodbc/Tools/get_refresh_token.sh "BIGQUERY_CODE"

      

      

      Make sure your BIGQUERY_CODE is wrapped with double quotes, " ".

   3. Copy the refresh token that is returned into a word document because we're about to use it soon.

Adding a System DSN in ODBC Manager

1. Use command(⌘) + Space to bring up Spotlight and search for ODBC Manager.
   

   

   
   
   
2. Go to System DSN tab.
   Click the Add button on the right-hand side. 
   

   

3. Choose the driver Simba ODBC Driver for Google BigQuery 
   Click OK
   

   

   
   
   
4. Data Source (DSN) = bigquery
   In Keyword/Value section on the bottom, click Add button to populate the following rows.
   
   Catalog = pmg-datawarehouse
   RequestGoogleDriveScope = 1
   SQLDialect = 1
   OAuthMechanism = 1
   RefreshToken = REFRESH_TOKEN you got earlier...
   

   

Connect BigQuery to Excel

1. Open Excel
2. Click on the Data tab
   

   

   
   
   
3. Click on New Database Query then
   

   

   

4. In the iODBC Data Source Chooser that appears, select the System DSN tab.
   Select the System Data Source named bigquery
   Click OK
   
   

   

5. Enter your PMG Google/Gmail email address as the username.
   Enter your PMG Google/Gmail email address as the password.
   
   

   

   
   
   

6. In the Microsoft Query window that appears, paste your SQL code then click Run button to see results.
   

   Test SQL Code

   SQL

   select * from playground.bigquery_odbc_test;

   
   

   

   
   
   

7. Click on Return Data to bring data into Excel workbook
   

   

   
   

Fin.

Advanced Driver Options

For advanced driver options, see: https://www.simba.com/products/BigQuery/doc/ODBC_InstallGuide/mac/content/odbc/bq/options/ui/additionalprojects.htm

A few options that may be useful:

• "AllowLargeResults" - however, it is enabled by default when "SQLDialect" is set to 1
• "AdditionalProjects" - allows for access to multiple projects
• "UseQueryCache" - uses cached version of data when available, may be useful for optimizing SQL in Excel spreadsheets