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

   

3. 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:

   

4. Click Create.

5. Download the .p12 key. BE SURE TO REMEMBER WHERE YOU DOWNLOADED IT.

   

6. After noting the download location, click Close.

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

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

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

4. 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:

• 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 Legacy SQL or Google BigQuery (Standard SQL).

• Project Name - The Google project ID.

  NOTE: 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.)
• OAuth Credentials Email - The email address for the service account, as you verified in an earlier step.
• OAuth Credentials P12 File - The certificate file for the service account you downloaded in an earlier step.
• Password - The password you created earlier for the P12 file.
• 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.

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.