ClickHouse^® Integration Google Cloud Storage for Real-Time Analytics

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

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.

<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 for teams seeking real-time event analysis without vendor lock-in.

Query In Place Example

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

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).

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 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 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

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 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

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) 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

<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

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 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:

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:

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.