---
title: "ClickHouse® Integration Google Cloud Storage for Real-Time Analytics"
excerpt: "ClickHouse® Integration with Google Cloud Storage: real-time analytics at scale, optimized cost, and blazing-fast query speed."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-02-06 00:00:00"
publishedOn: "2026-02-06 00:00:00"
updatedOn: "2026-02-06 00:00:00"
status: "published"
---

ClickHouse® integration with Google Cloud Storage (GCS) solves four distinct problems: **(1) querying files in GCS without loading into ClickHouse®**, **(2) loading data from GCS to MergeTree tables for low-latency queries**, **(3) exporting results or datasets back to GCS**, and **(4) using GCS as remote storage (disk) or backup destination**.

The critical detail: ClickHouse® accesses GCS via the **XML API with HMAC credentials** (S3-compatible), not the typical JSON API.

Integration matters in 2026 because teams increasingly **separate compute from storage** to reduce costs while maintaining query performance.

GCS serves as **cost-effective cold storage** for historical data ($0.012/GB/month Nearline, $0.004/GB/month Coldline) versus local SSD costs. Teams use ClickHouse® for **sub-100ms queries** over data stored in GCS, combining **object storage economics** with **analytical database performance**.

Common patterns include: **data lake queries** (Parquet in GCS queried directly), **incremental loading** (GCS as staging before MergeTree), **tiered storage** (hot data local, cold data in GCS with TTL), and **event-driven ingestion** (Pub/Sub notifications triggering loads).

This article examines integration patterns, performance considerations, and operational practices for production deployments.

## **Integration Patterns Overview**

| Pattern | Use Case | Latency Profile | Best For |
| ----- | ----- | ----- | ----- |
| Direct Query (gcs() function) | Exploratory analytics, data lake queries | Variable (network \+ cold reads) | Ad-hoc analysis, data validation |
| Load to MergeTree | Production serving, dashboards | Sub-100ms (local storage) | High-concurrency analytics |
| S3Queue Table Engine | Continuous ingestion from GCS | Near real-time (polling-based) | Event data, logs, CDC streams |
| Pub/Sub \+ Worker | Event-driven ingestion | Real-time (push-based) | Low-latency ingestion, complex transformations |
| Remote Disk (Tiered Storage) | Hot-warm-cold architecture | Mixed (cache \+ remote) | Cost optimization, long retention |
| Backup/Restore | Disaster recovery, compliance | Batch (scheduled) | Archival, point-in-time recovery |

## **Authentication and Endpoints: The First Failure Point**

ClickHouse® accesses GCS using **HMAC keys** (not OAuth/service account keys) because it communicates via the **Cloud Storage XML API** (S3-compatible). Google Cloud explicitly documents HMAC keys for **authenticating requests to the Cloud Storage XML API**, not the JSON API used by most GCP tools.

### **Critical Configuration Details**

**Endpoint Format**: Use `https://storage.googleapis.com/<bucket>/<folder>/<file>` (path-style). **Not** `https://storage.cloud.google.com/...` which is the console URL. The XML API endpoint is different from the JSON API endpoint.

**HMAC Key Generation**: Create HMAC keys associated with a service account. Navigate to **Cloud Storage → Settings → Interoperability** in Google Cloud Console. Generate access key ID and secret—these function identically to AWS access keys.

**Batch Delete Limitation**: When configuring GCS as remote disk, **disable batch deletes**. GCS XML API doesn't support batch delete operations like S3. Add `<support_batch_delete>false</support_batch_delete>` to disk configuration.

```xml
<clickhouse>
  <storage_configuration>
    <disks>
      <gcs_disk>
        <type>s3</type>
        <endpoint>https://storage.googleapis.com/my-bucket/clickhouse-data/</endpoint>
        <access_key_id>GOOG1E...</access_key_id>
        <secret_access_key>...</secret_access_key>
        <support_batch_delete>false</support_batch_delete>
      </gcs_disk>
    </disks>
  </storage_configuration>
</clickhouse>
```

### **Security Best Practices**

**Credential Management**: Never embed HMAC keys in SQL queries for production. Use **named collections** (ClickHouse® open source) or environment variables. Rotate HMAC keys like any credential—they're equivalent to access keys.

**Least Privilege**: Create service accounts with **minimum required permissions**. For read-only queries: `roles/storage.objectViewer`. For ingestion: `roles/storage.objectUser`. For backups: `roles/storage.admin` on specific bucket.

**Encryption**: GCS provides **server-side encryption by default** (Google-managed keys). For compliance requirements, use **Customer-Managed Encryption Keys (CMEK)** via Cloud KMS. Configure in bucket settings, transparent to ClickHouse®.

## **Direct Queries: Reading Files from GCS Without Loading**

The `gcs()` table function (alias of `s3()`) enables **querying files in GCS directly** without loading into ClickHouse® tables. Supports `SELECT` and `INSERT`, with **glob patterns** for reading multiple files (`*`, `**`, `?`, `{abc,def}`, `{N..M}`).

This capability makes ClickHouse® a compelling [Google Analytics alternative](https://www.tinybird.co/blog/google-analytics-alternative-in-3-minutes) for teams seeking real-time event analysis without vendor lock-in.

### **Query In Place Example**

```sql
SELECT
  count() AS total_events,
  min(event_time) AS earliest,
  max(event_time) AS latest,
  uniq(user_id) AS unique_users
FROM gcs(
  'https://storage.googleapis.com/analytics-lake/events/date=2026-01-*/**/*.parquet',
  'GOOG1E...',
  'secret...',
  'Parquet'
);
```

**Glob patterns** resolve across directory hierarchy. Pattern `date=2026-01-*/**/*.parquet` matches all Parquet files under any `date=2026-01-*` prefix recursively.

### **When Query In Place Makes Sense**

**Exploratory analysis**: Data quality checks, schema validation, sampling before loading. Query without commitment to loading full dataset into ClickHouse®.

**Staging validation**: GCS as landing zone—validate data structure and content before incremental load to MergeTree. Catch schema mismatches early.

**Cost optimization**: Avoid **double storage** (GCS \+ ClickHouse®) when queries are infrequent. Compute cost on-demand versus continuous storage cost.

**Federated queries**: Join GCS data with ClickHouse® tables for one-off analysis without permanent ingestion.

### **When Query In Place Doesn't Make Sense**

**High-concurrency dashboards**: Remote reads add **latency variability** (network, throttling, cold reads). Sub-100ms SLAs require local MergeTree tables.

**Small file problem**: Thousands of tiny files create **per-object overhead** killing performance. HTTP requests per file, metadata lookups, connection overhead dominate. Compact to well-sized Parquet files (64-256 MB per file).

**Complex queries**: Heavy aggregations, multiple joins, window functions over remote data perform poorly. Load to MergeTree first for complex analytics.

### **Format Optimization: Parquet Best Practices**

**Parquet** is the recommended format for GCS-ClickHouse® integration. **Columnar storage** enables column pruning (read only needed columns). **Compression** reduces network transfer. **Predicate pushdown** filters rows at read time.

ClickHouse® provides specific guidance for **working with Parquet**: row group size (1M rows recommended), compression codec (SNAPPY for balance, ZSTD for maximum compression), and schema evolution patterns.

## **Loading from GCS to MergeTree: Production Serving**

For **production analytics serving**, load data from GCS staging to **MergeTree tables**. This separates **ingestion** (flexible, batch-friendly) from **serving** (optimized for queries).

### **Incremental Loading Pattern**

```sql
INSERT INTO analytics.events
SELECT
  toDateTime(event_time) AS event_time,
  user_id,
  event_name,
  JSONExtractString(props, 'utm_source') AS utm_source,
  JSONExtractString(props, 'utm_campaign') AS utm_campaign
FROM gcs(
  'https://storage.googleapis.com/events-staging/date=2026-01-31/*.parquet',
  'GOOG1E...',
  'secret...',
  'Parquet'
)
WHERE event_time >= '2026-01-31 00:00:00'
  AND event_time < '2026-02-01 00:00:00';
```

**Incremental approach**: Load by **date partition** or **timestamp range**. Track processed prefixes/dates to avoid reloading. Idempotent loads enable retries without duplicates.

### **Table Design for GCS Ingestion**

**Partition key**: Match GCS prefix structure. If GCS uses `date=YYYY-MM-DD/`, partition ClickHouse® table by `toYYYYMMDD(event_time)`. Enables **partition pruning** during queries and **dropping old partitions** efficiently.

**Sorting key**: Design `ORDER BY` based on **actual query filters**. Example: events filtered by `user_id` and `event_time` should order by `(user_id, toStartOfHour(event_time))`. ClickHouse® reads minimal granules.

**Deduplication**: If GCS contains duplicates or loads overlap, use **ReplicatedReplacingMergeTree** with version column. Or implement application-level deduplication tracking processed files.

### **Cluster Parallelization with s3Cluster**

For **large loads** (hundreds of GB, billions of rows), parallelize reads across ClickHouse® cluster with `s3Cluster()` (works with GCS via XML API compatibility).

```sql
INSERT INTO analytics.events
SELECT *
FROM s3Cluster(
  'my_cluster',
  'https://storage.googleapis.com/events/date=2026-01-*/*.parquet',
  'GOOG1E...',
  'secret...',
  'Parquet'
);
```

**Mechanism**: ClickHouse® resolves glob pattern, distributes files across cluster nodes. Workers read assigned files in parallel, scaling horizontally. Critical for multi-TB loads where single-node throughput insufficient.

This loading pattern is particularly effective for [user-facing analytics](https://www.tinybird.co/blog/user-facing-analytics) applications, where sub-100ms query responses are critical for dashboards, customer reporting tools, and interactive analytical experiences.

## **Continuous Ingestion: S3Queue Table Engine**

S3Queue table engine provides continuous ingestion from GCS without external orchestration. Functions as message queue over object storage—polls bucket prefix, processes new files, tracks state. It serves as a foundation for [real-time change data capture](https://www.tinybird.co/blog/real-time-change-data-capture) pipelines that require reliable, incremental updates to analytical stores.

### **S3Queue Architecture**

S3Queue **lists objects** in bucket/prefix, **processes** based on mode (ordered/unordered), **tracks state** in ZooKeeper (processed files, retry queue), and **commits** based on configurable thresholds.

**Ordered mode**: Processes files in **lexicographic order**. Tracks maximum processed filename. Files appearing "before" max ignored. Requires **monotonic naming** (e.g., `dt=YYYY-MM-DD/HH/MM/00001.parquet`).

**Unordered mode**: Tracks **set of processed files** via persistent ZooKeeper nodes. Any order acceptable. More flexible but more state overhead.

### **S3Queue Configuration Example**

```sql
CREATE TABLE analytics.events_queue
(
    event_time DateTime,
    user_id UInt64,
    event_name String,
    properties String
)
ENGINE = S3Queue(
    'https://storage.googleapis.com/events-staging/incoming/*.parquet',
    'GOOG1E...',
    'secret...',
    'Parquet'
)
SETTINGS
    mode = 'ordered',
    after_processing = 'move',
    move_path = '/processed/',
    processing_threads_num = 4,
    max_processed_files_before_commit = 100,
    s3queue_loading_retries = 3;
```

### **S3Queue Settings Impact**

**Processing threads** (`processing_threads_num`): Parallel file processing. Balance throughput versus resource consumption. 4-8 threads typical for medium workloads.

This ingestion model closely aligns with [streaming data](https://www.ibm.com/think/topics/streaming-data) principles, where continuous flow and low-latency handling of event streams are key for maintaining system responsiveness.

**After processing** (`after_processing`): Options include `keep` (leave in place), `delete` (remove after success), `move` (relocate to archive path), `tag` (add object metadata tag). Enables **inbox/outbox pattern** within bucket.

**Commit thresholds**: Configure when to commit state—by files processed (`max_processed_files_before_commit`), rows (`max_processed_rows_before_commit`), bytes, or time. Trade-off: frequent commits (lower loss on failure) versus performance (commit overhead).

**Polling intervals**: `polling_min_timeout_ms` and `polling_max_timeout_ms` with backoff. Reduce costs by avoiding excessive list operations. 1-5 second intervals common.

### **Monitoring S3Queue**

Inspect effective settings: `SELECT * FROM system.s3_queue_settings WHERE database = 'analytics' AND table = 'events_queue'`. Available since ClickHouse® 24.10.

Monitor processing: `SELECT * FROM system.s3queue WHERE zookeeper_path LIKE '%events_queue%'` shows files in processing, failed, processed states.

## **Event-Driven Ingestion: Pub/Sub Notifications**

For **immediate ingestion** (sub-second latency), configure **GCS Pub/Sub notifications** triggering worker processes that load to ClickHouse®.

### **Pub/Sub Notification Architecture**

**GCS notification**: Object finalize events (`OBJECT_FINALIZE`) publish to Pub/Sub topic. **Pub/Sub delivery**: Push or pull subscription delivers messages to worker. **Worker logic**: Receives notification, validates, loads file to ClickHouse® via INSERT.

### **Critical: At-Least-Once Semantics**

GCS guarantees **at-least-once delivery** to Pub/Sub. Pub/Sub guarantees **at-least-once delivery** to consumer. Result: **duplicates are normal**—same event with different message IDs.

**No ordering guarantees**: Messages may arrive out of order relative to object creation time.

**Idempotency requirement**: Workers must handle duplicate notifications safely. Use `generation` and `metageneration` from notification payload as deduplication keys.

### **Idempotent Worker Pattern**

```python
def handle_notification(message):
    bucket = message['bucket']
    object_name = message['name']
    generation = message['generation']

    # Check if already processed
    already_processed = clickhouse.query(
        f"SELECT count() FROM control.processed_files "
        f"WHERE bucket = '{bucket}' AND object = '{object_name}' "
        f"AND generation = {generation}"
    )

    if already_processed > 0:
        return  # Skip duplicate

    # Load to ClickHouse
    clickhouse.query(
        f"INSERT INTO analytics.events "
        f"SELECT * FROM gcs('https://storage.googleapis.com/{bucket}/{object_name}', ...)"
    )

    # Record as processed
    clickhouse.query(
        f"INSERT INTO control.processed_files VALUES "
        f"('{bucket}', '{object_name}', {generation}, now())"
    )
```

**Control table** tracks `(bucket, object, generation)` preventing duplicate loads. **Generation number** distinguishes object overwrites—same name, different generation.

### **Pub/Sub Configuration Requirements**

**Permissions**: Grant `roles/pubsub.publisher` to Cloud Storage service agent on topic. Requires `roles/storage.admin` on bucket and `roles/pubsub.admin` on project/topic for initial setup.

**Notification filters**: Configure event types (`OBJECT_FINALIZE`, `OBJECT_DELETE`, `OBJECT_ARCHIVE`) and prefix filters. Example: only notify on `incoming/*.parquet` to avoid triggering on processed files.

Modern event-driven ingestion patterns are especially valuable for [Internet of Things (IoT)](https://www.ibm.com/think/topics/internet-of-things) environments, where devices continuously generate telemetry data that must be processed and analyzed in near real time for operational visibility and automation.

## **Remote Storage: GCS as ClickHouse® Disk**

For **tiered storage** (hot-warm-cold architecture), configure GCS as **remote disk** in ClickHouse®. Data starts local (fast NVMe), migrates to GCS via TTL rules (cost-effective long-term storage).

### **Storage Policy Configuration**

```xml
<clickhouse>
  <storage_configuration>
    <disks>
      <local_nvme>
        <type>local</type>
        <path>/var/lib/clickhouse/hot/</path>
      </local_nvme>

      <gcs_cold>
        <type>s3</type>
        <endpoint>https://storage.googleapis.com/analytics-cold/clickhouse/</endpoint>
        <access_key_id>GOOG1E...</access_key_id>
        <secret_access_key>...</secret_access_key>
        <support_batch_delete>false</support_batch_delete>
        <metadata_path>/var/lib/clickhouse/disks/gcs_cold/</metadata_path>
      </gcs_cold>

      <gcs_cache>
        <type>cache</type>
        <disk>gcs_cold</disk>
        <path>/var/lib/clickhouse/cache/gcs/</path>
        <max_size>100Gi</max_size>
      </gcs_cache>
    </disks>

    <policies>
      <hot_to_cold>
        <volumes>
          <hot>
            <disk>local_nvme</disk>
          </hot>
          <cold>
            <disk>gcs_cache</disk>
          </cold>
        </volumes>
        <move_factor>0.2</move_factor>
      </hot_to_cold>
    </policies>
  </storage_configuration>
</clickhouse>
```

### **TTL for Automatic Tiering**

```sql
CREATE TABLE analytics.events
(
    event_time DateTime,
    user_id UInt64,
    event_name String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(event_time)
ORDER BY (user_id, event_time)
TTL event_time + INTERVAL 7 DAY TO VOLUME 'cold'
SETTINGS storage_policy = 'hot_to_cold';
```

**TTL rule**: Data **7 days old** moves from `hot` volume (local NVMe) to `cold` volume (GCS with cache). ClickHouse® background merges execute moves asynchronously.

### **Cache Layer Critical for Performance**

**Without cache**, queries hitting cold storage (GCS) suffer **network latency** on every read. **With cache**, frequently accessed data stays local (100 GB cache in example above).

**Cache validation**: ClickHouse® validates cache using **path \+ ETag**. If object changes in GCS (new ETag), cache invalidated automatically.

**Cache policy**: **LRU eviction**—least recently used segments evicted when cache full. Configure `max_size` based on working set (data queried regularly).

### **Performance Considerations**

**Insert latency**: If new data already "expired" by TTL, ClickHouse® may try moving to cold storage during INSERT, **blocking ingestion**. Mitigate by designing partitions so new data never starts expired.

**Query latency**: Cold reads add **100-300ms** versus local disk. Cache hit rate critical—monitor `system.metrics` for `FilesystemCacheSize` and `FilesystemCacheElements`.

**Cost**: Avoid **cross-region egress**. Place ClickHouse® compute in **same region** as GCS bucket. GCS egress $0.12/GB to different region, versus \~$0.01/GB same region (region-specific).

Locating compute and storage together not only reduces cost but also minimizes network hops, ensuring consistently [low latency](https://www.cisco.com/site/us/en/learn/topics/cloud-networking/what-is-low-latency.html) for analytical workloads.

## **Performance Optimization: Cache and Parallelism**

Query performance over GCS depends on **minimizing network roundtrips** and **maximizing cache hits**. ClickHouse® provides two cache layers: **filesystem cache** (disk) and **userspace page cache** (memory).

### **Filesystem Cache for Remote Reads**

**Filesystem cache** stores **file segments** from remote storage (GCS) on local disk with **LRU eviction**. Subsequent reads hit local disk instead of network.

Enable per query:

```sql
SELECT *
FROM gcs('https://storage.googleapis.com/data/*.parquet', 'HMAC', 'SECRET', 'Parquet')
SETTINGS
    enable_filesystem_cache = 1,
    filesystem_cache_name = 'gcs_cache';
```

**Cache invalidation**: Automatic via ETag comparison. Changed objects in GCS automatically invalidate cached segments.

**Clear cache** for benchmarking: `SYSTEM DROP FILESYSTEM CACHE 'gcs_cache'` removes all cached data forcing cold reads.

### **Userspace Page Cache for Hot Data**

**Userspace page cache** caches **hot segments in memory** within ClickHouse® process. Faster than filesystem cache (no disk I/O) for repeatedly accessed data.

Enable in session: `SET use_page_cache_for_disks_without_file_cache = 1`. Configure max size: `SET page_cache_max_size = '10Gi'`.

**Use case**: Queries hitting **same partitions repeatedly** (dashboards, real-time analytics). In-memory cache eliminates disk access latency entirely.

### **Monitoring Cache Effectiveness**

**Metrics**: Query `system.metrics` for cache state:

```sql
SELECT
    metric,
    value
FROM system.metrics
WHERE metric LIKE '%Cache%';
```

Key metrics: `FilesystemCacheSize` (bytes cached), `FilesystemCacheElements` (segments cached), `FilesystemCacheHits`, `FilesystemCacheMisses`.

**Query log**: Analyze `system.query_log` comparing cold versus warm query execution times. Cache working correctly shows **10x+ speedup** on repeated queries.

## **Backup and Restore to GCS**

ClickHouse® supports **BACKUP** and **RESTORE** commands with GCS as destination using S3-compatible endpoint.

### **Backup to GCS**

BACKUP TABLE analytics.events  
TO S3(  
    'https://storage.googleapis.com/backups-bucket/ClickHouse®/events/base\_backup',  
    'GOOG1E...',  
    'secret...'  
)  
SETTINGS compression\_level \= 3;

**Incremental backups**: Base backup followed by incremental backups referencing base. Reduces storage and transfer costs for large tables with small daily changes.

### **Restore from GCS**

RESTORE TABLE analytics.events  
FROM S3(  
    'https://storage.googleapis.com/backups-bucket/ClickHouse®/events/base\_backup',  
    'GOOG1E...',  
    'secret...'  
);

**Point-in-time recovery**: Restore to specific backup timestamp. Critical for compliance and disaster recovery.

### **Backup Best Practices**

**Separate bucket**: Use dedicated backup bucket with **lifecycle policies** (transition to Coldline after 30 days, delete after 1 year).

**Encryption**: Enable **encryption at rest** for backup bucket (default) or use CMEK for sensitive data.

**Retention**: Configure GCS **Object Lifecycle Management** automatically deleting old backups. Example: retain daily backups 7 days, weekly 30 days, monthly 1 year.

## **Decision Framework**

### **Choose Direct Query (gcs() function) when:**

* **Ad-hoc analysis** over data lake without loading  
* **Data validation** before ingestion to ClickHouse®  
* **Query frequency low**—cost of double storage unjustified  
* **Data rarely changes**—cache hit rate high  
* **Not recommended**: High-concurrency dashboards (latency variability), thousands of small files (overhead)

### **Choose Load to MergeTree when:**

* **Production serving** with sub-100ms SLAs required  
* **High query concurrency**—multiple dashboards/users simultaneously  
* **Complex queries**—heavy aggregations, joins, window functions  
* **Incremental loading**—GCS as staging, ClickHouse® as serving layer  
* **Not recommended**: Infrequent queries (storage cost for low utilization), data changes constantly (reload overhead)

### **Choose S3Queue when:**

* **Continuous ingestion** without external orchestrator  
* **Files arrive continuously**—streaming data, CDC, logs  
* **Pull-based** acceptable—polling interval 1-5 seconds sufficient  
* **File-based processing**—each file independent unit of work  
* **Not recommended**: Sub-second latency required (use Pub/Sub), complex transformations (use Dataflow)

### **Choose Pub/Sub \+ Worker when:**

* **Event-driven** ingestion with sub-second latency  
* **Complex transformations** before ClickHouse® load  
* **Orchestration** required—multiple downstream systems  
* **Push-based** required—immediate reaction to new files  
* **Not recommended**: Simple file processing (S3Queue simpler), low-latency not critical

### **Choose Remote Disk (Tiered Storage) when:**

* **Hot-warm-cold** architecture for cost optimization  
* **Long retention** (months to years) required  
* **Query pattern**: recent data hot, historical data cold  
* **Compute-storage separation** for independent scaling  
* **Not recommended**: All data queried equally (no clear hot/cold), consistent sub-100ms latency required across all data

## **Conclusion**

ClickHouse® integration with Google Cloud Storage enables **cost-effective analytics** over **object storage** while maintaining **query performance** through intelligent caching and table design. The key patterns—**direct queries**, **incremental loading**, **continuous ingestion** (S3Queue, Pub/Sub), **tiered storage**, and **backups**—each solve specific architectural challenges.

However, a critical principle remains: **object storage excels at cost-effective durability, not query performance**.

Teams should architect recognizing this trade-off: **GCS for storage economics** (cold data, archives, data lakes), **ClickHouse® MergeTree for query performance** (hot data, serving layer), and **caching** to bridge the gap when querying remote data. By choosing the right integration pattern for each use case—direct queries for exploration, MergeTree for serving, tiered storage for cost optimization—teams can deliver both the **storage economics** of cloud object storage and the **sub-100ms query latency** required for production analytics.

The integration succeeds when teams understand they're **composing specialized systems**: GCS for **durable, cost-effective storage**, ClickHouse® for **real-time analytical queries**, and integration patterns that **minimize network overhead** while **maximizing cache efficiency**. This separation delivers both cost optimization and performance without compromising either.