Google BigQuery

This Flow connector materializes Flow collections into tables within a Google BigQuery dataset. It allows both standard and delta updates.

The connector uses your Google Cloud service account to materialize to BigQuery tables by way of files in a Google Cloud Storage (GCS) bucket. The tables in the bucket act as a temporary staging area for data storage and retrieval.

It is available for use in the Flow web application. For local development or open-source workflows, ghcr.io/estuary/materialize-bigquery:dev provides the latest version of the connector as a Docker image. You can also follow the link in your browser to see past image versions.

Performance considerations

Like other Estuary connectors, this is a real-time connector that materializes documents using continuous transactions. However, in practice, there are speed limitations. Standard BigQuery tables are limited to 1500 operations per day. This means that the connector is limited 1500 transactions per day.

To avoid running up against this limit, you should set the minimum transaction time to a recommended value of 2 minutes, or a minimum value of 1 minute. You do this by configuring the materialization's task shard. This causes an apparent delay in the materialization, but is necessary to prevent error. This also makes transactions more efficient, which reduces costs in BigQuery, especially for large datasets.

Instructions to set the minimum transaction time are detailed below.

Prerequisites

To use this connector, you'll need:

• A new Google Cloud Storage bucket in the same region as the BigQuery destination dataset.

• A Google Cloud service account with a key file generated and the following roles:

  • roles/bigquery.dataEditor on the destination dataset

  • roles/bigquery.jobUser on the project with which the BigQuery destination dataset is associated

  • roles/storage.objectAdmin on the GCS bucket created above

    See Setup for detailed steps to set up your service account.

The Flow collections you materialize must accommodate the following naming restrictions:

• Field names may not contain hyphens (-), or the materialization will fail.
• Field names must begin with a letter or underscore (_), or the materialization will fail.
• Field names may contain non-alphanumeric characters, but these are replaced with underscores in the corresponding BigQuery column name.
• If two field names become identical after special characters are replaced with underscores (for example, field! and field$ both become field_), the materialization will fail.
• Collection names may contain non-alphanumeric characters, but all such characters except hyphens are replaced with underscores in the BigQuery table name.

If necessary, you can add projections to your collection specification to change field names.

tip

If you haven't yet captured your data from its external source, start at the beginning of the guide to create a dataflow. You'll be referred back to this connector-specific documentation at the appropriate steps.

Setup

To configure your service account, complete the following steps.

1. Log into the Google Cloud console and create a service account. During account creation:

   1. Grant the user access to the project.
   2. Grant the user roles roles/bigquery.dataEditor, roles/bigquery.jobUser, and roles/storage.objectAdmin.
   3. Click Done.

2. Select the new service account from the list of service accounts. On the Keys tab, click Add key and create a new JSON key.

   The key is automatically downloaded. You'll use it to configure the connector.

Configuration

To use this connector, begin with data in one or more Flow collections. Use the below properties to configure a BigQuery materialization, which will direct one or more of your Flow collections to your desired tables within a BigQuery dataset.

A BigQuery dataset is the top-level container within a project, and comprises multiple tables. You can think of a dataset as somewhat analogous to a schema in a relational database. For a complete introduction to resource organization in Bigquery, see the BigQuery docs.

Properties

Endpoint

Property	Title	Description	Type	Required/Default
/project_id	Project ID	The project ID for the Google Cloud Storage bucket and BigQuery dataset.	String	Required
/billing_project_id	Billing project ID	The project ID to which these operations are billed in BigQuery. Typically, you want this to be the same as project_id (the default).	String	Same as project_id
/dataset	Dataset	Name of the target BigQuery dataset.	String	Required
/region	Region	The GCS region.	String	Required
/bucket	Bucket	Name of the GCS bucket.	String	Required
/bucket_path	Bucket path	Base path within the GCS bucket. Also called "Folder" in the GCS console.	String
/credentials_json	Service Account JSON	The JSON credentials of the service account to use for authorization.	String	Required

To learn more about project billing, see the BigQuery docs.

Bindings

Property	Title	Description	Type	Required/Default
/table	Table	Table name.	string	Required
/delta_updates	Delta updates.	Whether to use standard or delta updates	boolean	false

Shard configuration

Beta

UI controls for this workflow will be added to the Flow web app soon. For now, you must edit the materialization specification manually, either in the web app or using the CLI.

To avoid exceeding your BigQuery tables' daily operation limits as discussed in Performance considerations, complete the following steps when configuring your materialization:

1. Using the Flow web application or the flowctl CLI, create a draft materialization as you normally would.

   1. If using the web app, input the required values and click Discover Endpoint.
   2. If using the flowctl, create your materialization specification manually.

2. Add the shards configuration to the materialization specification at the same indentation level as endpoint and bindings. Set the minTxnDuration property to at least 1m (we recommend 2m). In the web app, you do this in the Catalog Editor.

   shards:
     minTxnDuration: 2m

   A full sample is included below.

3. Continue to test, save, and publish the materialization as usual.

Sample

materializations:
  ${PREFIX}/${mat_name}:
    endpoint:
      connector:
        config:
          project_id: our-bigquery-project
          dataset: materialized-data
          region: US
          bucket: our-gcs-bucket
          bucket_path: bucket-path/
          credentials_json: <secret>
        image: ghcr.io/estuary/materialize-bigquery:dev
    bindings:
    - resource:
        table: ${table_name}
      source: ${PREFIX}/${source_collection}
    shards:
      minTxnDuration: 2m

Delta updates

This connector supports both standard (merge) and delta updates. The default is to use standard updates.

Enabling delta updates will prevent Flow from querying for documents in your BigQuery table, which can reduce latency and costs for large datasets. If you're certain that all events will have unique keys, enabling delta updates is a simple way to improve performance with no effect on the output. However, enabling delta updates is not suitable for all workflows, as the resulting table in BigQuery won't be fully reduced.

You can enable delta updates on a per-binding basis:

    bindings:
    - resource:
        table: ${table_name}
        delta_updates: true
    source: ${PREFIX}/${source_collection}