You can sign up for the Private Preview waitlist here.
APPENDS, and CHANGES.
BigQuery ClickPipes can be deployed and managed manually using the ClickPipes UI, as well as programmatically using OpenAPI and Terraform.
Features
Initial load
An initial load copies selected tables from a BigQuery dataset to ClickHouse. You can run it on its own or follow it with continuous replication. The load uses a Google Cloud Storage (GCS) bucket that you provide for staging. An initial-load-only ClickPipe stops when the load finishes.ClickPipes uses BigQuery
EXPORT DATA queries to copy data to the staging bucket. BigQuery charges for running those queries, and GCS storage charges apply.Continuous replication
After the initial load, a BigQuery ClickPipe can poll for new data and apply it to ClickHouse. You can also start continuous replication without an initial load. Choose a replication method based on how the source table changes. These examples show the query shape; ClickPipes selects the configured columns and event metadata.
For query based replication, select a watermark column for each table. It must be a
TIMESTAMP column and must be included in the replicated columns. Set it when inserting or updating a row. If a row changes without a newer watermark value, ClickPipes will not pick up that change. Use CHANGES if you need deletes replicated.
Both events based methods require regular BigQuery tables. APPENDS does not require change history to be enabled. To use CHANGES, enable change history on each source table before creating the ClickPipe:
CHANGES also requires a BigQuery primary key or a custom sorting key configured for the destination table. Choose columns that identify a row so ClickPipes can apply updates and deletes to the correct row. BigQuery does not enforce primary key constraints, so the source data must keep those values unique.
APPENDS and CHANGES can read only changes within the source table’s BigQuery time travel window, which is seven days by default and can be as short as two days. If replication falls behind that window, run a fresh initial load to restore a complete copy. BigQuery charges for queries made by all three methods. Enabling change history for CHANGES also adds BigQuery storage costs.Data type mapping
BigQuery data types.Access control
Authentication
Service account credentials
ClickPipes authenticates to your Google Cloud project using a service account key. We recommend creating a dedicated service account with the minimum required set of permissions to allow ClickPipes to export data from BigQuery, load it into the staging GCS bucket, and read it into ClickHouse.GCP workload identity (Beta)
Follow the GCP workload identity guide to retrieve the managed service account from the ClickPipes service context. Grant it the BigQuery and GCS roles below, and create the pipe withSERVICE_ACCOUNT_WORKLOAD_IDENTITY authentication. Do not provide service account credentials with this authentication method.
Permissions
BigQuery
The service account used by ClickPipes, whether provided by you or managed through workload identity, needs these BigQuery roles: For continuous replication, also grantroles/bigquery.readSessionUser on the Google Cloud project. ClickPipes uses the BigQuery Storage Read API to create read sessions and read CDC query results. This role cannot be granted at the dataset level; keep roles/bigquery.dataViewer on the source datasets or tables so the service account can read their data.
To further scope access, we recommend using IAM conditions to restrict the resources the role has access to. For example, you can restrict the dataViewer role to the specific dataset containing the tables you want to sync:
Cloud Storage
The service account must have the following Cloud Storage roles: To further scope access, we recommend using IAM conditions to restrict the resources the role has access to. For example, you can restrict theobjectAdmin and bucketViewer roles to the dedicated bucket created for ClickPipes syncs.