> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> How to export data from BigQuery to ClickHouse Cloud using ClickPipes.

# Integrating BigQuery with ClickHouse Cloud

export const Image = ({img, alt, size}) => {
  return <Frame>
      <img src={img} alt={alt} />
    </Frame>;
};

<PrivatePreviewBadge />

<Note>
  You can sign up for the Private Preview waitlist [here](https://clickhouse.com/cloud/clickpipes/bigquery-connector).
</Note>

The BigQuery ClickPipe provides a fully-managed and resilient way to ingest data from BigQuery into ClickHouse Cloud. In Private Preview, it supports the **initial load** replication method to help you bulk load BigQuery datasets for exploration and prototyping. **CDC** will be supported in the future — in the meantime, we recommend using the [Google Cloud Storage ClickPipe](/integrations/clickpipes/object-storage/google-cloud-storage/overview) to continuously sync BigQuery data exports into ClickHouse Cloud once the initial load is completed.

BigQuery ClickPipes can be deployed and managed manually using the ClickPipes UI, as well as programmatically using [OpenAPI](/integrations/clickpipes/programmatic-access/openapi) and [Terraform](/integrations/clickpipes/programmatic-access/terraform).

<h2 id="features">
  Features
</h2>

<h3 id="initial-load">
  Initial load
</h3>

The BigQuery ClickPipe will load selected tables in a BigQuery [dataset](https://docs.cloud.google.com/bigquery/docs/datasets-intro) into the ClickHouse destination tables in a single batch operation. Once the ingestion task completes, the ClickPipe stops automatically. The initial load ingestion process requires a user-provided Google Cloud Storage (GCS) bucket for staging. In the future, the intermediary bucket will be provided and managed by ClickPipes.

<Note>
  ClickPipes relies on batch extract jobs to fetch data from BigQuery into the staging GCS bucket. This operations incurs **no processing charges** in BigQuery.
</Note>

<h3 id="cdc">
  CDC (Change Data Capture)
</h3>

CDC is **not supported** in Private Preview, but will be supported in the future. In the meantime, we recommend using the [Google Cloud Storage ClickPipe](/integrations/clickpipes/object-storage/google-cloud-storage/overview) to continuously sync BigQuery data exports into ClickHouse Cloud once the initial load is completed.

<h2 id="data-type-mapping">
  Data type mapping
</h2>

[BigQuery data types](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/data-types).

| BigQuery Data Type | ClickHouse Data Type | Details                                                           |
| ------------------ | -------------------- | ----------------------------------------------------------------- |
| `BOOL`             | `Bool`               |                                                                   |
| `INT64`            | `Int64`              |                                                                   |
| `FLOAT64`          | `Float64`            |                                                                   |
| `NUMERIC`          | `Decimal(P, S)`      | Precision up to 38, scale up to 9. Precision/scale is preserved.  |
| `BIGNUMERIC`       | `Decimal(P, S)`      | Precision up to 76, scale up to 38. Precision/scale is preserved. |
| `STRING`           | `String`             |                                                                   |
| `BYTES`            | `String`             |                                                                   |
| `JSON`             | `String` (JSON)      |                                                                   |
| `DATE`             | `Date`               |                                                                   |
| `TIME`             | `String`             | Microsecond precision.                                            |
| `DATETIME`         | `DateTime`           | Microsecond precision.                                            |
| `TIMESTAMP`        | `DateTime64(6)`      | Microsecond precision.                                            |
| `GEOGRAPHY`        | `String`             |                                                                   |
| `GEOMETRY`         | `String`             |                                                                   |
| `UUID`             | `String`             |                                                                   |
| `ARRAY<T>`         | `Array(T)`           |                                                                   |
| `ARRAY<DATE>`      | `Array(Date)`        |                                                                   |
| `STRUCT` (RECORD)  | `String`             |                                                                   |

<h2 id="access-control">
  Access control
</h2>

<h3 id="authentication">
  Authentication
</h3>

<h4 id="service-account-credentials">
  Service account credentials
</h4>

ClickPipes authenticates to your Google Cloud project using a [service account key](https://docs.cloud.google.com/iam/docs/keys-create-delete). We recommend creating a dedicated service account with the minimum required set of [permissions](#permissions) to allow ClickPipes to export data from BigQuery, load it into the staging GCS bucket, and read it into ClickHouse.

<Image img="https://mintcdn.com/private-7c7dfe99-fix-nav-issues/lGskH5qUgz9Vtlav/images/integrations/data-ingestion/clickpipes/bigquery/cp_iam.png?fit=max&auto=format&n=lGskH5qUgz9Vtlav&q=85&s=94a71a402fa88acc2607d1197cc0e444" alt="Creating a service account key with BigQuery and Cloud Storage permissions" size="lg" border width="2682" height="1336" data-path="images/integrations/data-ingestion/clickpipes/bigquery/cp_iam.png" />

<h3 id="permissions">
  Permissions
</h3>

<h4 id="bigquery">
  BigQuery
</h4>

The service account must have the following BigQuery roles:

* [`roles/bigquery.dataViewer`](https://docs.cloud.google.com/bigquery/docs/access-control#bigquery.dataViewer)
* [`roles/bigquery.jobUser`](https://docs.cloud.google.com/bigquery/docs/access-control#bigquery.jobUser)

To further scope access, we recommend using [IAM conditions](https://docs.cloud.google.com/bigquery/docs/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:

```bash theme={null}
resource.name.startsWith("projects/<PROJECT_ID>/datasets/<DATASET_NAME>")
```

<h4 id="cloud-storage">
  Cloud Storage
</h4>

The service account must have the following Cloud Storage roles:

* [`roles/storage.objectAdmin`](https://docs.cloud.google.com/storage/docs/access-control/iam-roles#storage.objectAdmin)
* [`roles/storage.bucketViewer`](https://docs.cloud.google.com/storage/docs/access-control/iam-roles#storage.bucketViewer)

To further scope access, we recommend using [IAM conditions](https://docs.cloud.google.com/bigquery/docs/conditions) to restrict the resources the role has access to. For example, you can restrict the `objectAdmin` and `bucketViewer` roles to the dedicated bucket created for ClickPipes syncs.

```bash theme={null}
resource.name.startsWith("projects/_/buckets/<BUCKET_NAME>")
```