BigQuery Destination Plugin

Latest: v3.3.6

The BigQuery plugin syncs data from any CloudQuery source plugin(s) to a BigQuery database running on Google Cloud Platform.

The plugin currently only supports a streaming mode through the legacy streaming API. This is suitable for small- to medium-sized datasets, and will stream the results directly to the BigQuery database. A batch mode of operation is being developed to support larger datasets, but this is not currently supported.

Streaming is not available for the Google Cloud free tier (opens in a new tab).

Before you begin

Make sure that billing is enabled for your Cloud project. Learn how to check if billing is enabled on a project (opens in a new tab).
Create a BigQuery dataset that will contain the tables synced by CloudQuery. CloudQuery will automatically create the tables as part of a migration run on the first sync.
Ensure that you have write access to the dataset. See Required Permissions (opens in a new tab) for details.

Example config

kind: destination
spec:
  name: bigquery
  path: cloudquery/bigquery
  version: "v3.3.6"
  write_mode: "append"
  spec:
    project_id: ${PROJECT_ID}
    dataset_id: ${DATASET_ID}
    # Optional parameters
    # dataset_location: ""
    # time_partitioning: none # options: "none", "hour", "day"
    # service_account_key_json: ""
    # endpoint: ""
    # batch_size: 10000
    # batch_size_bytes: 5242880 # 5 MiB
    # batch_timeout: 10s

This example above expects the following environment variables to be set:

PROJECT_ID - The Google Cloud Project ID
DATASET_ID - The Google Cloud BigQuery Dataset ID

The BigQuery destination utilizes batching, and supports batch_size and batch_size_bytes.

Note that the BigQuery plugin only supports the append write mode.

Authentication

The BigQuery plugin authenticates using your Application Default Credentials (opens in a new tab). Available options are all the same options described here (opens in a new tab) in detail:

Local Environment:

gcloud auth application-default login (recommended when running locally)

Google Cloud cloud-based development environment:

When you run on Cloud Shell or Cloud Code credentials are already available.

Google Cloud containerized environment:

When running on GKE use workload identity (opens in a new tab).

Google Cloud services that support attaching a service account (opens in a new tab):

Services such as Compute Engine, App Engine and functions supporting attaching a user-managed service account which will CloudQuery will be able to utilize.

On-premises or another cloud provider

The suggested way is to use Workload identity federation (opens in a new tab)
If not available you can always use service account keys and export the location of the key via GOOGLE_APPLICATION_CREDENTIALS. (Not recommended as long-lived keys are a security risk)

BigQuery Spec

This is the top-level spec used by the BigQuery destination plugin.

project_id (string) (required)

The id of the project where the destination BigQuery database resides.
dataset_id (string) (required)

The name of the BigQuery dataset within the project, e.g. my_dataset. This dataset needs to be created before running a sync or migration.
dataset_location (string) (optional)

The data location of the BigQuery dataset. If set, will be used as the default location for job operations. Pro-tip: this can solve "dataset not found" issues for newly created datasets.
time_partitioning (string) (options: none, hour, day) (default: none)

The time partitioning to use when creating tables. The partition time column used will always be _cq_sync_time so that all rows for a sync run will be partitioned on the hour/day the sync started.
service_account_key_json (string) (optional) (default: empty).

GCP service account key content. This allows for using different service accounts for the GCP source and BigQuery destination. If using service account keys, it is best to use environment or file variable substitution.
endpoint (string) (optional)

The BigQuery API endpoint to use. This is useful for testing against a local emulator.
batch_size (integer) (optional) (default: 10000)

Number of records to write before starting a new object.
batch_size_bytes (integer) (optional) (default: 5242880 (5 MiB))

Number of bytes (as Arrow buffer size) to write before starting a new object.
batch_timeout (duration) (optional) (default: 10s (10 seconds))

Inactivity time before starting a new object.

Underlying library

We use the official cloud.google.com/go/bigquery (opens in a new tab) package for database connection.

Overview Types