Integrating Google Cloud Storage with ClickHouse Cloud

### Virtual columns[​](#virtual-columns "Direct link to Virtual columns")

To track which files have been ingested, include the `_file` virtual column to the column mapping list. The `_file` virtual column contains the filename of the source object, which can be used to query which files have been processed.

## Access control[​](#access-control "Direct link to Access control")

### Permissions[​](#permissions "Direct link to Permissions")

The GCS ClickPipe supports public and private buckets. [Requester Pays](https://docs.cloud.google.com/storage/docs/requester-pays) buckets are **not** supported.

#### GCS bucket[​](#gcs-bucket "Direct link to GCS bucket")

The service account used by ClickPipes must allow the following actions at the bucket level:

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

This role contains the [`storage.objects.list`](https://docs.cloud.google.com/storage/docs/json_api/v1/objects/list) and [`storage.objects.get](https://docs.cloud.google.com/storage/docs/json_api/v1/objects/get#required-permissions) IAM permissions, which allow ClickPipes to list and fetch objects in the specified bucket.

#### Pub/Sub subscription[​](#pubsub-subscription "Direct link to Pub/Sub subscription")

When using [unordered mode](#continuous-ingestion-any-order), the service account must have the following roles on the Pub/Sub subscription:

- [`roles/pubsub.subscriber`](https://cloud.google.com/pubsub/docs/access-control#roles) — to receive and acknowledge messages.
- [`roles/pubsub.viewer`](https://cloud.google.com/pubsub/docs/access-control#roles) — to get subscription metadata.

### Authentication[​](#authentication "Direct link to Authentication")

#### Service account[​](#service-account "Direct link to Service account")

Service Account authentication is required when using [unordered mode](#continuous-ingestion-any-order) with Pub/Sub notifications. Select **Service Account** as the authentication method and upload the service account key JSON file.

#### HMAC credentials[​](#hmac-credentials "Direct link to HMAC credentials")

To use [HMAC keys](https://docs.cloud.google.com/storage/docs/authentication/hmackeys) to authenticate, choose `Credentials` under **Authentication method** when setting up your ClickPipe connection. Then, provide the access key (e.g., `GOOGTS7C7FUP3AIRVJTE2BCDKINBTES3HC2GY5CBFJDCQ2SYHV6A6XXVTJFSA`) and secret key (e.g., `bGoa+V7g/yqDXvKRqq+JTFn4uQZbPiQJo4pf9RzJ`) under `Access key` and `Secret key`, respectively.

Follow [this guide](https://clickhouse.com/docs/integrations/gcs#create-a-service-account-hmac-key-and-secret) to create a service account with an HMAC key.

### Network access[​](#network-access "Direct link to Network access")

GCS ClickPipes use two distinct network paths for metadata discovery and data ingestion: the ClickPipes service and the ClickHouse Cloud service, respectively. If you want to configure an additional layer of network security (e.g., for compliance reasons), network access **must be configured for both paths**.

- For **IP-based access control**, the [IP filtering rules](https://docs.cloud.google.com/storage/docs/ip-filtering-overview) for your GCS bucket must allow the static IPs for the ClickPipes service region listed [here](/docs/integrations/clickpipes#list-of-static-ips), as well as the [static IPs](/docs/manage/data-sources/cloud-endpoints-api) for the ClickHouse Cloud service. To obtain the static IPs for your ClickHouse Cloud region, open a terminal and run:

  ```
  # Replace <your-region> with your ClickHouse Cloud region
  curl -s https://api.clickhouse.cloud/static-ips.json | jq -r '.gcp[] | select(.region == "<your-region>") | .egress_ips[]'
  ```

## Advanced settings[​](#advanced-settings "Direct link to Advanced settings")

ClickPipes provides sensible defaults that cover the requirements of most use cases. If your use case requires additional fine-tuning, you can adjust the following settings:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Setting Default value Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `Max insert bytes` 10GB Number of bytes to process in a single insert batch.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `Max file count` 100 Maximum number of files to process in a single insert batch.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `Max threads` auto(3) [Maximum number of concurrent threads](/docs/operations/settings/settings#max_threads) for file processing.| `Max insert threads` 1 [Maximum number of concurrent insert threads](/docs/operations/settings/settings#max_insert_threads) for file processing.| `Min insert block size bytes` 1GB [Minimum size of bytes in the block](/docs/operations/settings/settings#min_insert_block_size_bytes) which can be inserted into a table.| `Max download threads` 4 [Maximum number of concurrent download threads](/docs/operations/settings/settings#max_download_threads).| `Object storage polling interval` 30s Configures the maximum wait period before inserting data into the ClickHouse cluster.|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `Parallel distributed insert select` 2 [Parallel distributed insert select setting](/docs/operations/settings/settings#parallel_distributed_insert_select).| `Parallel view processing` false Whether to enable pushing to attached views [concurrently instead of sequentially](/docs/operations/settings/settings#parallel_view_processing).| `Use cluster function` true Whether to process files in parallel across multiple nodes. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

### Scaling[​](#scaling "Direct link to Scaling")

Object Storage ClickPipes are scaled based on the minimum ClickHouse service size determined by the [configured vertical autoscaling settings](/docs/cloud/features/autoscaling/vertical#configuring-vertical-auto-scaling). The size of the ClickPipe is determined when the pipe is created. Subsequent changes to the ClickHouse service settings won't affect the ClickPipe size.

To increase the throughput on large ingest jobs, we recommend scaling the ClickHouse service before creating the ClickPipe.

## Known limitations[​](#known-limitations "Direct link to Known limitations")

### File size[​](#file-size "Direct link to File size")

ClickPipes will only attempt to ingest objects that are **10GB or smaller** in size. If a file is greater than 10GB, an error will be appended to the ClickPipes dedicated error table.

### Compatibility[​](#compatibility "Direct link to Compatibility")

The GCS ClickPipe uses on the Cloud Storage [XML API](https://docs.cloud.google.com/storage/docs/interoperability) for interoperability, which requires using the `https://storage.googleapis.com/` bucket prefix (instead of `gs://`) and using [HMAC keys](https://docs.cloud.google.com/storage/docs/authentication/hmackeys) for authentication.

### View support[​](#view-support "Direct link to View support")

Materialized views on the target table are also supported. ClickPipes will create staging tables not only for the target table, but also any dependent materialized view.