How to connect to Google BigQuery

You can connect to your Google BigQuery data warehouse and run queries to build audiences and send messaging campaigns to your customers. In Vero, database connections are referred to as Data Sources.

Configure or create a project and enable the BigQuery API

1. Login to your Google Account.
2. Configure your Google Cloud Platform:

• If you don't have a Google Cloud Platform project setup already, create one.
• Once you have a project, enable the BigQuery API for it.

3. Store your Project ID for later use.

Create a service account

For the most up-to-date documentation on creating a service account, follow the Google Cloud Platform (GCP) documentation.

A summary of the steps is below:

1. In the Google Cloud Console, navigate to IAM & Admin → Service Accounts.
2. Select Create service account.
3. Enter a name for the service account (e.g., vero-bigquery-sync) and click Create and Continue.
4. In the second step, grant the service account the BigQuery User role. This allows the service account to run jobs and queries.
5. Click Done.
6. Once created, find your new service account in the service accounts list and click on it to view the details.
7. Make note of the service account email (it will look like vero-bigquery-sync@your-project.iam.gserviceaccount.com). You'll need this later.

Grant access to your new service account

The GCP documentation outlines how to manage access for your service account.

By default, your GCP service account doesn't have permission to read any data from BigQuery. When configuring your service account, you can choose to either give the service account full access to BigQuery or limit access to the minimum required for Vero to view your tables and run queries against the relevant datasets.

Granting full access

The easiest way to get started is to grant full access by assigning the bigquery.user and bigquery.dataViewer roles to your service account at the project level. You can do this in the Google Cloud Platform console (under IAM).

Granting limited access

Alternatively you can grante the least required access to the service account you'll use with Vero. This is our recommendation. Whilst this takes a little more configuration it ensures good data hygiene.

To setup the minimum access required by Vero:

• Grant the bigquery.dataViewer role only to the specific datasets, tables, or views you want to use with Vero.
• Create a new role (we suggest ID custom.bigquery.vero.role and name Custom BigQuery Vero Role).
  • Add all of the permissions from the bigquery.jobUser role to your new, custom, role.
  • Also explicitly grant the bigquery.tables.list, bigquery.datasets.get and bigquery.tables.get permissions to the new role.
    • Vero needs these to query the list of datasets, schemas and tables available. Whilst Vero will be able to see the names of other datasets, schemas and tables, Vero can only query data from tables assigned the bigquery.dataViewer role. Tables that weren't assigned this role return an error if you attempt to query them.

Once created, assign the new role to your service account at the project level.

Generate a JSON key

Adding your BigQuery connection

Once your service account is setup with the correct role you can add your service account credentials to Vero to access your BigQuery data warehouse.

To add a BigQuery instance, click Data Sources and Add Data Source. In the menu that appears, choose BigQuery:

To add your database you will need the following credentials:

• Project ID. The Project ID of the Google Cloud Platform project containing the BigQuery instance you want to access.
• Dataset. The ID of the dataset
• JSON Key. Copy/paste the full contents of the service account key. Make sure your key is in the JSON format (not the P12 format).

Supported regions

Vero supports datasets created in both the US and EU regions.

Testing your connection

Once you’ve entered your database credentials, select Connect. Vero will automatically test the connection.

Once the connection test is successful, your new connection is saved and ready to go! You can now use your connection to add audiences to your campaigns.

Securing your connection

Manage your service account

When used as a Data Source, Vero will only execute SELECT queries against your database: it is read only by design.

Vero will show users all available tables when using the SQL editor in our UI. Limiting the tables and columns that Vero has access to is good practice.

FAQs

Query limits

Vero will show your query results in blocks of 100 in the UI. In order to load an audience you will need to load your entire query (without a LIMIT clause).

There is no technical limit to the number of rows you can query using Vero’s UI but we have currently tested up to one million rows consistently.

Query timeout

Vero will currently timeout queries after 12 hours.