How to create a BigQuery connection in Looker

Looker Q&A Looker
,
1

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.

kaAWajB7GlABMHTc4G5Wd7sG4XVbDmFMOnldWYmLtN0Hjz734-1JRKPx3nSXEYOHuk6SvIE8RTMFV1ZqD15Qc610reoKR6ewm05SMrATWeo4avfP_c0J79lHe6WNXR42BGXYMPG2J1Frnnq91EKMW-q70phrCbpvHjXNM4sMZfajLUKcST_xhky5DjYRaJw-eQK9rQzxs5JZk34OXs6X7tAVYEXU7OPp6Q
kaAWajB7GlABMHTc4G5Wd7sG4XVbDmFMOnldWYmLtN0Hjz734-1JRKPx3nSXEYOHuk6SvIE8RTMFV1ZqD15Qc610reoKR6ewm05SMrATWeo4avfP_c0J79lHe6WNXR42BGXYMPG2J1Frnnq91EKMW-q70phrCbpvHjXNM4sMZfajLUKcST_xhky5DjYRaJw-eQK9rQzxs5JZk34OXs6X7tAVYEXU7OPp6Q1600×545 107 KB

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.

plfphiUAoi3coF-SPwDkTGSb3EZNjGI5yKqfud44UFUEb_fT5d4SfuxP5IsOzqroW4q2UeLXzEWl4KeoYwvBZbrS1CkjwJEbAhfc5IpjAGm_s4P7A2Vnh9k3vK2axl-MOPr--xZipfLn7Ou4jG7D9jGjZ9uLxKN-WGBFprTU18VhjPDTn73leT84EnbXtkuoef8cf-fsvS0g9idVZHexIoYeE6kcnxRytQ
plfphiUAoi3coF-SPwDkTGSb3EZNjGI5yKqfud44UFUEb_fT5d4SfuxP5IsOzqroW4q2UeLXzEWl4KeoYwvBZbrS1CkjwJEbAhfc5IpjAGm_s4P7A2Vnh9k3vK2axl-MOPr--xZipfLn7Ou4jG7D9jGjZ9uLxKN-WGBFprTU18VhjPDTn73leT84EnbXtkuoef8cf-fsvS0g9idVZHexIoYeE6kcnxRytQ1136×356 18.3 KB

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.

s9mmbVo2F0oGe5_58gaJb_D-wfSlYcGXkXkImmR4WsJ4tPiVMtaVYIC5jNvScbwhyC6Jw4Tad_p7sHkyffur5ibHBmNAk9keFzs-PtKUUn7AZ9dPCbQ0U5IqpOf5M2aUEM-x_zJY8n0e0vCxwj1lb59iWOBG790AxEaBpTSoAfvRzRXC1f54btLOVbUhYXx20--yxdeYTmo3b3duGBKi9UALV_8hauVDrA
s9mmbVo2F0oGe5_58gaJb_D-wfSlYcGXkXkImmR4WsJ4tPiVMtaVYIC5jNvScbwhyC6Jw4Tad_p7sHkyffur5ibHBmNAk9keFzs-PtKUUn7AZ9dPCbQ0U5IqpOf5M2aUEM-x_zJY8n0e0vCxwj1lb59iWOBG790AxEaBpTSoAfvRzRXC1f54btLOVbUhYXx20--yxdeYTmo3b3duGBKi9UALV_8hauVDrA922×1090 82.6 KB

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.

xHSqGrMzmvfXsUZOphW8a_xpCMzpfgJeGJOgPIstAwpuY0pWJJHZvjmJRrNIrg0G_7YOXV-6J93Swhip7Cui3F77vwbGZ90mZvBgR3KWkNRtWw_sTeq382vlAGnogI91vBD7s_nnUHs_tDhq9ya_kbIxTTWZw-wZcOwc5HUlEV11sZAARsAVRk5jELllj2Jm01iR8ZcxlnBlE3xolAsdaWwyVl6LHS7Kbg
xHSqGrMzmvfXsUZOphW8a_xpCMzpfgJeGJOgPIstAwpuY0pWJJHZvjmJRrNIrg0G_7YOXV-6J93Swhip7Cui3F77vwbGZ90mZvBgR3KWkNRtWw_sTeq382vlAGnogI91vBD7s_nnUHs_tDhq9ya_kbIxTTWZw-wZcOwc5HUlEV11sZAARsAVRk5jELllj2Jm01iR8ZcxlnBlE3xolAsdaWwyVl6LHS7Kbg1600×1296 197 KB

Image 4: Creating Service Account

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

3wcJUOeWzrXNyHKGr49kFCfxsw4IB0tv_KW0oxoNLJFPc_E7Zw6Y1iXprRe10beBsaZam13TKQYQBLdPZpFNb5qP2Sy2nwdcOdi7UGtEteOLIKlE27Uv02aSP0G66PKoUL0xOS-Ah7yAvwBKf0EWJopmFVhB5FUHR-qdXfg6jsgtFhSTYo5NhQiRUEPLhXaKUn-IEx9x55ZmexKhWU5XISQhQl67SmC9ew
3wcJUOeWzrXNyHKGr49kFCfxsw4IB0tv_KW0oxoNLJFPc_E7Zw6Y1iXprRe10beBsaZam13TKQYQBLdPZpFNb5qP2Sy2nwdcOdi7UGtEteOLIKlE27Uv02aSP0G66PKoUL0xOS-Ah7yAvwBKf0EWJopmFVhB5FUHR-qdXfg6jsgtFhSTYo5NhQiRUEPLhXaKUn-IEx9x55ZmexKhWU5XISQhQl67SmC9ew1600×1400 241 KB

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.

rdoAKDTFOVINg4C5A80ePyjlwjaENzJ4z06Cz_SWYWz7OsjO-nnBFo7BsxV4wUxLP7IwkGT7lLXXxgH6jwJDPc5xl-I2VRYYldCaDnKdfjM35h4CIfgPlKD1drmYHlbjO66G7yeTlGA8oXas_Su7cNzm2ipFKFGYONPnqiusqlfBJtF-y5JBlSclnqZ51C_fN09hjYpNSWCYdJnwvd7K3yoCBGt4XqaBYQ
rdoAKDTFOVINg4C5A80ePyjlwjaENzJ4z06Cz_SWYWz7OsjO-nnBFo7BsxV4wUxLP7IwkGT7lLXXxgH6jwJDPc5xl-I2VRYYldCaDnKdfjM35h4CIfgPlKD1drmYHlbjO66G7yeTlGA8oXas_Su7cNzm2ipFKFGYONPnqiusqlfBJtF-y5JBlSclnqZ51C_fN09hjYpNSWCYdJnwvd7K3yoCBGt4XqaBYQ1600×354 108 KB

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

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

BBDk18uzeO2jmhQ5juuZVS4nhvwzUj4FSx_BgZo5sIbxTIOCWzey1M9lMj5fcvJEle7Y7xVnGNafdu0VhQceo4Rjyr_Px-sUjj5KjLlgnd1ABI33D-HvBNh4jILPGaugz9akn5ZOlB3vkzU0basmDMo7rPaoC6glbUTPLuABQY1iqMzFp-hQO883tfnyzDvG20gGoD5Hp94Fs0Hl__a86AkR3-oVKsz9jQ
BBDk18uzeO2jmhQ5juuZVS4nhvwzUj4FSx_BgZo5sIbxTIOCWzey1M9lMj5fcvJEle7Y7xVnGNafdu0VhQceo4Rjyr_Px-sUjj5KjLlgnd1ABI33D-HvBNh4jILPGaugz9akn5ZOlB3vkzU0basmDMo7rPaoC6glbUTPLuABQY1iqMzFp-hQO883tfnyzDvG20gGoD5Hp94Fs0Hl__a86AkR3-oVKsz9jQ1600×710 156 KB

Image 7: Generating Keys for the BigQuery Service Account

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

C-YcjEGSd71juqDNC2vpIW17KRR8mBPxWdlJtyGKLY3ithKMqbmR9mySZR91gZyZ6VwDZuOXCMPF_1QwnqGFW40qUz2afgDq5AQD6O4FCiXrm3TqLBA1CwSwRyFeONzPCDgcyjeARDYaKsesR9E0vJdkf4oV-8owedZYiNn0y-aK3MHq2YqG4XcHdcdkdRKBYLN8kBtyYz7GuEo8ZPhLwfWhyrLGwfCTFw
C-YcjEGSd71juqDNC2vpIW17KRR8mBPxWdlJtyGKLY3ithKMqbmR9mySZR91gZyZ6VwDZuOXCMPF_1QwnqGFW40qUz2afgDq5AQD6O4FCiXrm3TqLBA1CwSwRyFeONzPCDgcyjeARDYaKsesR9E0vJdkf4oV-8owedZYiNn0y-aK3MHq2YqG4XcHdcdkdRKBYLN8kBtyYz7GuEo8ZPhLwfWhyrLGwfCTFw1130×778 45.8 KB

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.

gkKkCg6fY7WP40tkmFcbJ95SmECp6o8ipUp2uHtq68lQrD0bZIsFdD5bHjhJc3mf0ocpzeSwht92zHqrWz20GbeROT5vmuSMnFwDRohmmlazL1mRBB41SjxOwaaqiaY_AxvzOB1RvIG1t_wde81HxcQPR7zEEzK9p30spFkayDP8UkZxaGLF8K4jVqtytSnym6qrK3NRvCIfkshtJx1yiSoCuAldNSLUzw
gkKkCg6fY7WP40tkmFcbJ95SmECp6o8ipUp2uHtq68lQrD0bZIsFdD5bHjhJc3mf0ocpzeSwht92zHqrWz20GbeROT5vmuSMnFwDRohmmlazL1mRBB41SjxOwaaqiaY_AxvzOB1RvIG1t_wde81HxcQPR7zEEzK9p30spFkayDP8UkZxaGLF8K4jVqtytSnym6qrK3NRvCIfkshtJx1yiSoCuAldNSLUzw926×1600 232 KB

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.

2

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.

3

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.

4

It does exist. Unfortunately, it doesn’t exist in the dropdown window. Worse, you cannot find it if you filter for it. But this is just an error in the GUI, the role still exists.

However, the BigQuery User role is a superset that includes everything in BigQuery Job User, but still does not allow for any destructive acts like what BigQuery Data Owner would provide, for instance.

You can always see what the full suite of roles are by looking on the IAM => Roles page.

5

your fields 3,4&5 in step 3 do not match the current dialog.

For me, the Database Settings are:

  • Billing Project ID
  • Storage Project ID
  • Primary Dataset