---
schema_version: "1"
id: cba5952b86fade69cc97fdd643b6677bf04ef42431ac165bd279ada7a5e3a0af
title: Google BigQuery for Legacy SQL Connection
collection: admin
version: current
locale: en-us
portal_url: https://docs.salesforce.com/admin/current/en-us/analytics/264-0-0/bi_integrate_connectors_googlebigquery_settings.md
canonical_url: https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_connectors_googlebigquery_settings.htm&release=264.0.0&type=5
source_site: help.salesforce.com
content_type: documentation
content_hash: ec57803091a70114e8aad9b7e9a684ffd7db0a4dfd42a65b5fad990693313d3a
description: Create a remote connection using the Google BigQuery connector to sync data from Google BigQuery to CRM Analytics.
products:
  - CRM Analytics
  - Cross Cloud
release: "264"
---
# Google BigQuery for Legacy SQL Connection

Create a remote connection using the Google BigQuery connector to sync data from Google BigQuery to CRM Analytics.

![Important](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/images/icon_important.png)

Important Use the [Google BigQuery Standard SQL Connection](https://help.salesforce.com/articleView?id=bi_integrate_connectors_googlebigquery_standard_sql.htm&language=en_US) instead of this connector if using standard SQL or when querying partitioned tables. For more information on the limitations with legacy SQL and partitioned tables, see Google’s [Introduction to Partitioned Tables](https://cloud.google.com/bigquery/docs/partitioned-tables#limitations).

## Connection Requirements

The service account that you use to connect to Google Big Query must have the standard role "BigQuery Data Viewer" and a custom role with these permissions.

- bigquery.jobs.create
- bigquery.jobs.get
- bigquery.jobs.list
- bigquery.jobs.listAll
- bigquery.jobs.update
- bigquery.tables.create
- bigquery.tables.delete
- bigquery.tables.update
- bigquery.tables.updateData
- storage.buckets.list

Account creation generates a JSON file of account properties. Most of the necessary connection settings are in this file. For more information about viewing and downloading service account details, see Google’s [Creating and managing service account keys](https://cloud.google.com/iam/docs/creating-managing-service-account-keys) documentation.

## Create Connection

1. On the Data Manager Connections tab, click **New Connection**.
2. Click the name of the connector, and click **Next**.
3. Enter the connector settings.
4. To validate your settings and attempt to connect to the source, click **Save & Test**. If the connection fails, CRM Analytics shows possible reasons.

All settings require a value, unless otherwise indicated.

<table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small ">
        <colgroup>
          <col style="width:50%">
          <col style="width:50%">
        </colgroup>
        <thead class="slds-text-title_caps ">
          <tr class="">
            <th scope="col" class="slds-cell-wrap" style="vertical-align:top;" id="d54584e136">Connection Setting</th>
            <th scope="col" class="slds-cell-wrap" style="vertical-align:top;" id="d54584e139">Description</th>
          </tr>
        </thead>
        <tbody>
          <tr class="">
            <td data-label="Connection Setting" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e136 ">Connection Name</td>

            <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e139 ">Identifies the connections. Use a convention that lets you easily distinguish
              between different connections.</td>

          </tr>
          <tr class="">
            <td data-label="Connection Setting" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e136 ">Developer Name</td>

            <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e139 ">API name for the connection. This name can’t include spaces. The API name is
              used in your recipes to reference data extracted through this connection. You can’t
              change the developer name after you create the connection.</td>

          </tr>
          <tr class="">
            <td data-label="Connection Setting" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e136 ">Description</td>

            <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e139 ">Description</td>

          </tr>
          <tr class="">
            <td data-label="Connection Setting" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e136 ">Schema</td>

            <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e139 ">Name of the Google BigQuery Dataset ID you’re connecting to.</td>

          </tr>
          <tr class="">
            <td data-label="Connection Setting" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e136 ">Private Key</td>

            <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e139 ">
              <p>Enter the <code>private_key</code> value from the JSON
                file. </p>
              <p style="margin-bottom:0;">Don’t include <code>"private_key": "-----BEGIN
                    PRIVATE KEY-----"</code> at the start of the key, and <code>\n-----END PRIVATE KEY-----\n"</code> at the
                end.</p>
            </td>

          </tr>
          <tr class="">
            <td data-label="Connection Setting" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e136 ">Client Email</td>

            <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e139 ">Enter the <code>client_email</code> value from the JSON
              file.</td>

          </tr>
          <tr class="">
            <td data-label="Connection Setting" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e136 ">Project ID</td>

            <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e139 ">
              <p>Enter the <code>project_id</code> value from the JSON
                file.</p>
              <p style="margin-bottom:0;">If you have multiple projects with the same service account, enter the ID
                of the project containing the dataset that you want to connect to.</p>
            </td>

          </tr>
          <tr class="">
            <td data-label="Connection Setting" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e136 ">Billing Project ID</td>

            <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e139 ">The ID for the Cloud Billing account you want this connection’s activity
              charged to.</td>

          </tr>
          <tr class="">
            <td data-label="Connection Setting" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e136 ">Extract Mode</td>

            <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e139 ">
              <p>Optional setting. Enter one of the following modes to extract data from Google
                BigQuery. Direct is the default if a mode isn’t specified. </p>
              <p>Direct: Use this mode
                to extract small data volumes. The connector extracts the data directly from
                Google BigQuery.</p>
              <p style="margin-bottom:0;">Staging: Use this mode to extract large data volumes, such
                as 10 million records. To increase performance when handling large data volumes,
                the connector stages the data in the specified Google Cloud storage path and then
                extracts the data from the storage path. If a direct extract fails, try using this
                extract mode.</p>
            </td>

          </tr>
          <tr class="">
            <td data-label="Connection Setting" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e136 ">Use Storage API</td>

            <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e139 ">
              <p>Optional setting. </p>
              <p><samp class="parmname">True</samp>: Uses the Google BigQuery
                Storage Read API. See <a href="https://cloud.google.com/bigquery/docs/reference/storage" target="_blank">Google’s BigQuery Storage Read API</a> documentation to
                understand your expected consumption, and <a href="https://cloud.google.com/bigquery/pricing#storage-api" target="_blank">Google’s data extraction pricing</a> for additional cost
                information. </p>
              <p style="margin-bottom:0;"><samp class="parmname">False</samp>: Uses the Direct extract mode. This
                mode has a data limit of 10MB. </p>
            </td>

          </tr>
        </tbody>
      </table>

## Filter Data Synced to CRM Analytics

Exclude unnecessary or sensitive data from syncing to CRM Analytics with data sync filters. Filters run on the source object and speed up data sync by pulling only the data you need into CRM Analytics.

![Note](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/images/icon_note.png)

Note If you might use excluded data in the future, use a recipe filter to limit the data written to a dataset instead of a data sync filter.

1. From Data Manager, click the **Connections** tab.
2. Select the connection associated with the object to filter.
3. Click the name of the object to filter.
4. Click **Data Sync Filter**.
5. Enter the filter.
6. Click **Save**.

For the Google BigQuery connector, enter a filter in the syntax described in [Google’s BigQuery Help](https://cloud.google.com/bigquery/docs/reference/legacy-sql#where).

## Google BigQuery Connector Considerations

Keep these behaviors in mind when working with the Google BigQuery connector.

- Connected object names must start with a letter and contain only letters, digits, or underscores. Object names can’t end with an underscore.
- Only field names with combinations of alphanumeric, dot, underscore, or dash characters are supported. If a connector includes field names that contain other characters, such as spaces or brackets, the sync fails.
- The connector can sync up to 100 million rows or 50 GB per object, whichever limit is reached first.
- BigQuery tables using data integrated from Google Drive aren’t supported. The Google Drive data must be moved into BigQuery.

Nested and Repeated Fields

The connector flattens nested fields and syncs them as separate fields. In addition, the connector generates new rows for repeated fields. Let’s look at an example. The contact data in this JSON file contains the current and previous address for each contact. The addresses field has nested status, street, city, and state fields. These nested fields appear twice for each contact: first for the current address, and then for the previous address.

```
{"id":"1","name":"James Park","addresses":[{"status":"current","street":"1 Harper Alley","city":"Toledo","state":"OH"},{"status":"previous","street":"392 Parkside Street","city":"Seattle","state":"WA"}]}
{"id":"2","name":"Lori Carr","addresses":[{"status":"current","street":"7501 Talisman Court","city":"Fort Worth","state":"TX"},{"status":"previous","street":"3368 Anderson Lane","city":"Moreno Valley","state":"CA"}]}
```

If you include the addresses field for sync, each nested field appears as a separate field in each row of the synced connected object. The repeated address fields result in two rows for each contact: one row for the current address, and one row for the previous address.

     
| id | name | addresses.status | addresses.street | addresses.city | addresses.state |
| --- | --- | --- | --- | --- | --- |
| 1 | James Park | current | 1 Harper Alley | Toledo | OH |
| 1 | James Park | previous | 392 Parkside Street | Seattle | WA |
| 2 | Lori Carr | current | 7501 Talisman Court | Fort Worth | TX |
| 2 | Lori Carr | previous | 3368 Anderson Lane | Moreno Valley | CA |

Temporary Destination Tables

The connector creates temporary destination tables in BigQuery to stage data before serving it to the client. The connector names these tables in the format EA\_TEMP\_<5-character random string>\_<objectName>. Tables are usually deleted automatically. To avoid incurring extra data storage costs on Google BigQuery, check that all temporary tables have been deleted, and manually delete any tables that haven’t been deleted.

Table Support

The Google BigQuery for Legacy SQL connector supports Standard SQL Tables, Legacy SQL Tables, and Legacy SQL Views, but doesn’t support Standard SQL Views.

![Note](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/images/icon_note.png)

Note This connector uses the Google BigQuery Storage Read API for increased throughput, and may impact your Google billing. You may see a nominal cost increase per TB if you're not on a flat-rate plan. See [Google’s BigQuery Storage Read API](https://cloud.google.com/bigquery/docs/reference/storage) documentation to understand your expected consumption, and [Google’s data extraction pricing](https://cloud.google.com/bigquery/pricing#storage-api) for additional cost information.

<table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small " summary="Additional Settings Required for Legacy Connector">
        <caption>Additional Settings Required for Legacy Connector</caption>
        <colgroup>
          <col style="width:50%">
          <col style="width:50%">
        </colgroup>
        <thead class="slds-text-title_caps ">
          <tr class="">
            <th scope="col" class="slds-cell-wrap" style="vertical-align:top;" id="d54584e525">Setting</th>
            <th scope="col" class="slds-cell-wrap" style="vertical-align:top;" id="d54584e528">Description</th>
          </tr>
        </thead>
        <tbody>
          <tr class="">
            <td data-label="Setting" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e525 ">Storage Path</td>

            <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d54584e528 ">
              <p>Applicable only to the legacy connector version. Required when using the
                Staging extract mode. Path in Google Cloud Storage where a local stage file is
                created to store the data temporarily. Enter the bucket name or the bucket name and
                folder name. For example, enter <kbd>&lt;bucket_name&gt;</kbd> or
                <kbd>&lt;bucket_name&gt;/&lt;folder_name&gt;</kbd>.
              </p>
              <p style="margin-bottom:0;">To write files to this
                staging area, the user specified in Client Email must have the list, read, and
                write permissions on the bucket.</p>
            </td>

          </tr>
        </tbody>
      </table>

#### See Also

- [Google BigQuery Connector Known Issues and Knowledge Articles](https://sfdc.co/gbqconnsearch)
- [Add and Remove Remote Objects and Fields from Data Sync](https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_connectors_replicate_connection.htm&release=264.0.0&type=5 "Update data sync for connected external data sources to keep up with your changing business by adding objects, removing objects, and managing which fields are synced. Synced data is stored in CRM Analytics as objects that can be used in recipes.")