---
title: "ClickHouse Integration with Amazon Glue Guide"
excerpt: "Master ClickHouse integration with Amazon Glue with ETL use cases, Glue catalog, and real-time analytics architecture tips."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-01-31 00:00:00"
publishedOn: "2026-01-31 00:00:00"
updatedOn: "2026-01-31 00:00:00"
status: "published"
---

Your data pipeline looked solid on paper. AWS Glue processes data from S3, transforms it with Spark, and loads results into your analytics database. Clean ETL architecture. The data engineering team approved the design.

Then you moved to production analytics serving.

Dashboard queries that should return in milliseconds take 3-5 seconds. Real-time metrics lag by 15 minutes because of batch processing windows. Users complain about stale data during business hours. Your Glue jobs run constantly, burning through DPUs, yet analytics performance remains mediocre.

Sound familiar?

This is the Glue integration trap. What works brilliantly for batch ETL and data lake management breaks when you need **real-time analytics serving** with sub-second query performance.

The problem isn't that AWS Glue is poorly designed. **Most teams misunderstand what Glue actually solves.** Glue excels at orchestrating Spark transformations and managing data lake metadata. It wasn't built to serve dashboards, power customer-facing analytics, or deliver sub-100ms API responses at high concurrency.

ClickHouse® integration with Amazon Glue typically follows three patterns: **(1) Glue as ETL loading ClickHouse for fast queries**, **(2) Glue Data Catalog as lakehouse metadata with ClickHouse querying Iceberg tables directly**, and **(3) hybrid architectures combining both**. The right pattern depends on whether you need batch transformation infrastructure, unified metadata governance, or both.

Let's explore what actually works.

## **Pattern 1: Glue Spark ETL Loading ClickHouse for Fast Analytics**

The most common pattern: **AWS Glue runs Spark jobs transforming data and loading results into ClickHouse tables** for low-latency serving.

### **Why this pattern exists**

Glue excels at orchestrating complex transformations—joining datasets from multiple sources, enriching with external data, applying business logic, running ML models. ClickHouse excels at serving analytical queries with sub-second latency on billions of rows.

**Glue provides transformation infrastructure. ClickHouse provides query performance.**

Separate the layers: heavy batch processing in Spark, fast serving in ClickHouse.

### **How to connect Spark to ClickHouse**

Two main approaches exist:

**Spark native connector (recommended):**

* DataSource V2 API integration with better parallelism  
* Pushdown optimization for filters and aggregations  
* Natural integration with Spark SQL and DataFrame APIs  
* Optimized batch writes to ClickHouse

**JDBC connector (simpler, less efficient):**

* Standard Spark JDBC interface  
* Works without additional dependencies  
* Lower performance for large-scale reads/writes  
* Easier for quick prototypes or small data volumes

### **The parallelization challenge**

Default JDBC reads execute on single Spark task—entire table scanned serially. **For large ClickHouse tables, this kills performance.**

Force parallel reads with partitioning:

df \= (  
  spark.read.format("jdbc")  
    .option("url", "jdbc:clickhouse://HOST:8123/DB")  
    .option("dbtable", "events")  
    .option("user", USER)  
    .option("password", PASS)  
    .option("partitionColumn", "event\_date")  
    .option("lowerBound", "2025-01-01")  
    .option("upperBound", "2025-12-31")  
    .option("numPartitions", "64")  
    .load()  
)

This creates 64 parallel tasks reading different date ranges simultaneously. **Read time: 10 minutes → 30 seconds** for billion-row table.

For writes, batch size matters enormously:

df.write \\  
  .format("jdbc") \\  
  .option("url", "jdbc:clickhouse://HOST:8123/DB") \\  
  .option("dbtable", "target\_table") \\  
  .option("batchsize", "100000") \\  
  .mode("append") \\  
  .save()

**ClickHouse performance depends heavily on insert batch size.** Many small inserts (1000 rows each) create excessive parts requiring background merges. Larger batches (50,000-100,000 rows) reduce merge overhead and improve query performance.

In many deployments, ClickHouse effectively acts as the downstream system for analytics serving, absorbing curated outputs from Spark while isolating query workloads from upstream batch complexity. This separation clarifies ownership and SLAs between transformation and serving layers, a pattern discussed extensively in the context of a [downstream system](https://medium.com/@ogunodabas/downstream-upstream-system-c1dc6cf4b59e).

### **The staging table pattern for idempotency**

Glue jobs sometimes rerun—failures, backfills, late-arriving data. **Direct loading creates duplicate data or inconsistent state.**

Production pattern:

1. **Glue writes to staging table** (append-only with batch metadata)  
2. **ClickHouse promotion query** deduplicates and moves to final table  
3. **Staging table TTL** removes old batches automatically

\-- Staging table with batch tracking  
CREATE TABLE events\_staging (  
  event\_id String,  
  user\_id UInt64,  
  event\_time DateTime,  
  batch\_id String,  
  ingestion\_time DateTime DEFAULT now()  
) ENGINE \= MergeTree()  
ORDER BY (batch\_id, event\_time);

\-- Promotion to final table (deduplicated)  
INSERT INTO events\_final  
SELECT
  event\_id,  
  user\_id,  
  event\_time  
FROM (  
  SELECT \*,  
    ROW\_NUMBER() OVER (  
      PARTITION BY event\_id
      ORDER BY ingestion\_time DESC  
    ) as rn  
  FROM events\_staging  
  WHERE batch\_id \= 'batch\_2025\_01\_28'  
)  
WHERE rn \= 1;

**Rerun the same Glue job safely.** Staging absorbs duplicates; promotion ensures idempotency.

### **When Glue ETL to ClickHouse makes sense**

Choose this pattern when:

* **Complex transformations** require Spark (large joins, ML, heavy aggregations)  
* **Multiple source systems** need unification before analytics serving  
* **Batch processing acceptable** (hourly, daily loads) for your freshness requirements  
* **ClickHouse serving critical** for dashboard and API performance  
* **Separation of concerns**—transformation infrastructure (Glue) versus query serving (ClickHouse)

**Don't choose this when:**

* **Real-time streaming** required (Kafka → ClickHouse more direct)  
* **Simple transformations** don't justify Spark overhead  
* **Continuous updates** needed (Glue batch windows add staleness)

One team shared: "We process clickstream data in Glue—sessionization, attribution modeling, fraud detection. Transformed results load to ClickHouse hourly. Dashboards query ClickHouse in 50ms. Separating heavy batch work from serving solved our performance problem."

## **Pattern 2: Glue Data Catalog as Lakehouse Metadata, ClickHouse Querying Directly**

The alternative pattern: **Data lives in S3 as Iceberg tables cataloged in Glue Data Catalog. ClickHouse queries lake tables directly without duplicating data.**

### **Why this pattern exists**

In modern [cloud computing](https://www.ibm.com/think/topics/cloud-computing), data lakes centralize storage with governance and compliance requirements. Multiple systems need access—Athena for ad-hoc queries, EMR for batch processing, ClickHouse for low-latency serving.

**Duplicating data into ClickHouse wastes storage and creates synchronization problems.** Query the lake directly when ClickHouse needs lake access.

### **How ClickHouse connects to Glue catalogs**

ClickHouse provides DataLakeCatalog [database](https://www.oracle.com/database/what-is-database/) engine discovering tables from external catalogs:

CREATE DATABASE glue\_lake  
ENGINE \= DataLakeCatalog  
SETTINGS
  catalog\_type \= 'glue',  
  region \= 'us-east-1';

\-- Discover tables  
SHOW TABLES FROM glue\_lake.analytics\_db;

\-- Query Iceberg table through catalog  
SELECT
  event\_date,  
  COUNT(\*) as events  
FROM glue\_lake.analytics\_db.user\_events  
WHERE event\_date \>= today() \- 7  
GROUP BY event\_date;

ClickHouse reads **Iceberg metadata** (manifests, snapshots) from Glue catalog, then reads **Parquet files** directly from S3. No data duplication—single source of truth in S3.

### **The Iceberg format requirement**

**Critical limitation: Glue catalog integration in ClickHouse primarily supports Iceberg format.** Don't assume Delta Lake, Hudi, or arbitrary Parquet work without validation.

Iceberg provides:

* **ACID transactions** on object storage  
* **Time travel** through snapshot isolation  
* **Schema evolution** without rewriting data  
* **Partition evolution** changing partitioning scheme over time  
* **Metadata optimization** for efficient query planning

If your lake uses other formats, either migrate to Iceberg or use Pattern 1 (ETL to ClickHouse tables).

### **Performance depends on lake health**

ClickHouse query performance on Iceberg depends entirely on **lakehouse maintenance**:

**Small files problem:**

* Thousands of tiny Parquet files slow queries dramatically  
* S3 list operations become bottleneck  
* ClickHouse reads more files than necessary

**Solution:** Regular compaction merging small files into larger ones (target: 128MB-512MB per file).

**Snapshot accumulation:**

* Iceberg creates new snapshot per write operation  
* Manifests accumulate over time  
* Query planning reads excessive metadata

**Solution:** Expire old snapshots regularly—retain only needed for time travel and compliance.

**Partition strategy:**

* Poor partitioning forces full table scans  
* ClickHouse can't prune effectively

**Solution:** Partition by query filter columns (date ranges, geographic regions, tenant IDs).

AWS Glue Data Catalog now supports **storage optimization for Iceberg tables** including automatic compaction and snapshot expiration policies. Configure these from the start.

### **Authentication and permissions complexity**

Querying Iceberg through Glue requires proper AWS credentials:

**ClickHouse needs:**

* **IAM role or access keys** with S3 read permissions  
* **Glue Data Catalog permissions** for metadata access  
* **Network connectivity** to S3 and Glue API endpoints

**If using Lake Formation:**

* Row-level and column-level security policies  
* Additional permissions management  
* Integration with Lake Formation grants

Configuration example:

CREATE DATABASE glue\_lake  
ENGINE \= DataLakeCatalog  
SETTINGS
  catalog\_type \= 'glue',  
  region \= 'us-east-1',  
  aws\_access\_key\_id \= '...',  
  aws\_secret\_access\_key \= '...',  
  \-- Or use IAM role if ClickHouse runs on EC2  
  use\_instance\_profile \= 1;

**Common failure:** Works in Athena but fails in ClickHouse due to missing IAM permissions or network access. Test connectivity thoroughly.

### **When querying Iceberg through Glue makes sense**

Choose this pattern when:

* **Data already lives in S3 as Iceberg** with governance requirements  
* **Multiple consumers** need same data (Athena, Spark, ClickHouse)  
* **Avoiding duplication** matters for storage costs or compliance  
* **Exploratory queries** on large infrequently-accessed datasets  
* **Lake Formation governance** required across organization

**Don't choose this when:**

* **Sub-100ms queries required** (lakehouse adds latency versus native ClickHouse tables)  
* **High concurrency** dashboard serving (S3 requests accumulate costs)  
* **Frequent access** to same data (materialization in ClickHouse more efficient)

One team explained: "We keep raw events in S3 Iceberg for compliance—7 years retention required. ClickHouse queries recent months directly through Glue catalog. Older data accessed occasionally via Athena. Single source of truth without duplicating petabytes."

## **Pattern 3: Hybrid Architecture Combining Both Approaches**

Most production systems combine patterns: **lakehouse governance with selective materialization in ClickHouse.**

### **The hybrid workflow**

1. **Raw data lands in S3 as Iceberg** cataloged in Glue Data Catalog  
2. **Glue Spark jobs** transform and curate datasets  
3. **Hot data materializes in ClickHouse** for serving dashboards and APIs  
4. **Cold data remains in lake** accessed occasionally through catalog  
5. **Glue provides unified metadata** for both lake and ClickHouse tables

### **Incremental loading with Glue Job Bookmarks**

Glue provides **job bookmarks** tracking processed data across runs—essential for incremental loads:

from awsglue.context import GlueContext  
from awsglue.job import Job

glueContext \= GlueContext(SparkContext.getOrCreate())  
job \= Job(glueContext)  
job.init(args\['JOB\_NAME'\], args)

\# Read with bookmark support  
datasource \= glueContext.create\_dynamic\_frame.from\_catalog(  
    database \= "analytics",  
    table\_name \= "events",  
    transformation\_ctx \= "datasource"  \# Stable context for bookmarking  
)

\# Transform and write to ClickHouse  
\# Only new partitions processed on subsequent runs

**Transformation context must remain stable** across job executions. Changing it resets bookmark state, potentially reprocessing all data.

For S3 sources, bookmarks track **new partitions** (partition-based incremental). For JDBC sources, define **bookmark key columns** filtering new rows.

### **When hybrid architecture makes sense**

Choose hybrid when:

* **Compliance requires lake retention** but serving requires speed  
* **Multiple analytical workloads** with different latency requirements  
* **Governance and lineage** managed centrally in Glue catalog  
* **Cost optimization**—expensive ClickHouse storage for hot data only  
* **Flexibility**—materialize datasets proving valuable for performance

Most enterprise deployments eventually arrive here. **Start with one pattern, evolve to hybrid as requirements clarify.**

## **How Tinybird Simplifies ClickHouse Integration**

Everything discussed—Spark connectors, staging tables, incremental bookmarks, catalog integration, idempotency patterns—requires significant engineering to implement and maintain, a recurring challenge across [real-time data platforms](https://www.tinybird.co/blog/real-time-data-platforms).

**Tinybird provides managed ClickHouse with built-in data pipeline capabilities** eliminating most Glue integration complexity.

### **Streaming ingestion without Glue jobs**

While Glue excels at batch Spark transformations, **most analytics workloads need continuous data ingestion**, not hourly/daily batch windows.

**Tinybird's streaming architecture:**

* **Kafka, webhooks, HTTP events** flow continuously into ClickHouse  
* **Sub-second queryability** from ingestion to query results  
* **No batch delays** or Glue job scheduling  
* **Incremental materialized views** maintain aggregations automatically

**No Glue jobs for simple data movement.** Stream directly to Tinybird, transform with SQL.

### **SQL transformations instead of Spark jobs**

For many transformations, Spark is overkill. Joins, aggregations, filtering, enrichment execute efficiently in SQL. These datasets can further benefit from [projections](https://www.tinybird.co/blog/projections) to precompute or reorganize data for faster query serving.

**Tinybird workflow:**

1. **Ingest raw events** continuously  
2. **SQL transformations** define materialized views  
3. **Instant APIs** serve results with sub-100ms latency

**No Glue job development, testing, or deployment.** Write SQL, get production data pipelines.

### **When Tinybird makes sense versus Glue integration**

**Choose Tinybird when:**

* **Real-time analytics** required (seconds, not hours)  
* **Operational simplicity** preferred over infrastructure control  
* **SQL transformations sufficient** (90% of cases)  
* **Streaming ingestion** from Kafka, webhooks, or events  
* **Sub-100ms serving** for dashboards and APIs critical

A common outcome of sub-100ms APIs is customer-facing use cases such as recommendations, dynamic pricing, and [real-time personalization](https://www.tinybird.co/blog/real-time-personalization), where fresh features and context must be computed and served continuously without batch windows.

**Keep Glue when:**

* **Complex Spark transformations** genuinely required (ML pipelines, heavy joins across disparate sources)  
* **Existing Glue investment** and expertise justify continuation  
* **AWS ecosystem integration** with EMR, Athena, SageMaker essential  
* **Lake governance** through Glue catalog mandated organizationally

Many teams use **both**: Glue for complex batch transformations landing in S3 lake, Tinybird streaming subsets from Kafka for real-time analytics serving. **Separate batch infrastructure from serving platforms.**

## **The Path Forward: Architecture Matching Requirements**

ClickHouse integration with Amazon Glue solves specific problems:

**Glue Spark ETL → ClickHouse** separates heavy batch transformations from fast query serving  
 **Glue Data Catalog with Iceberg** provides unified lakehouse metadata ClickHouse queries directly  
 **Hybrid approaches** combine governance, retention, and selective materialization

But these patterns require engineering investment:

* Connector configuration and parallelization tuning  
* Staging tables and idempotency logic  
* Job bookmark management for incrementals  
* Lake maintenance (compaction, snapshot expiration)  
* Authentication and permission coordination  
* Monitoring and cost optimization

**Tinybird provides purpose-built alternative** for teams where:

* Real-time matters more than batch windows  
* SQL transformations sufficient versus Spark complexity  
* Operational simplicity preferred over infrastructure control  
* Analytics serving (APIs, dashboards) is primary requirement

The choice is yours: build integration infrastructure connecting Glue and ClickHouse, or adopt platforms designed for real-time analytics serving from the start.

For analytics that stay fast as data scales—choose architectures built for the job.