Configuring BigQuery Access
This article will describe how to configure Google BigQuery Data Warehouse credentials for use by Census, and why those permissions are needed.
censusdataset ('dataset' is what Google calls their equivalent of a 'schema' in standard database terminology).
- Permission to create a new
censusdataset or an existing
censusdataset, and full admin access to all schema/tables within that dataset (including creating and deleting tables, and reading and writing to all tables).
- Read-only access to any tables and views in any schemata that you would like Census to publish to Salesforce.
BigQuery manages these permissions through their IAM Policy mechanism. Specifically, Census uses two BigQuery policies by default:
bigquery.dataViewer- Allows Census read-only access to list the datasets and tables in the BigQuery instance, and to get data from the tables.
bigquery.user- Allows Census to create new datasets which we use to create the
censusdataset. This then grants Census the
bigquery.dataOwnerrole on the contents of this dataset.
Configuring a new BigQuery connection
Because permissions are a bit unique on BigQuery so the process of creating a new connection to Census requires a few extra steps.
- Visit the Connections section on Census, and press Add Data Warehouse Connection, selecting BigQuery from the list.
- Census will ask you to provide the Google Cloud Project ID that contains your BigQuery instance. You can find that on the Google Cloud Console in the Project Info section. If you have multiple Google Cloud projects, you'll need to first select the correct one with the project picker in the top right.
- Once you've provided Census with your Project ID, Census will automatically generate a new Role Account we'll use to communicate with your BigQuery and provide you with two copy and paste-able commands you can use to grant permissions for this account on this project. The easiest way to execute these commands is within the Google Cloud Shell in the Google Cloud Console.
- Once you've run both commands, press the Test button in Census. This will validate that you've granted the appropriate credentials. Once you've got a checkmark for all four steps, you're good to go!
BigQuery does things a bit differently from other data warehouses. Unfortunately, as of this moment, BigQuery does not support limiting access to specific IP addresses.