User Guide Help Center Documentation User Forums Training
New LookML
Old LookML
New LookML
Google BigQuery Legacy SQL and Google BigQuery Standard SQL


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 .p12 credentials certificate.
  2. Change the password for the .p12 certificate.
  3. Create a temporary dataset for storing persistent derived tables.
  4. Set up the Looker connection to your database.
  5. Test the connection

Create a Service Account and Download the .p12 Credentials Certificate

Google has documentation on creating a service account that you can refer to here.

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

  2. Click Create credentials and choose Service account key:

  1. Select New service account and fill out the form as follows:

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

      • If your Google administrator is using default roles as defined here:
        • Select BigQuery > BigQuery User.
        • If you are using PDTs, select BigQuery > BigQuery Data Editor.
        • If you aren’t using PDTs, select either BigQuery Data Viewer or 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: 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:

  2. Click Create.


  4. After noting the download location, click Close.

  5. Find the email address corresponding to the service account. You will need this in configuring the Looker connection to BigQuery.

    • Select Manage service accounts.

    • In the Service accounts list, find the Service account ID associated with the Service account name you chose in an earlier step.

Change the .p12 Certificate Password

The .p12 certificate downloaded for the service account is in PKCS #12 format and encoded with the default password notasecret. For security, Looker recommends changing this.

Here are instructions for changing the password using openssl, a command line utility. There are two steps: creating a temporary password and then creating the final password.

Step 1: Creating a Temporary Password

$ openssl pkcs12 -in MyProject-3f024e3d05b5.p12 -out temp.pem Enter Import Password: (Enter notasecret) MAC verified OK Enter PEM pass phrase: (Enter your temporary password) Verifying - Enter PEM pass phrase: (Reenter your temporary password)

Step 2: Creating the Final Password

$ openssl pkcs12 -export -in temp.pem -out MyProject-3f024e3d05b5.p12 Enter pass phrase for temp.pem: (Enter your temporary password) Enter Export Password: (Enter your final password) Verifying - Enter Export Password: (Reenter your final password)

Step 3: Remove the Temporary PEM File

Now you should remove the temporary PEM file you created: srm temp.pem

Create a Temporary Dataset for Persistent Derived Tables

  1. Open the BigQuery console and, if necessary, switch projects:

  1. Click the down arrow next to My Project and select Create new dataset:

  1. Give the dataset a name (typically looker_scratch).

  2. Uncheck Expire new tables in one day:

Set Up the Looker Connection

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

Fill out the page as follows:

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.