Articles in this section

Google BigQuery

Avatar
Adverity Support
  • Updated
Follow

CREATE A GBQ DESTINATION

  1. In the Transfer Element, Click "+Add" in the top right.
  2. In the list of Destinations, select "Google BigQuery" under the Database heading.
  3. Click "Setup a new Connection".
  4. Use your Google account credentials to login and authorize Adverity to access data.
  5. Save to finalize the Connection, your Google BigQuery data will now be available to the Adverity platform.

 

CONFIRMING SUCCESS

  1. Navigate through Transfer > Destinations.
  2. Select the Google BigQuery Destination from the list to open the Destination Settings. 
  3. If your setup was successful, you will be able to choose a dataset from the drop-down shown below. 
    GBQ_dataset.png
If the drop-down menu is empty, that means that your setup was unsuccessful. We suggest you try the following:
  • Ensure that there is a dedicated dataset created in your Google BigQuery account (Adverity creates tables within this dataset). The name of this dataset(s) will populate the drop-down menu.
  • Recheck the user roles of the account that granted the connection.
  • If both of the above are definitely correct but it is still not working, try using a service account.

IMPORT OPTIONS

  • TRUNCATE + PARTITION BY DATE: The table is partitioned by a date column and data is only replaced based on date (this is the recommended option, and needs to be used with Local Data Retention > Unique By Date).
  • APPEND: Each extract is added to the existing table (advanced, should only be used for certain append-only types of data).
  • TRUNCATE: Each time import happens, the entire table is deleted and replaced by the most recent extract. (used in combination with bundle stream).
  • ERROR (NO ACTION): The Datastream will only create a table if it does not exist, and will not update it afterwards. If an import is attempted, BigQuery will return an error message.

All load jobs to BigQuery through Adverity are subject to the existing BigQuery quotas. Please be mindful of these and contact your BigQuery Account Manager to discuss the limit in case of questions.

GOOGLE-RELATED PRE-REQUISITES

BigQuery Datasource permissions

To authorize Adverity with BigQuery as a Datasource, the Google Account you use must include the following permissions:

  bigquery.jobs.get
  bigquery.jobs.list
  bigquery.jobs.create
  bigquery.tables.get
  bigquery.tables.getData
  bigquery.tables.list
  bigquery.datasets.get
See more information in Predefined roles and permissions and Granting, changing, and revoking access to resources in Google's documentation.
If your account does not include all of the above, the authorization will not be successful. In this case, grant access through the alternative method: Creating a Connection Using a Service Account.

BigQuery Destination permissions

To authorize Adverity with BigQuery as a Destination, the Google Account you use must include the following permissions:

  bigquery.jobs.get
  bigquery.jobs.list
  bigquery.jobs.create
  bigquery.tables.get
  bigquery.tables.getData
  bigquery.tables.list
  bigquery.datasets.get
  bigquery.tables.create
  bigquery.tables.update
  bigquery.tables.updateData
See more information in Predefined roles and permissions and Granting, changing, and revoking access to resources in Google's documentation.
If your account does not include all of the above, the authorization will not be successful. In this case, grant access through the alternative method: Creating a Connection Using a Service Account.

SCHEMA MAPPING

To create the correct data entries in the BigQuery table, you have to use Schema Mapping. You can use the defaults or create your own, further information can be found in Data Schema. 

 

INSERT DATA

  • Unlike a relational database, you cannot define a set of key columns. 
  • Whenever the structure of the table has changed it’s necessary to drop or alter the table. The table structure changes whenever the column datatype is changed.

For further details on how to start with Google BigQuery we recommend reading the Google BigQuery Documentation.

TABLE NAMES

Each Datastream will automatically generate a separate table in the Destination. The default name of the output file is datastreamtype_datastream-id e.g. adwords_83. To import several Datastreams into one table, change the table name accordingly.
  1. Navigate through Transfer > Destinations.
  2. Select the Google BigQuery Destination from the list to open the Destination Settings. 
  3. To configure a custom Datastream table name, select the Datastream in the left column, and put a value in the right column.
    Big_Query_Table_Names.PNG
Allowed characters for Table Name are "a-z, 0-9", and "_".
You can use the placeholders in the right column to configure dynamic file names:
  • {id} - Datastream ID.
  • {app_label} - Datastream Type Short Name.
  • {name} - Actual Extract's name.
  • %Y - Year (YYYY).
  • %m - Month (MM).
  • %d - Day (DD).

 

Related articles

Comments

0 comments

Article is closed for comments.