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 Big Query 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

When authorizing Adverity with Big Query, the Google Account used must have the following the following permissions:

 bigquery.jobs.get
 bigquery.jobs.list
 bigquery.jobs.create
 bigquery.tables.get
 bigquery.tables.getData
 bigquery.tables.list
 bigquery.tables.create
 bigquery.datasets.get

This translates into a pre-configured role:

 roles/bigquery.admin
Further details and explanation can be found in Google's Documentation.
If your account does not have all of the above, authorization will not be successful. In this case, you will need to instead grant access via 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 to read 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.