Configure a BigQuery destination

This page describes how to configure Datastream so that you can write data to BigQuery datasets.

Required permissions

Datastream uses the BigQuery built-in support for change data capture (CDC) updates. Datastream updates the BigQuery tables by processing and applying streamed changes using the BigQuery Storage Write API.

The permissions required for using the API and ingesting data to BigQuery are granted to the Datastream Service Agent role.

Configure BigQuery as a destination

To configure Datastream for streaming to BigQuery, perform the following steps:

  1. Make sure that the BigQuery API is enabled in your Google Cloud project.
  2. If you want to use the Single dataset for all schemas option, create a dataset in BigQuery.
  3. Configure the destination dataset for your stream.
  4. Configure write mode.
  5. Specify the maximum data staleness limit for your stream.

Configure the destination datasets

Datasets are top-level containers that are used to organize and control access to your BigQuery tables.

When you configure datasets for the BigQuery destination using Datastream, you can select one of the following options:

  • Dataset for each schema: The dataset is selected or automatically created by Datastream in the BigQuery location specified, based on the schema name of the source. As a result, each schema in the source has a corresponding dataset in BigQuery. For example, if you have a MySQL source, and this source has a mydb database and an employees table within the database, then Datastream creates the mydb dataset and employees table in BigQuery.

    If you select this option, then Datastream creates datasets in the project that contains the stream. Although you don't need to create the datasets in the same region as your stream, we recommend to keep all resources for the stream, as well as datasets, in the same region for cost and performance optimization.

  • Single dataset for all schemas: You select a BigQuery dataset for the stream. Datastream streams all data into this dataset. For the dataset that you select, Datastream creates all tables as <schema>_<table>.

    For example, if you have a MySQL source, and this source has a mydb database and an employees table within the database, then Datastream creates the mydb_employees table in the dataset that you select.

Configure write mode

There are two modes you can use to define how you want your data written to BigQuery:

  • Merge: This is the default write mode. When selected, BigQuery reflects the way your data is stored in the source database. This means that Datastream writes all changes to your data to BigQuery, and BigQuery then consolidates the changes with existing data, thus creating final tables that are replicas of the source tables. With merge mode, no historical record of the change events is kept. For example, if you insert and then update a row, BigQuery only keeps the updated data. If you then delete the row from the source table, BigQuery no longer keeps any record of that row.

  • Append-only: The append-only write mode lets you add data to BigQuery as a stream of changes (INSERT, UPDATE-INSERT, UPDATE-DELETE and DELETE events). Use this mode when you need to retain the historical state of your data. To get a better understanding of the append-only write mode, consider the following scenarios:

    • Initial backfill: after the initial backfill, all events are written to BigQuery as INSERT type events, with the same timestamp, universally unique identifier (UUID), and change sequence number.
    • Primary key update: when a primary key changes, two rows are written to BigQuery:
      • An UPDATE-DELETE row with the original primary key
      • An UPDATE-INSERT row with the new primary key
    • Row update: when you update a row, a single UPDATE-INSERT row is written to BigQuery
    • Row deletion: when you delete a row, a single DELETE row is written to BigQuery

Specify data staleness limit

BigQuery applies source modifications in the background on an ongoing basis, or at query run time, according to the configured data staleness limit. When Datastream creates a new table in BigQuery, the table's max_staleness option is set according to the current data staleness limit value for the stream.

For more information about using BigQuery tables with the max_staleness option, see Table staleness.