Overview

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

The general steps for setting up a Google BigQuery Standard SQL or Google BigQuery Legacy 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

You must have Google Cloud admin permissions to create a service account. 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:

   

3. Enter a name for the new service account, optionally add a description, and click CREATE:

   

4. Your service account requires two Google BigQuery predefined roles:

   • BigQuery > BigQuery Data Editor
   • BigQuery > BigQuery Job User

   Select the first role in the Select a role field, then click ADD ANOTHER ROLE and select the second role:

   

   After selecting both roles, click CONTINUE:

   

5. Click CREATE KEY:

   

6. Select JSON and click CREATE:

   

7. The JSON key will be saved to your computer. BE SURE TO REMEMBER WHERE IT IS SAVED. After noting the download location, click CLOSE:

   

8. Click DONE:

   

9. 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.

• 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: If you are using a legacy .p12 credentials file, enter the password to the .p12 credentials file. If using a JSON credentials file, leave this field empty.
• 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 preload 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 whether the database dialect can support them.

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.