Create a connection for BigQuery

  • Updated

This guide describes the process of setting up Analytics over data stored in a BigQuery warehouse. The following are some quick links to the different procedures that you will need to follow during the setup:

Create a custom role

To enable Analytics to connect to and query your Google BigQuery database, you must create a custom role with specific permissions and assign it to the dedicated Analytics service account. You must have admin privileges in your project to complete the following steps.

  1. Sign in to console.cloud.google.com and select your project.
  2. Go to IAM & Admin > Roles.
  3. Click + Create Role and complete the following:
    • Enter a title, description, and role ID (or use the existing ID).
    • Select the appropriate role launch stage.
    • Click + Add permissions and select the following permissions:

      • bigquery.config.get

      • bigquery.datasets.getIamPolicy

      • bigquery.jobs.create

      • bigquery.jobs.list

      • bigquery.savedqueries.get

      • bigquery.savedqueries.list

      • bigquery.tables.get

      • bigquery.tables.getData

      • bigquery.tables.list

      • resourcemanager.projects.get

  4. Click Add.
  5. Click Create.

Set up your service account

If you already have a service account for BigQuery, verify that the following permissions are granted to your account:

  • The service account has BigQuery Data Viewer permissions for the datasets you want to analyze with Analytics.

  • The service account has permissions for the netspring_rw dataset from the BigQuery Data Owner.

  • The service account has permission to run jobs in the project through the BigQuery Job User role.

If you do not have a service account for BigQuery, create one using the following steps:

  1. Sign in to console.cloud.google.com and select your project.
  2. Go to IAM & Admin > Service Accounts > + Create Service Account.
  3. Enter a name and add an optional description.
  4. Click Create and continue.
  5. Click Select a role and select the custom role you recently created.
  6. Click Continue.
  7. Click Done.

Create a JSON key

  1. Select the service account you recently created.
  2. Go to Keys > Add key > Create new key.
  3. Select JSON as the key type.
  4. Click Create. The key is downloaded to your computer, and a confirmation message displays when the download is complete. Note the filename and click Close.

Share your dataset

  1. Sign in to console.cloud.google.com and select your project.

  2. Go to BigQuery.

  3. Ensure you are in the correct project, then select the dataset you want to share.

  4. Click Share Dataset.

  5. Enter the email address of the service account with which you want to share the dataset in the Add Members field.

  6. Click Select a Role, choose Project Owner, and click Add.

  7. Click Done.

Create a writable schema in BigQuery

To enhance query performance and reduce costs, create a writable schema specifically for Analytics.

First, create a dataset dedicated to Analytics:

  1. Sign in to console.cloud.google.com and select your project.

  2. Go to BigQuery.

  3. Click View actions (...) for your project in the Explorer panel, and click Create dataset.
  4. Enter netspring_rw for the Dataset ID.
  5. Ensure you use the same location type settings as your other datasets to minimize data movement costs.
  6. Click Create dataset.

Second, share write-access for this dataset with Analytics:

  1. Click View actions (...) for the netspring_rw dataset and select Share > Manage Permissions.
  2. Click Add principal.
  3. Enter the service account email in New principals.
  4. Select BigQuery > BigQuery Data Editor for the Role.
  5. Click Save.

To enable Analytics to debug BigQuery performance issues, add the following role:

roles/bigquery.resourceViewer

Alternatively, you can directly grant the following privileges:

`bigquery.jobs.get`
`bigquery.jobs.listAll`

You also must add details of the schema in the Analytics application:

    1. Go to Settings in Analytics.
    2. Enable Materialization under General Settings and specify the following details:
      • Database – The database name in the data warehouse where the materialized tables are created.
      • Schema – The schema's name within the database where the materialized tables are created. This value isnetspring_rw.
      • Refresh Cron Schedule – The refresh periodicity of the materialized tables using the Cron syntax.

When this is done, Analytics creates materialized tables within the data warehouse that contain intermediate results to improve performance.