Overview

This page explains how to set up a connection in Looker to Google BigQuery Legacy SQL or Google BigQuery Standard SQL.

The general steps for setting up a Google BigQuery Legacy SQL or Google BigQuery Standard SQL connection are:

1. Create a service account with access to the Google project and download the JSON credentials certificate.
2. Create a temporary dataset for storing persistent derived tables.
3. Set up the Looker connection to your database.
4. Test the connection.

Create a Service Account and Download the JSON Credentials Certificate

Google has documentation on creating a service account and generating a private key.

1. Open the credentials page in the Google Cloud Platform API Manager and, if necessary, select your project:

   

2. Click Create credentials and choose Service account key:

   

3. Select New service account from the Service account drop-down:

   

4. Fill out the form as follows:

   • Service account name: Name the account something like “Looker service account”.
   • Key type: Select JSON.

   • Role:

     • If your Google administrator is using default roles as defined here, select both of the following roles:
       • BigQuery > BigQuery Job User
       • BigQuery > BigQuery Data Editor

     • If your Google administrator has defined custom roles, these specific capabilities are required:

       • For the project Looker will be working on: List/get tables, Read tables/metadata, List/get datasets

       • For the datasets Looker will be running queries on: List/get tables, Read tables/metadata, Create jobs/queries, Cancel jobs

       • For the scratch dataset where PDTs will be written: List/get tables, Read tables/metadata, Modify/delete tables, Create jobs/queries, Cancel jobs

   The form appears as follows:

   

5. Click Create.

6. Download the JSON key. BE SURE TO REMEMBER WHERE YOU DOWNLOADED IT.

   

7. After noting the download location, click CLOSE.

8. Find the email address corresponding to the service account. You will need this to configure the Looker connection to BigQuery.

   

Create a Temporary Dataset for Persistent Derived Tables

1. Open the BigQuery console and, if necessary, switch projects. Click the down arrow next to your project name and select Create new dataset:

   

2. Give the dataset an ID (typically looker_scratch) and click OK:

   

Set Up the Looker Connection

In the Admin section of Looker, navigate to the Connections page and click New Connection. Looker displays this page:

Fill out the page as follows:

• Name: The name for the connection, which can be anything you want. This is how you will refer to it in the model file.
• Dialect: Select Google BigQuery Standard SQL or Google BigQuery Legacy SQL.

• Project Name: The Google project ID.

  Please be sure to enter your project ID here, and not your project name.

• Dataset: The name of the default dataset that you plan to use. If a table doesn’t have a dataset specified, then it is assumed to be in this dataset. (You can also model other datasets in this project.) This must match the name of a dataset in your BigQuery database.

• Username: The username of the account you use to log into BigQuery.
• OAuth Credentials Email: The email address for the service account, as you verified in an earlier step.
• OAuth Credentials JSON File: The certificate file for the service account you downloaded in an earlier step.
• Password: The password of the account you use to log into BigQuery.
• Temp Dataset: The BigQuery dataset you created earlier for writing persistent derived tables.
• Max Billing Tier: Leave blank for no limit. See here for more detail.
• Max Connections: Can be left at the default value initially. Read more about this setting here.
• Connection Pool Timeout: Can be left at the default value initially. Read more about this setting here.
• SQL Runner Precache: To cause SQL Runner not to pre-load table information and to load table information only when a table is selected, uncheck this option. Read more about this setting here.
• Database Time Zone: The default time zone for BigQuery is UTC. The time zone setting you specify here needs to match your BigQuery time zone setting.

Test the Connection

Click Test These Settings to verify that the connection was set up correctly.

If you see Can connect, then press Add connection. This runs the rest of the connection tests to verify that the service account was set up correctly and with the proper roles.

Feature Support

Looker’s ability to provide some features depends on the database dialect’s ability to support that functionality.

In the current Looker release, Google BigQuery Legacy SQL supports the following Looker features:

In the current Looker release, Google BigQuery Standard SQL supports the following Looker features:

Next Step

After you have connected your database to Looker, you’re ready to configure sign-in options for your users.