BigQuery
Connect a Google BigQuery warehouse from Equals
To connect BigQuery to Equals you need to create a service account with the right permissions and then upload the resulting key to Equals. The easiest way to handle permissions is to grant Equals the BigQuery Data Viewer role and the BigQuery Job User role.
If you need more discrete control over permissions, you can also choose to create a custom role and set the required permissions yourself.
You can follow the instructions below or watch the 2-minute video walkthrough below.
Using predefined roles
Predefined roles cover all required roles for Equals to work with BigQuery.
Create a service account
- In the top nav search type Service accounts and choose Service accounts under Products & Pages.
- Click Create Service Account give your account a name like Equals and click Create and Continue.
- Add role
BigQuery Job User
. - If you would like to grant access to all datasets, add role BigQuery Data Viewer. Otherwise, you can grant this role to the individual datasets you want to grant access to later.
- Once you’ve added the appropriate roles, click Continue.
Create Private Key
- Select your service account you just created from the Service accounts list page.
- Click Keys in the tab bar at the top, click Add key and then Create new key.
- Select JSON in the modal and then click Create
- A JSON file will be downloaded to your computer. Upload this file as the Private Key in Equals. Equals should now be able to connect to your BigQuery instance.
Using custom roles
Custom roles give you more control over what Equals has access to.
Create a custom role
You need to create a custom role to grant a service account permissions to view your schema tables. We need access to these schema tables for querying in Equals to work.
- In the top nav search type roles and choose Roles under Products & Pages.
- Click Create role in the top nav
- Name the custom role
- Click on Add permissions
- In the modal, search and add the following permissions:
- If you plan to restrict access to specific datasets, add this permission.
bigquery.jobs.create
Enables Equals to execute SQL queries against a BigQuery dataset or table. This is required at the service account level.- Follow instructions under the restricting access section to create a separate role that you can apply to specific tables and datasets in your project.
ii. If you plan to give access to all datasets in your project, add the rest of these permissions at the service account level. bigquery.datasets.get
Enables Equals to see a datasetbigquery.tables.get
Enables Equals to see a tablebigquery.tables.list
Enables Equals to get the tables on a dataset
- If you plan to restrict access to specific datasets, add this permission.
- Click Create
Create a service account
- In the top nav search type Service accounts and choose Service accounts under Products & Pages.
- Click Create Service Account give your account a name like Equals and click Create and Continue.
- You need to grant the service account access to the custom role you just created.
- Once you’ve added the custom role, click Continue.
Create a private key
- Select your service account you just created from the Service accounts list page.
- Click Keys in the tab bar at the top, click Add key and then Create new key.
- Select JSON in the modal and then click Create
- A JSON file will be downloaded to your computer. Upload this file as the Private Key in Equals. Equals should now be able to connect to your BigQuery instance.
Restricting access
Specific datasets
If you need to restrict access to specific datasets in your project, you can either:
- Assign the pre-defined role you created to each dataset you want Equals to have access to.
- Create a new custom role with these permissions and then assign it to each dataset you want Equals to have access to.
bigquery.datasets.get
bigquery.tables.get
bigquery.tables.list
Tables in an allowed dataset
If you need to restrict access to specific tables within a dataset, you’ll need to create two custom roles.
The first role should include these permissions:
bigquery.datasets.get
bigquery.tables.get
bigquery.tables.list
Assign this role to the dataset your table is in.
The second role should include these permissions:
bigquery.tables.getData
Assign this role to the tables you want to give Equals access to.
External tables
If your project is using external tables, you’ll need to add these permissions.
- If you’re using Pre-defined roles, add the
BigQuery Connection User
role to your service account. - If you’re using custom roles, add the
bigquery.connections.user
permission to your role.
Permissions errors
Equals needs the bigquery.jobs.create
permission
bigquery.jobs.create
permissionThe service account must have the bigquery.jobs.create
permission to work. You have to apply this to the service account level.
If you’re using Pre-defined roles the easiest way to do this is to give the service account the BigQuery Job User role. You’ll find the guide under Using Pre-defined Roles.
If you’re using Custom Roles, make sure this permission is included in the Custom Role you’re using for the service account. You’ll find the guide under Using Custom Roles.
Equals needs the bigquery.connections.user
permission
bigquery.connections.user
permissionThe service account must have the bigquery.connections.user
permission to work. You have to apply this to the service account level.
If you’re using Pre-defined roles the easiest way to do this is to give the service account the BigQuery Connection User role. You’ll find the guide under Using Pre-defined Roles.
If you’re using Custom Roles, make sure this permission is included in the Custom Role you’re using for the service account. You’ll find the guide under Using Custom Roles.
Equals doesn’t have access to any dataset in BigQuery
This is likely due to an improper set up of permissions in BigQuery. Check the following:
- Make sure your role has the
bigquery.jobs.create
permission at the service account level. - Make sure the other required permissions are set at either the service account level, or for specific datasets and tables if you’re restricting access.
Missing dataset permission
We have access to 1 or more datasets, but we don’t have adequate permissions on some or all datasets to generate schema. Check the following:
- Make sure your role has the
bigquery.jobs.create
permissions at the service account level. - Make sure the other required permissions are set for all datasets you want us to have access to.
Updated 7 months ago