Google BigQuery for Legacy SQL Connection

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 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.

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 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.

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.