Documentation

Note: Google BigQuery is currently supported only as a Destination. This guide doesn’t cover the DataPrep setup for GBQ.

Description

BigQuery is Google's serverless, highly scalable enterprise data warehouse designed for your data analysts. Improvado can load all data gathered from dozens of available data sources to this storage.

Follow our setup guide to connect Google BigQuery to Improvado.

Generate a Service Account Key JSON file

In order to use Service Account Key authentication, first, you need to generate a JSON file via Google Cloud Console using official documentation or an interactive step-by-step guide provided by Google. Alternatively, you can follow the instructions below.

1. In Google Cloud Console, go to IAM & Admin → Service Accounts.

2. Click on the Actions button for your Service account and select Manage keys.

3. Create a new JSON Private key in the KEYS tab.

Note: Clicking Create downloads a service account key file. After you download the key file, you cannot download it again.

4. In the downloaded JSON file, copy your Project ID.

{
  "type": "service_account",
  "project_id": "{{PROJECT_ID}}",
  "private_key_id": "{{KEY_ID}}",
  "private_key": "-----BEGIN PRIVATE KEY-----\n{{PRIVATE_KEY}}\n-----END PRIVATE KEY-----\n",
  "client_email": "SERVICE_ACCOUNT_EMAIL",
  "client_id": "{{CLIENT_ID}}",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/{{SERVICE_ACCOUNT_EMAIL}}"
}

Learn more about Workload Identity Federation configuration here.

Roles and permissions

Don't forget to set up the jobUser & dataEditor/dataOwner roles given to the Service account or a custom role with needed permissions. A detailed list of required permissions should look as follows:

Complete configuration

On the Google BigQuery connection page, fill in the following fields:

1. Enter a name for your Destination connection in the Title.
2. Select Service Account Key as the Authentication type (recommended).
3. Unless you have specific security requirements, it is recommended to use Improvado-owned bucket as the Transitional GCS bucket. Google Cloud Storage bucket must belong to the same Google Cloud Platform project.
4. Enter the Transitional GCS Bucket Name.
5. Enter the Dataset Name.
6. Select the neccesary Use static IP option from the dropdown. {%dropdown-button name="use-static-ip"%}

{%dropdown-body name="use-static-ip"%}

You can learn how to restrict IP addresses that are allowed to access Google BigQuery here: Restrict IP addresses allowed to access Google BigQuery. Learn more about VPC Service Controls in the official documentation here.

Select Yes for Use static IP option if you allow Improvado to connect your database by the static IPs mentioned on the Destination connection page.

Select No if you have permitted access to your database from any IP. In this case, Improvado will connect your database using dynamic IPs not listed on the Destination connection page.

{%dropdown-end%}

7. Upload your Service account key JSON file to the Service account key.
8. Enter your GCP project ID as Project ID.
9. (Workload Identity Federation only) Enter the GBQ Project Number. You can find it in Google Cloud Developers Console → Cloud Overview → Dashboards.
10. (Workload Identity Federation only) Enter the Workload Pool ID. Pool IDs are used as identifiers in IAM.
11. (Workload Identity Federation only) Enter the AWS Provider ID. Providers manage and verify identities.
12. (Workload Identity Federation only) Enter the Service Account Email. A service account is identified by its email address, which is unique to the account.

Secondary Authentication Option (Workload Identity Federation)

Note: We recommend using the Service Account Key as an authentication method.

With Identity Federation, you can use Identity and Access Management (IAM) to grant external identities IAM roles, including the ability to impersonate service accounts. This approach eliminates the maintenance and security burden associated with service account keys.

1. Set up a Workload pool and Provider for your Google Cloud project.
2. Copy the Improvado AWS Account ID that you can find on the Destination connection page.

3. Paste Improvado AWS Account ID and configure Attribute mapping:
4. ~Set ```attribute.aws_role``` attribute value to ```assertion.arn.extract('assumed-role/{role}/')```.

4. Add a condition to allow only one AWS IAM role which is called: ```workload_identity_federation```.
5. ~Set the Condition CEL to ```attribute.aws_role == "workload_identity_federation"```.

5. On the Workpool details page, click the Grant access button.

6. Select principals (identities that can access the service account):
7. ~Set ```aws_role``` attribute value to ```workload_identity_federation```.

7. In Complete configuration, enter your Workload Pool ID and AWS Provider ID to the corresponding fields in the Improvado UI.

Learn more about Workload Identity Federation configuration here.

Data formats and breakdowns

Improvado stores data GBQ in "sharding" format in tables named like ```TABLE_NAME_YYYYMMDD```. Typically we break the data down by platform, account, dimension, and month, but there are some alternatives. We can implement a day-by-day division if you need it.

Additional options

If you want to have your accounts divided into groups and load those groups into different datasets (and even different GBQ accounts), we can handle this. Don't hesitate to reach our support for additional details.