Set Up a Google BigQuery Data Federation Connection

Set up a connection between Google BigQuery and Data 360 to access data.

Requirements

Before you configure the connection, review these network and service account requirements.

• If you use Virtual Private Cloud (VPC) Service Controls in Google Cloud, ask your Google Cloud Platform (GCP) org admin to review Data 360 IP Allowlist and Allow access to protected resources from outside a perimeter, and update your allowlists.
• Create a Google Cloud service account with these permissions. See Create service accounts.
  • BigQuery Metadata Viewer
  • BigQuery Job User
  • BigQuery Data Viewer
  • BigQuery Read Session User

Set Up the Connection

1. In Data Cloud, go to Data Cloud Setup.

2. Under Configuration, select More Connectors.

3. Click New.

4. Under Source, select Google BigQuery and click Next.

5. Enter a connection name, connection API name.

6. If you want to use key-based authentication, click Private Key Pair and upload the Private Key JSON file or paste its contents in the SSH Private Key field.

   If you’re using key-based authentication, skip the next step and continue configuring your BigQuery connection.

7. If you want to use identity provider–based authentication, click Identity Provider Based.

   1. In the Google Cloud Service (GCS) account data warehouse, select IAM & Admin > Workload Identity Federation.

   2. In the Workload Identity Pool, click Add provider.

      1. Under details, enter a name for the provider.

      2. For Issuer (URL), enter your org's My Domain URL followed by /services/connectors. For example, https://yourcompany.my.salesforce.com/services/connectors.

      3. Under Audiences, select Allowed Audience and enter your org's MyDomain URL. For example, https://yourcompany.my.salesforce.com.

      4. Under Attribute Mappings, match the audience attribute to OIDC audience (attribute.audi = assertion.aud) and the subject attribute to OIDC subject (google.subject = assertion.sub).

      5. Click Save.

   3. To provide audience access to the workload pool that you created, click Grant access and then select Grant access using service account impersonation.

      1. Select your service account email.

      2. Under Select principals, select the audience attribute that you created, and enter your org's MyDomain URL.

      3. Click Save.

   4. To provide access to subject, click Grant access and then select Grant access using service account impersonation.

      1. From the Data Cloud connection window, copy the external ID.

      2. In GCS, select your service account.

      3. Under Select principals, select the subject attribute and enter the Data Cloud external ID.

      4. Click Save.

   5. Download the config file generated by GCS.

      1. In the Configure your application dialog box, select the provider that you created.

      2. Enter a name for the OIDC token path and click Download config. You can choose any name as it won't be needed in the Data Cloud setup window.

      3. Copy the contents of the downloaded config file and paste it in the OIDC Client Config field in Data Cloud.

8. Enter the Project ID.

9. Enter the Service Account email.

10. To review your configuration, click Test Connection.

11. Click Save.

Considerations for Large Tables

Large Result Set and Unload are two independent optimizations that can be used to enhance the performance of queries on large tables. Unload is more scalable than Large Result Set. Use Unload if you are able to enable acceleration.

• Large Result Set (LRS): When LRS is enabled and the size in GB of the data to be returned by a query is significant, Google writes the data to a Google-managed, internal, temporary, staging location which Data 360 queries transparently. To enable LRS:

  1. Grant the bigquery.tables.create and bigquery.dataset.create permissions in BigQuery to your service account. Associate with your service account either a predefined IAM role that already has these permissions or a custom IAM role with these permissions. See BigQuery predefined IAM roles.

  2. In your BigQuery project, create a dataset named sf_temp_dataset and set its expiration time to six hours.

  3. Select the Large Result Set checkbox in the connection configuration screen.

• Unload: Unload is only applicable to accelerated data streams and is suitable for very data-intensive queries. When Unload is enabled and a refresh of an accelerated data stream is triggered, BigQuery executes the SQL query that Data 360 sends and exports the result to a temporary Google Cloud Storage (GCS) bucket that Data 360 queries using the GCS API. The result of the query is cached in Data 360. Unload is a connection-level property; acceleration is a data stream-level property.

  1. Create a new GCS bucket, and associate the Storage Object User IAM role with your service account.

  2. To reduce storage costs, create a bucket lifecycle policy that deletes exported files older than one day. See Object Lifecycle Management | Cloud Storage.

  3. Toggle the Use Unload button to active.

  4. In the GCS Bucket Absolute Path field, enter the absolute path of the GCS bucket followed by a forward slash. For example, gs://[BUCKET_NAME]/.