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.
Pre-requisites
- You must have privileges to manage service accounts and IAM roles in your GCP project, or get assistance from an administrator. We recommend creating a dedicated service account with the minimum required set of permissions following the official documentation.
- An initial load requires a Google Cloud Storage (GCS) bucket for staging. We recommend creating a dedicated bucket for your ClickPipe following the Google Cloud instructions.
- For continuous replication, grant the service account the BigQuery Read Session User role on the project. Check the requirements for your replication method before selecting tables.
1
Select the data source
1. In ClickHouse Cloud, select Data sources in the main navigation menu and click Create ClickPipe.2. Click the BigQuery tile.
2
Set up your ClickPipe connection
Provide the connection details for your BigQuery project. If you are running an initial load, provide a staging GCS bucket.1. Upload the
.json key for the service account you created for ClickPipes. Ensure the service account has the minimum required set of permissions.2. Select the Replication mode: initial load only, Initial load + CDC, or CDC only. For a CDC mode, choose a query based or events based method.3. If you are running an initial load, provide the path to the GCS bucket for staging data.4. Click Next to validate.3
Configure your ClickPipe
Set the following values for the phases you selected. Initial load settings apply only when the ClickPipe imports existing data; CDC settings apply only when it continues replicating changes.
4
Configure tables
1. Select the ClickHouse database where you want the BigQuery tables to be replicated to. You can select an existing database or create a new one.2. Select the source tables. You can change each destination table name and choose which columns to replicate under Column settings. Only datasets that the service account can access appear in the list.3. Set the CDC option for each table. The screenshot shows query based settings. Select a CDC watermark column of type
TIMESTAMP, keep it among the replicated columns, and update its value whenever the row changes. With events based replication, select either APPENDS or CHANGES instead. CHANGES requires enable_change_history = TRUE on the source table.4. Review the Sorting key. If rows can be updated or deleted, choose stable columns that identify each row. For CHANGES, the source table must have a primary key or you must set a sorting key here. ClickHouse uses the sorting key to organize data for queries; without one, it uses tuple() and has no primary index.5. Open Advanced settings to choose the ClickHouse table Engine or an optional partitioning key for the initial load. This partitioning key does not set the destination table’s PARTITION BY expression.5
Configure permissions
Finally, you can configure permissions for the internal ClickPipes user.Permissions: ClickPipes will create a dedicated user for writing data into a destination table. You can select a role for this internal user using a custom role or one of the predefined roles:
Full access: full access to the cluster. Required if you use materialized views or dictionary with the destination table.Only destination: insert permissions to the destination table only.
6
Complete setup
Click Create ClickPipe to complete the setup. You’ll be redirected to the overview page, where you can the progress of the initial load and click through to see the details for your BigQuery ClickPipes.