Google BigQuery

BigQuery is Google's data warehouse that enables scalable analysis over data. This integration allows pushing your specific input properties, or all of them, to BigQuery.

Key features

The Google BigQuery destination provides the following key features:

• Events structure: our Events reference feeds BigQuery, meaning that your data is properly bridged to the expected fields in an optimized way.

• Multi schema support: store event data following your preferred/existing schema or we can help you creating an universal one.

• Data control: select your properties or just check a box to send them all to BigQuery.

Destination setup

Ensure BigQuery API is enabled. More details are available following this LINK. Use Destination filters to refine events and/or other properties matching your specific needs.

This destination accommodates all schemas, more specifically:

1. your existing table schema or,

2. a universal schema.

The first option is useful for those who want to use an existing table with its predefined columns: this is a common scenario when you have data already stored and is the recommended option as it's fast to configure and data can be accessed directly from the specific column. To enable this mode, in the Configuration, you just need to flag the Auto-discover table structure (recommended) and then proceed to the section Event Property Mapping to select the columns in BigQuery column and their values in Your value . The second method relies on a Universal schema, where all your data (or selected properties) is stored in just one column as a JSON string and using the BigQuery function PARSE_JSON you can retrieve specific values within. We suggest checking the section Universal schema with a detailed walkthrough on how you can create the universal schema and understand if this is the proper table structure for you. To enable this mode, in the Configuration, you can either flag Send all properties to BigQuery with universal schema so all properties will be included or you can input the properties you want to send by using the Property name table in the section Properties to include with universal schema .

Configuration

Settings	Description
Authentication	Required Your credentials with Google as set in the Commanders Act interface following: Administration ➜ Connector Credentials ➜ Add connector credentials ➜ BigQuery
Project Id	Required Select your project identifier from the dropdown menu as reported in BigQuery console. More details are available following this LINK.
Dataset Id	Required Select your dataset identifier as reported in BigQuery console. More details are available following this LINK.
Table Id	Required Select table identifier as reported in BigQuery console. More details are available following this LINK.
Auto-discover table structure (recommended)	Check this option to enable the auto-discover table structure feature.
Send all properties to BigQuery with universal schema	Flag this option to send all properties to BigQuery following a specific schema: see section Universal schema for more details.
Event property Mapping	When Auto-discover table structure (recommended) is flagged, you have to map your BigQuery fields by selecting them in the BigQuery column and set their values in Your value .
Property name [1]	When both Auto-discover table structure (recommended) and Send all properties to BigQuery with universal schema are not flagged, you can input the properties you want to include in your table with universal schema, one per line.

[1] in the section Properties to include with universal schema .

Universal schema

The following steps are not required if you check Auto-discover table structure (recommended) .

When flagging Send all properties to BigQuery a specific schema is required. See the following subsections to learn how you can create an universal schema. This is not

Dataset

Access BigQuery console to locate your (1) project identifier and click (2) the three dots on the right. Select (3) Create dataset from the menu or, alternatively, you can use an existing dataset and jump to the next subsection.

Dataset creation #1.

Input a (4) dataset identifier (E.g. "myDatasetId"), select a (5) location type and click (6) CREATE DATASET.

Dataset creation #2.

Table

Create a table with the following structure:

Field name	Type	Mode
rawDataCa	String	Required
createdAt	Timestamp	Required

The esiest way to create it is to click (7) the plus button:

Compose a new query.

copy and paste the following query in (8) the input area:

CREATE TABLE IF NOT EXISTS [PROJECT_ID].[DATASET_ID].[TABLE_ID] (rawDataCa STRING NOT NULL, createdAt TIMESTAMP NOT NULL) OPTIONS(description="CA raw event data stored in Google BigQuery")

Replace [PROJECT_ID] , [DATASET_ID] and [TABLE_ID] with your project, dataset and table identifier respectively. You set your table identifier at this step.

and then click the (9) RUN button.

Run table creation query.

Quick reference

Commanders Act Events	BigQuery Table Columns
[Any events] [1]	rawDataCa, createdAt [2]

[1] Use Destination filters to specify your matching events. [2] Two columns: rawDataCa contains your event properties, while createdAt is the creation timestamp.