Using components: Google BigQuery Destination

Use the Google BigQuqery destination component to store the output of a data flow in a BigQuery table.

The destination component stores the data intermediately into Google Cloud Storage and then uses BigQuery's API to import the data into it.

Connection

Select an existing Google BigQuery connection or create a new one.

Destination Properties

  • Target table - the name of the target table in your BigQuery dataset. By default, if the table doesn't exist, it will be created automatically.
  • Automatically create table if it doesn't exist - if unchecked and the table doesn't exist, the job fails.
  • Automatically add missing columns - when checked, the job will check if each of the specified columns exist in the table and if one does not exist, it will add it. Note: Uncheck this if your target is a partitioned table (See below).

Inserting data into partitioned tables

Data can be inserted into a partitioned table by specifying the partition to insert into using the notation table$partition (e.g. events$20160405).

Operation type

Append (Insert only) - default behaviour. Data will only be appended to the target table

Overwrite (Truncate and insert) - truncate the target table before data is inserted into the target table.

Merge with existing data - incoming data is merged with existing data in the table. Requires setting the merge keys correctly in field mapping. Merge is done in the following manner:

  • A staging table is created and the data flow's data is stored in it.
  • The target table is copied onto a new table (target-copy) and is cleaned up.
  • Data that exists in the staging table that doesn't exist in target-copy is inserted into the target table. (new data)
  • Data that exists in both staging table and target-copy is joined, columns values are taken from the staging table unless they are null. (old/updated data).
  • Both staging table and target-copy table are dropped.
  • In case of an error, the process tries to copy the target-copy back on to the original table.

Note: In merge, the incoming data must be unique according to the key fields you selected. You may use the aggregate component or the limit component (partition by the key field(s) and limit to 1 record per partition) to make sure that the key fields are indeed unique.

Advanced options

  • Maximum errors - If this number of errors occurs in BigQuery while loading data into the table, the job fails.

Schema Mapping

Map the dataflow fields to the target table's columns.

Creating packages

  1. Creating a new package in New Xplenty
  2. Creating a workflow
  3. Working in the new package designer
  4. Validating a package
  5. Using components: Amazon Redshift Source
  6. Using components: Bing Ads Source
  7. Using components: Database Source
  8. Using components: Facebook Ads Insights Source
  9. Using components: File Storage Source
  10. Using components: Google Adwords source
  11. Using components: Google Analytics Source
  12. Using components: Google BigQuery Source
  13. Using components: Google Cloud Spanner Source
  14. Using components: MongoDB Source
  15. Using components: NetSuite Source
  16. Using components: Salesforce source
  17. Using components: Rest API Source
  18. Using components: Aggregate Transformation
  19. Using components: Assert Transformation
  20. Using components: Clone transformation
  21. Using components: Cross Join Transformation
  22. Using components: Distinct Transformation
  23. Using components: Filter Transformation
  24. Using components: Join Transformation
  25. Using components: Limit Transformation
  26. Using components: Rank Transformation
  27. Using components: Select Transformation
  28. Using components: Sort Transformation
  29. Using components: Union Transformation
  30. Using components: Window Transformation
  31. Using components: Sample Transformation
  32. Using components: Cube transformation
  33. Using components: Amazon Redshift Destination
  34. Using components: Database Destination
  35. Using components: File Storage Destination
  36. Using components: Google BigQuery Destination
  37. Using components: Google Spanner Destination
  38. Using components: MongoDB Destination
  39. Using components: Salesforce Destination
  40. Using components: Snowflake Destination (beta)
  41. Using and setting variables in your packages
  42. System and pre-defined variables
  43. Using pattern-matching in source component paths
  44. Using ISO 8601 string functions
  45. Using Expressions in Xplenty
  46. Xplenty Functions

Feedback and Knowledge Base