How to create a BigQuery connection in Looker

[kosalan]

Step 1: 

Create a Google Cloud Project, Big Query Project and Dataset. This document does not cover how to do that. However, if you need help please refer to the documentation here. (https://cloud.google.com/bigquery/docs). Once this is configured you can see the BigQuery dataset as referred to in the image below. 

Image1: BigQuery Project, Dataset Page

Note: The Red box highlights the BigQuery Project Name and Dataset Name. The Copy button will also provide the said names. 

Image 2: Clicking “Copy” in Image 1 shows the Project Name and Dataset Name.

Step 2: 

Create a BigQuery service account, with access to the BiQuery project. This creates an account that is separate from the principal account that can be used to establish connection. 

Image 3: Create Service Accounts in “IAM & Admin → Service Accounts”. Grant Access to Service Accounts in “IAM & Admin → IAM”

Note: Go to Service Accounts in IAM & Admin to create a Service Account. To add or delete permissions and to create keys, access logs, metrics for a service account go to IAM. 

Image 4: Creating Service Account  

Note: In Service Account, click “Create Account” to create Service Account 

Image 5: Grant BigQuery Job User permission to the service account

Note: Looker requires BigQuery Job User and BigQuery Data Editor permission to establish connection at the very least.

Image 6: Service Account with BigQuery Job User access to the BigQuery Project. 

Note: Click on the Service Account created to add Keys. 

Image 7: Generating Keys for the BigQuery Service Account 

Note: Click on “Keys” → “ADD KEY” to generate a new key. 

Image 8: Choose JSON or P12 and “CREATE”. 

Note: If chosen JSON this downloads a JSON file with the credentials. Keep it safe. 

Step 3: 

Create a BigQuery connection in Looker. 

Image: 9 Looker Connection for BigQuery

Note: The fields highlighted in RED are mandatory fields that are required to be filled in. 

1. Provide a name for the connection. 

2. Select BigQuery Standard SQL Copy in Dialect.

3. Copy and paste the “Project name” from Image 2 in “Project ID”. 

4. Copy and paste the “Dataset name” from Image 2 in “Dataset”.

5. Copy and paste the “Email” from Image 6 in “Service Account Email”.

6. Choose the JSON file downloaded when creating the key in Image 8. 

7. Click “Update Connection” to establish the connection. 

8. Click on the “Test These Settings” and you should see the “ Can Connect” message. 

Good Luck! Now you have successfully established a BigQuery connection in Looker. Now you can use the magic of Looker. 

[brian_foor]

This part isn't available in BigQuery and/or no longer exists: "Image 5: Grant BigQuery Job User permission to the service account." I am currently unable to proceed past this point. Please advise. 

[marcoschierhorn]

We have created a connection to BigQuery successfully and can also query the dataset and its tables via Looker's Explore functionality. But whenever I try to connect to the same dataset via the Google Analytics 4 Templates (available via the marketplace), the API connect will fail due to connection request to a wrong location/region. The BigQuery dataset is in the EU multi-region and not in a specific region.