ClickHouse Integration with Azure Event Hubs guide

Your streaming data pipeline looked solid on paper. Azure Event Hubs ingests events from applications, mobile devices, and IoT sensors. ClickHouse® consumes from Event Hubs topics and loads data for analytics. Clean streaming architecture. The data engineering team approved the design.

Then you moved to production analytics serving.

Queries that should return in milliseconds take 2-4 seconds. Consumer lag grows steadily until you're hours behind real-time. Dashboard users see stale data during peak hours. Your Event Hubs throttles constantly, burning through throughput units while ClickHouse struggles to keep up.

Sound familiar?

This is the Event Hubs integration trap. What works for moderate event volumes breaks when you need high-throughput ingestion with sub-second analytics serving at scale.

The problem isn't that Azure Event Hubs is poorly designed. Most teams misunderstand what Event Hubs actually solves. Event Hubs excels at durable event ingestion with Kafka compatibility. It wasn't built to serve dashboards, power customer-facing analytics, or deliver sub-100ms API responses—that's ClickHouse's job.

ClickHouse integration with Azure Event Hubs typically follows three patterns: (1) Kafka table engine consuming directly from Event Hubs, (2) Kafka Connect acting as integration hub, and (3) managed ingestion through ClickHouse Cloud ClickPipes. The right pattern depends on operational complexity tolerance, throughput requirements, and team expertise.

Let's explore what actually works in production.

Pattern 1: ClickHouse Kafka Table Engine Consuming Event Hubs

The most direct pattern: ClickHouse Kafka table engine consumes from Event Hubs Kafka endpoint, transforming and loading data through materialized views.

Why this pattern exists

Event Hubs exposes Kafka-compatible endpoint (1.0+) supporting standard Kafka protocol—topics, partitions, consumer groups, offsets. ClickHouse provides native Kafka table engine acting as consumer.

Event Hubs provides durable event streaming. ClickHouse provides transformation and query serving.

Minimal components: Event Hubs publishes events, ClickHouse consumes and serves analytics. No separate stream processors or connectors.

How to connect ClickHouse to Event Hubs

Event Hubs Kafka endpoint requires SASL_SSL authentication with connection string:

Connection configuration:

• bootstrap.servers: <namespace>.servicebus.windows.net:9093
• security.protocol: SASL_SSL
• sasl.mechanism: PLAIN
• sasl.jaas.config: username="$ConnectionString" and password="<full-connection-string>"

Critical detail: Username is literally $ConnectionString (not your namespace name). This trips up most failed authentication attempts.

The staging pattern for production reliability

Direct consumption works for prototypes. Production requires staging tables with materialized views for transformation and error handling.

Three-layer architecture:

1. Kafka queue table consumes from Event Hubs (ephemeral)
2. Materialized view transforms and loads to destination
3. Destination MergeTree table stores queryable data

Step 1: Define destination table (MergeTree)

CREATE TABLE events_raw (
event_time DateTime64(3),
event_name String,
user_id String,
properties_json String,
ingestion_time DateTime DEFAULT now()
)
ENGINE = MergeTree
PARTITION BY toDate(event_time)
ORDER BY (event_time, event_name, user_id);

Partition by date for query pruning and TTL management. Order by query filters for sparse index efficiency.

Step 2: Define Kafka queue table

CREATE TABLE events_queue (
event_time DateTime64(3),
event_name String,
user_id String,
properties_json String
)
ENGINE = Kafka
SETTINGS
kafka_broker_list = '<namespace>.servicebus.windows.net:9093',
kafka_topic_list = 'events',
kafka_group_name = 'clickhouse-events-consumer',
kafka_format = 'JSONEachRow',
kafka_num_consumers = 4,
kafka_max_block_size = 500000;

Step 3: Materialized view for transformation

CREATE MATERIALIZED VIEW events_consumer
TO events_raw
AS SELECT
event_time,
event_name,
user_id,
properties_json
FROM events_queue;

ClickHouse automatically consumes messages and applies materialized view transformation continuously.

Performance tuning for throughput

Default Kafka engine settings optimize for simplicity, not throughput. Production requires tuning.

Critical settings:

kafka_max_block_size (default too small):

• Default: 65,536 rows
• Production: 500,000-1,000,000 rows
• Impact: Larger blocks reduce insert overhead and parts created

kafka_thread_per_consumer (enable parallelism):

• Enables independent flush per consumer thread
• Prevents blocking when one partition has large batches
• Essential for multi-partition topics

kafka_num_consumers (match partitions):

• Should equal or exceed Event Hubs partition count
• More consumers ≠ always better (memory/CPU overhead)
• Scale horizontally across ClickHouse nodes instead

Example optimized configuration:

ENGINE = Kafka
SETTINGS
kafka_broker_list = '<namespace>.servicebus.windows.net:9093',
kafka_topic_list = 'events',
kafka_group_name = 'clickhouse-consumer',
kafka_format = 'JSONEachRow',
kafka_num_consumers = 8,
kafka_max_block_size = 1000000,
kafka_thread_per_consumer = 1,
kafka_commit_every_batch = 0;

Result: 10,000 events/sec → 100,000 events/sec with same infrastructure through tuning alone.

Connection stability and timeout configuration

Event Hubs closes idle TCP connections after inactivity. Without proper timeouts, consumers experience intermittent failures.

Required Kafka client configuration:

• connections.max.idle.ms: < 240,000ms (Event Hubs closes after 4 minutes idle)
• metadata.max.age.ms: < 240,000ms (keep metadata fresh)
• request.timeout.ms: ≥ 60,000ms (allow for throttling delays)
• session.timeout.ms: ≥ 30,000ms (prevent spurious rebalances)

Configure these in ClickHouse Kafka engine configuration file (not SQL DDL) for global application.

Error handling without stopping consumption

Production topics contain malformed messages—schema changes, corrupted data, encoding issues. Default behavior: stop consumption on first error.

Better approach: dead letter queue pattern using virtual columns:

-- Dead letter table for unparseable messages
CREATE TABLE events_deadletter (
raw_message String,
error String,
topic String,
partition UInt64,
offset UInt64,
ingestion_time DateTime DEFAULT now()
)
ENGINE = MergeTree
ORDER BY (ingestion_time, topic, partition);

-- Materialized view catching parse errors
CREATE MATERIALIZED VIEW events_error_handler
TO events_deadletter
AS SELECT
_raw_message as raw_message,
_error as error,
_topic as topic,
_partition as partition,
_offset as offset
FROM events_queue
WHERE _error != '';

Virtual columns _error and _raw_message expose parse failures. Messages that fail parsing route to dead letter table; valid messages continue normally.

No consumption blocking. Investigate failures asynchronously.

When direct Kafka engine makes sense

Choose ClickHouse Kafka table engine when:

• Minimal infrastructure preferred (no Kafka Connect deployment)
• SQL transformations sufficient versus complex stream processing
• Team expertise in ClickHouse stronger than stream processing frameworks
• Low latency critical (direct consumption eliminates intermediate hops)
• Operational simplicity valued over separation of concerns

Don't choose this when:

• Complex transformations require stateful processing, windowing, or joins
• Schema registry integration essential for Avro/Protobuf with centralized schema management
• Organizational standardization on Kafka Connect for all streaming integrations

One team shared: "We tried Kafka Connect first—operational overhead killed us. Switched to ClickHouse Kafka engine with materialized views. Same throughput, 1/3 the infrastructure, SQL transformations our team already knows."

Pattern 2: Kafka Connect as Integration Hub

Alternative pattern: Kafka Connect consumes from Event Hubs and sinks to ClickHouse with centralized integration management.

Why this pattern exists

Kafka Connect provides standardized framework for streaming integrations—connectors, transforms, error handling, schema management—separate from ClickHouse.

Kafka Connect handles integration complexity. ClickHouse focuses on serving.

Better fit when organizational standards require Connect, or when complex serialization (Avro, Protobuf with schema registry) is required.

How Kafka Connect integrates Event Hubs and ClickHouse

Event Hubs supports Kafka Connect as Kafka-compatible broker. ClickHouse provides Kafka Connect sink connector.

Architecture flow:

1. Event Hubs receives events from producers
2. Kafka Connect consumes via Kafka protocol
3. Connect transforms apply (optional)
4. ClickHouse sink connector writes to tables
5. ClickHouse serves queries as the analytics database

Benefits over direct consumption:

• Centralized operations through Connect REST API
• Standardized monitoring across all connectors
• Schema registry integration for Avro/Protobuf
• Transforms apply centrally without ClickHouse changes before data lands in each downstream system
• Dead letter queues managed by Connect framework

ClickHouse sink connector configuration

{
"name": "clickhouse-sink",
"config": {
"connector.class": "com.clickhouse.kafka.connect.ClickHouseSinkConnector",
"tasks.max": "4",
"topics": "events",
"hostname": "clickhouse.example.com",
"port": "8123",
"database": "analytics",
"table": "events_raw",
"format": "JSONEachRow",
"key.converter": "org.apache.kafka.connect.json.JsonConverter",
"value.converter": "org.apache.kafka.connect.json.JsonConverter",
"errors.tolerance": "all",
"errors.deadletterqueue.topic.name": "events-dlq"
}
}

Exactly-once semantics configurable through connector settings (with caveats—validate for your use case).

When Kafka Connect makes sense

Choose Kafka Connect when:

• Organizational standards mandate Connect for streaming integrations
• Schema registry with Avro/Protobuf centrally managed
• Multiple sinks consume same Event Hubs topics (ClickHouse, S3, Elasticsearch)
• Operations team already manages Connect infrastructure
• Transforms apply business logic before ClickHouse

Trade-off: More operational complexity (Connect deployment, configuration, monitoring) versus direct consumption simplicity.

One team explained: "We have 15 Kafka Connect connectors already. Adding ClickHouse sink was 30 minutes of configuration. Direct consumption would mean new operational model just for ClickHouse."

Pattern 3: Managed Ingestion with ClickHouse Cloud ClickPipes

For ClickHouse Cloud users: ClickPipes provides managed Event Hubs integration without infrastructure operations—an approach aligned with modern cloud computing practices.

What ClickPipes provides

ClickPipes abstracts Event Hubs integration complexity:

• Managed consumption from Event Hubs topics
• Automatic schema inference and evolution
• Built-in error handling and monitoring
• No infrastructure to deploy or maintain
• Regional colocation recommendations for latency

Configure through UI or API:

1. Event Hubs connection details
2. Topic selection
3. Destination ClickHouse table
4. Schema mapping
5. Start ingestion

Operational burden: zero. ClickHouse Cloud manages consumers, offsets, and scaling.

When ClickPipes makes sense

Choose ClickPipes when:

• Using ClickHouse Cloud already
• Operational simplicity preferred over infrastructure control
• Rapid deployment prioritized
• Managed service acceptable for streaming ingestion

Limitation: ClickHouse Cloud only. Self-managed deployments use Kafka engine or Connect patterns.

The At-Least-Once Delivery Reality

Critical understanding: Event Hubs provides at-least-once delivery semantics. Duplicate messages will arrive. Your ClickHouse pipeline must handle them.

Three deduplication strategies

Strategy 1: ReplacingMergeTree with version column

CREATE TABLE events_deduplicated (
event_id String,
event_time DateTime64(3),
user_id String,
data String,
version DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree(version)
PARTITION BY toDate(event_time)
ORDER BY (event_id, event_time);

Deduplication happens during background merges. Query with FINAL for deduplicated view (slower) or accept transient duplicates until merge completes.

Strategy 2: Staging with explicit deduplication

Load to staging table, deduplicate in scheduled query to final table:

INSERT INTO events_final
SELECT event_id, event_time, user_id, data
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY event_id ORDER BY version DESC
) as rn
FROM events_staging
WHERE ingestion_date = today()
)
WHERE rn = 1;

Control over deduplication timing versus automatic background merges.

Strategy 3: Idempotent aggregations

For analytics, often duplicates don't matter if aggregations are idempotent:

SELECT event_date,
uniqExact(event_id) as unique_events, -- Handles duplicates
count() as total_events -- Includes duplicates
FROM events_raw
GROUP BY event_date;

Simpler than explicit deduplication when use case permits.

Consumer offset management

ClickHouse commits offsets to Event Hubs (Kafka protocol) automatically. Understanding offset behavior prevents data loss or duplication:

Default behavior:

• Commits offsets after successfully writing batch to ClickHouse
• Failure before commit = reprocess messages (duplicates)
• Failure after commit = messages lost if batch write failed

kafka_commit_every_batch setting:

• Default (0): Commit after writing full block
• Enabled (1): Commit after each batch processed
• Trade-off: More commits (overhead) versus smaller replay window

For most cases: accept at-least-once, design for idempotency rather than fighting for exactly-once semantics.

Throughput Limits and Throttling

Event Hubs enforces throughput limits based on Throughput Units (TUs) in Standard tier or Processing Units (PUs) in Premium/Dedicated.

Understanding throttling behavior

When exceeding capacity:

• AMQP protocol: Immediate ServerBusy error
• Kafka protocol: Request delays with throttle_time_ms in response

ClickHouse perspective: Throttling appears as increased latency, growing consumer lag, and slow offset commits.

Detecting throttling

Azure Monitor metrics:

• ThrottledRequests: Direct throttling indicator
• IncomingMessages vs OutgoingMessages: Spot capacity gaps
• ActiveConnections: Track connection utilization

ClickHouse indicators:

• Growing lag in system.kafka_consumers
• Increased latency in consumption metrics
• Timeout errors in ClickHouse logs

Mitigation strategies

Scale capacity:

• Increase TUs (Standard) or PUs (Premium)
• Enable Auto-Inflate (Standard tier)
• Upgrade to Premium/Dedicated for higher limits

Optimize partitioning:

• Distribute events evenly across partitions
• Avoid hot partitions from skewed partition keys
• Use hash-based partition key (user_id, device_id) versus low-cardinality keys (country, plan)

Producer-side optimization:

• Batch events before publishing
• Compress payloads
• Implement exponential backoff on throttling

One team discovered: "We were throttling constantly with 32 partitions. Root cause: 80% of events went to 3 partitions due to partition key skew. Switched from tenant_id to hash(event_id) and throttling disappeared."

Event Hubs Capture for Cold Path and Replay

Event Hubs retention limits (7 days Standard, 90 days Premium/Dedicated) constrain historical replay and backfill capabilities. Capture solves this.

How Capture works

Automatic export to Azure Storage:

• Blob Storage or Azure Data Lake Storage Gen2
• Avro or Parquet format
• Size-based (MB) or time-based (minutes) triggers
• Preserves all events beyond retention window

Architecture pattern:

Hot path: Event Hubs → ClickHouse (real-time serving)
Cold path: Event Hubs Capture → Storage → Batch loading (backfills, corrections)

Replay and backfill from Capture

When consumer falls behind retention window or requires historical reprocessing:

1. Query Capture files in Azure Storage (Avro/Parquet)
2. Load to ClickHouse via batch insert or external table
3. Deduplicate with existing data if needed

-- Read Parquet from Azure Blob
INSERT INTO events_raw
SELECT event_time,
event_name,
user_id,
properties_json
FROM azureBlobStorage(
'https://<account>.blob.core.windows.net/<container>/captured/*.parquet',
'<account_key>',
'Parquet'
);

Dual-path architecture maintains real-time serving while preserving complete history for compliance and backfills.

How Tinybird Simplifies Event Hubs Integration

Everything discussed—Kafka engine configuration, materialized views, error handling, deduplication patterns, throttling management, capture coordination—requires engineering effort to implement and maintain.

Tinybird provides managed ClickHouse with built-in streaming capabilities eliminating Event Hubs integration complexity.

Compared with traditional data warehouses, this approach reduces operational overhead while keeping sub-second serving for APIs and dashboards.

Streaming ingestion without Kafka engine configuration

While Event Hubs excels at durable event ingestion, connecting it to ClickHouse requires careful configuration, tuning, and monitoring.

Tinybird's approach:

• Native Kafka integration consuming from Event Hubs without manual engine configuration
• Automatic scaling handling throughput spikes
• Built-in error handling with dead letter management
• Monitoring included tracking lag, throughput, and errors

No Kafka table engine tuning. Configure Event Hubs connection, ingest data, query immediately.

SQL transformations instead of materialized views

Event Hubs provides raw events. Transformations happen somewhere—either in ClickHouse materialized views or stream processors.

Tinybird workflow:

1. Ingest raw events from Event Hubs continuously
2. SQL pipes define transformations and aggregations
3. Instant APIs serve results with sub-100ms latency

No separate stream processing frameworks. Write SQL, get production data pipelines for real-time data processing.

When Tinybird makes sense versus Event Hubs integration

Choose Tinybird when:

• real-time analytics required (seconds, not batch windows)
• Operational simplicity preferred over infrastructure control
• SQL transformations sufficient (vast majority of cases)
• Sub-100ms serving for dashboards and APIs critical
• Managed platform acceptable for streaming analytics

Keep Event Hubs integration patterns when:

• Existing Event Hubs investment and expertise justify continuation
• Azure ecosystem integration with Stream Analytics, Functions, Logic Apps essential
• Self-managed ClickHouse required for specific compliance or architectural reasons
• Kafka Connect standards organizationally mandated

Many teams use both: Event Hubs as durable event backbone, Tinybird consuming for real-time analytics serving. Separate event infrastructure from serving platforms.

The Path Forward: Architecture Matching Requirements

ClickHouse integration with Azure Event Hubs solves specific problems:

Kafka table engine provides direct consumption with minimal infrastructure
Kafka Connect centralizes streaming integration management
ClickPipes eliminates operational overhead for ClickHouse Cloud users

But these patterns require engineering investment:

• Engine configuration and performance tuning
• Materialized view design and error handling
• Offset management and deduplication strategies
• Throttling detection and capacity planning
• Capture coordination for historical replay
• Monitoring and operational procedures

Tinybird provides purpose-built alternative for teams where:

• Real-time matters more than batch flexibility
• SQL transformations sufficient versus complex stream processing
• Operational simplicity preferred over infrastructure control
• Analytics serving (APIs, dashboards) is primary requirement

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

For analytics that stay fast as event volumes scale—choose architectures built for the job.