Skip to main content

BigQuery Connection Guide

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. You can use OAuth (recommended) or a service account.

Prerequisites​

  • A Google Cloud project with the BigQuery API enabled.
  • You must be an admin user.

OAuth Authentication​

OAuth lets each user authenticate with their own Google identity instead of sharing a service account key file. Setup has two parts: a one-time Workspace configuration (admin), and per-user sign-in.

Follow these steps if you don't already have an app created.

  1. Go to APIs & Services and click OAuth consent screen.
  2. Go through the steps to create an App.
  3. Select the Audience based on your organization policy. We suggest Internal for workspace organizations.
  4. Go to Clients or click Create OAuth client.
  5. Set Application type to Web application.
  6. Under Authorized JavaScript origins add your domain https://<your-coalesce-domain>. For example, https://my-org.app.coalescesoftware.io.
  7. Under Authorized redirect URIs add your domain and redirect. https://<your-coalesce-domain>/oauthredirect. For example, https://my-org.app.coalescesoftware.io/oauthredirect.
  8. Copy the Client ID and Client Secret.
Google Cloud Console APIs & Services page with OAuth consent screen highlighted in the left navigation
Go to APIs & Services then OAuth consent screen
Create OAuth client ID form with Web application type, client name, authorized JavaScript origins, and authorized redirect URIs configured
Add the JavaScript origins and redirect URL

Step 2: Add OAuth To Coalesce​

  1. In Coalesce, go to Workspace Settings.

  2. Open the OAuth Settings tab.

  3. Toggle Enable OAuth on.

  4. Click Edit OAuth Credentials and enter the Client ID and Client Secret from Google Cloud.

  5. Save. A checkmark will confirm the credentials are stored.

    Workspace Settings OAuth tab with Google OAuth credentials configured and saved

  6. In Coalesce, go to Build Settings > Environments and select your environment.

  7. Open the User Credentials tab.

  8. In the Authentication Type dropdown, select OAuth.

  9. Click Authenticate. You'll be redirected to Google's consent screen.

  10. Sign in with your Google account and grant BigQuery access.

  11. You'll be redirected back to Coalesce. Your Google email will appear confirming the connection.

  12. Click Test Connection to verify.

    Build Settings Environments page with OAuth authentication and Test Connection button

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

Service Accounts​

A service account uses a JSON key file instead of per-user OAuth. Use it when you want shared credentials for your Workspace, such as for automated jobs or when OAuth is not an option. You'll create a service account in Google Cloud, assign project and data set permissions, then upload the JSON key to Coalesce.

Understanding Service Account Permissions​

Service account access in BigQuery is controlled at two levels: project and data set. Project roles define what the service account can do across the project; data set roles define access to specific data sets.

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.

Data Set Level Permissions​

Data set 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
Data SetBigQuery 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 Data Set 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 data sets page filtered by search term, with two data sets listed

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

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

  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 data set. 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.

Adding the Google Service Account to 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 Coalesce in 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.

What's Next?​