Google BigQuery

IMPORT OPTIONS

• TRUNCATE + PARTITION BY DATE: the table is partitioned by a date column and data is only replaced based on date (recommended option, 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 Datatap 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.

CREATE DESTINATION

• Create a Google BigQuery Connection
• Select Connection and choose an existing dataset 
• You are now ready to start with BigQuery. Optionally you can also define table names per Datastream (for details see section SQL Database below).

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 in the 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
  
  • datatype of a column is changed

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

TABLE NAMES

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

• Go to Manage Organizations and select the Destination for the required Organization
• To configure a custom table name of a datastream, select the datastream in the left column and put a value in the right column
• 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)