Skip to main content

BigQuery Connection Guide

Preview

This feature is currently available in a private preview and may not be accessible to all customers.

info

Service accounts are the only available authentication method

In this guide, you'll learn how Google permissions work for Coalesce, how to set permissions in Google Cloud IAM, and how to authenticate in Coalesce.

Permissions Overview​

BigQuery requires permissions to be set on the project and the data set.

Project Level Permissions​

Project permissions are roles assigned in Google Cloud IAM. These roles are inherited in data sets only when a service account is also granted permission to access that data set.

Dataset Level Permissions​

Dataset permissions control what the service account or principal can do with specific data sets. A hierarchy for common roles is Data Viewer < Data Editor < Data Owner.

RoleCapabilities
Data ViewerCan test a connection and view storage mappings, but can't add sources or build. This role isn't recommended.
Data EditorCan do all things within your data transformation app. This role is recommended.
Data OwnerCan do all things within your data transformation app and gains extra permissions within BigQuery.

Minimum Required Permissions​

The minimum combination to use Coalesce with BigQuery:

LevelRole
ProjectBigQuery User
DatasetBigQuery Data Editor

Set Up Project Level Permissions​

  1. Make sure you're in the project you want to add the service account to.

  2. Go to IAM & Admin > Service Accounts in Google Cloud.

    Google Cloud Console IAM & Admin navigation menu with Service Accounts selected, showing options such as IAM, PAM, Policy Analyzer, Organization Policies, and Service Accounts.

  3. Click Create service account.

    Google Cloud Console IAM & Admin Service accounts page showing a list of service accounts for a project, with the Create service account button highlighted at the top.

  4. Fill out the required Service account ID and any other information required by your company's policy. Click Create and Continue.

  5. Search for the role BigQuery User. Click Continue.

    Google Cloud Console Create service account page showing the Permissions step, with the role selector open and BigQuery User highlighted as a role option.

  6. You can skip Principals with access. Click Done. Move on to setting data set level permissions.

Set Up Dataset Level Permissions​

  1. Go to BigQuery Studio.

  2. In the Explorer, find the data set you want to add to Coalesce.

    Google BigQuery console showing the Datasets page filtered by search term, with two datasets listed

  3. In the data set, click Share > Manage Permissions.

    Google BigQuery console showing the dataset overview, with the Share menu open and the Manage permissions option highlighted for the dataset.

  4. Click Add principal.

  5. Search for your service account name. It should look similar to name@projectname.iam.gserviceaccount.com.

  6. Then assign roles as BigQuery Data Editor.

Google Cloud Console IAM dialog for granting access to a BigQuery dataset. A service account is added as a principal, and the BigQuery Data Editor role is selected under Assign roles, with options to add IAM conditions and save changes.

Authenticate in Coalesce​

  1. Go to IAM & Admin > Service Accounts in Google Cloud.

    Google Cloud Console IAM & Admin navigation menu with Service Accounts selected, showing options such as IAM, PAM, Policy Analyzer, Organization Policies, and Service Accounts.

  2. Click on the service account created for Coalesce.

  3. Click Keys.

  4. Then Add key > Create new key.

    Google Cloud Console Service account Keys page showing the Add key menu expanded, with the Create new key option highlighted for generating a new service account key.

  5. Download the JSON file.

  6. In the Create Workspace step or in Workspace settings, upload the JSON file from Google Cloud IAM. Then click Test Connection.

Create a Workspace setup screen showing the Add your BigQuery Credentials step, with Authentication Type set to Service Account and an option to upload a Google service account JSON key file.

You've authenticated BigQuery in Coalesce. Add some data to get started.

Unable to Connect

Make sure the permissions are set on the project and data set.