Set up ClickHouse and Kafka real-time data pipeline with streaming analytics

Advanced 45 min Apr 03, 2026 1,191 views
Ubuntu 24.04 Debian 12 AlmaLinux 9 Rocky Linux 9

Build a production-ready real-time data pipeline using ClickHouse for high-performance analytics and Apache Kafka for streaming data ingestion. Configure clustering, replication, and automated data processing workflows.

Prerequisites

  • Root or sudo access
  • Minimum 8GB RAM
  • Java 11 or higher
  • Network access to download packages
  • At least 50GB free disk space

What this solves

Real-time data analytics requires a robust pipeline that can ingest, process, and analyze streaming data at scale. This tutorial sets up ClickHouse as your analytical database with Apache Kafka for stream processing, creating a production-grade pipeline capable of handling millions of events per second with sub-second query latency.

Step-by-step installation

Update system packages

Start by updating your package manager and installing essential dependencies for the data pipeline setup.

sudo apt update && sudo apt upgrade -y
sudo apt install -y curl wget gnupg software-properties-common apt-transport-https ca-certificates
sudo dnf update -y
sudo dnf install -y curl wget gnupg2 ca-certificates

Install Java for Kafka

Apache Kafka requires Java 8 or higher. Install OpenJDK 11 for optimal compatibility and performance.

sudo apt install -y openjdk-11-jdk
java -version
sudo dnf install -y java-11-openjdk java-11-openjdk-devel
java -version

Install ClickHouse

Add the ClickHouse repository and install the server with client tools for high-performance analytical processing.

curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg
echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg] https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt update
sudo apt install -y clickhouse-server clickhouse-client
sudo yum install -y yum-utils
sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
sudo yum install -y clickhouse-server clickhouse-client

Configure ClickHouse for production

Set up ClickHouse with optimized settings for real-time analytics workloads and enable clustering support.

Configure ClickHouse users and security

Set up user authentication and access controls for secure database operations.

Install Apache ZooKeeper

Install ZooKeeper for Kafka cluster coordination and ClickHouse replication management.

cd /opt
sudo wget https://archive.apache.org/dist/zookeeper/zookeeper-3.8.3/apache-zookeeper-3.8.3-bin.tar.gz
sudo tar -xzf apache-zookeeper-3.8.3-bin.tar.gz
sudo mv apache-zookeeper-3.8.3-bin zookeeper
sudo chown -R root:root /opt/zookeeper

Configure ZooKeeper

Set up ZooKeeper configuration for stable cluster operations and data consistency.

tickTime=2000
dataDir=/var/lib/zookeeper
clientPort=2181
initLimit=10
syncLimit=5
server.1=localhost:2888:3888
maxClientCnxns=60
autopurge.snapRetainCount=3
autopurge.purgeInterval=1
sudo mkdir -p /var/lib/zookeeper
sudo chown -R root:root /var/lib/zookeeper
echo "1" | sudo tee /var/lib/zookeeper/myid

Create ZooKeeper systemd service

Configure ZooKeeper as a system service for automatic startup and process management.

[Unit]
Description=Apache ZooKeeper server
Documentation=http://zookeeper.apache.org
Requires=network.target remote-fs.target
After=network.target remote-fs.target

[Service]
Type=forking
User=root
Group=root
ExecStart=/opt/zookeeper/bin/zkServer.sh start
ExecStop=/opt/zookeeper/bin/zkServer.sh stop
ExecReload=/opt/zookeeper/bin/zkServer.sh restart
WorkingDirectory=/opt/zookeeper

[Install]
WantedBy=multi-user.target

Install Apache Kafka

Download and install Kafka for distributed streaming platform capabilities.

cd /opt
sudo wget https://archive.apache.org/dist/kafka/2.13-3.6.1/kafka_2.13-3.6.1.tgz
sudo tar -xzf kafka_2.13-3.6.1.tgz
sudo mv kafka_2.13-3.6.1 kafka
sudo chown -R root:root /opt/kafka

Configure Kafka server

Set up Kafka broker configuration optimized for high-throughput data streaming and ClickHouse integration.

broker.id=0
listeners=PLAINTEXT://localhost:9092
log.dirs=/var/lib/kafka-logs
num.network.threads=8
num.io.threads=16
socket.send.buffer.bytes=102400
socket.receive.buffer.bytes=102400
socket.request.max.bytes=104857600

num.partitions=3
num.recovery.threads.per.data.dir=1
offsets.topic.replication.factor=1
transaction.state.log.replication.factor=1
transaction.state.log.min.isr=1

log.retention.hours=168
log.retention.bytes=1073741824
log.segment.bytes=1073741824
log.retention.check.interval.ms=300000

zookeeper.connect=localhost:2181
zookeeper.connection.timeout.ms=18000

group.initial.rebalance.delay.ms=0
auto.create.topics.enable=true
delete.topic.enable=true

compression.type=lz4
max.request.size=10485760
message.max.bytes=10485760
replica.fetch.max.bytes=10485760
sudo mkdir -p /var/lib/kafka-logs
sudo chown -R root:root /var/lib/kafka-logs

Create Kafka systemd service

Configure Kafka as a system service with proper dependency management.

[Unit]
Description=Apache Kafka server
Documentation=http://kafka.apache.org/documentation.html
Requires=zookeeper.service
After=zookeeper.service

[Service]
Type=simple
User=root
Group=root
Environment=JAVA_HOME=/usr/lib/jvm/java-11-openjdk-amd64
ExecStart=/opt/kafka/bin/kafka-server-start.sh /opt/kafka/config/server.properties
ExecStop=/opt/kafka/bin/kafka-server-stop.sh
Restart=on-failure

[Install]
WantedBy=multi-user.target

Install Kafka Connect

Set up Kafka Connect for seamless data integration between Kafka and ClickHouse.

cd /opt
sudo wget https://github.com/ClickHouse/clickhouse-kafka-connect/releases/download/v1.0.12/clickhouse-kafka-connect-v1.0.12.zip
sudo unzip clickhouse-kafka-connect-v1.0.12.zip -d kafka-connect-clickhouse
sudo mv kafka-connect-clickhouse /opt/kafka/
sudo chown -R root:root /opt/kafka/kafka-connect-clickhouse

Configure Kafka Connect for ClickHouse

Set up Kafka Connect worker configuration for distributed mode operation.

bootstrap.servers=localhost:9092
group.id=clickhouse-connect-cluster

key.converter=org.apache.kafka.connect.storage.StringConverter
value.converter=org.apache.kafka.connect.storage.StringConverter
key.converter.schemas.enable=false
value.converter.schemas.enable=false

offset.storage.topic=connect-offsets
offset.storage.replication.factor=1
offset.storage.partitions=25

config.storage.topic=connect-configs
config.storage.replication.factor=1

status.storage.topic=connect-status
status.storage.replication.factor=1
status.storage.partitions=5

offset.flush.interval.ms=10000

plugin.path=/opt/kafka/kafka-connect-clickhouse

rest.host.name=localhost
rest.port=8083

Start all services

Enable and start ZooKeeper, Kafka, and ClickHouse services in the correct order.

sudo systemctl daemon-reload
sudo systemctl enable --now zookeeper
sudo systemctl enable --now clickhouse-server
sudo systemctl enable --now kafka

# Wait for services to start
sleep 10

# Verify services are running
sudo systemctl status zookeeper
sudo systemctl status clickhouse-server
sudo systemctl status kafka

Create ClickHouse database and tables

Set up the analytics database structure optimized for real-time data ingestion from Kafka.

clickhouse-client --query="CREATE DATABASE IF NOT EXISTS analytics"

clickhouse-client --database=analytics --query="
CREATE TABLE IF NOT EXISTS events_queue (
    timestamp DateTime64(3),
    user_id String,
    event_type String,
    properties String,
    ip_address IPv4,
    user_agent String
) ENGINE = Kafka()
SETTINGS 
    kafka_broker_list = 'localhost:9092',
    kafka_topic_list = 'user_events',
    kafka_group_name = 'clickhouse_consumer',
    kafka_format = 'JSONEachRow',
    kafka_num_consumers = 3;"

clickhouse-client --database=analytics --query="
CREATE TABLE IF NOT EXISTS events (
    timestamp DateTime64(3),
    user_id String,
    event_type String,
    properties String,
    ip_address IPv4,
    user_agent String,
    date Date MATERIALIZED toDate(timestamp)
) ENGINE = MergeTree()
PARTITION BY date
ORDER BY (event_type, user_id, timestamp)
TTL date + INTERVAL 90 DAY;"

clickhouse-client --database=analytics --query="
CREATE MATERIALIZED VIEW IF NOT EXISTS events_mv TO events AS
SELECT 
    timestamp,
    user_id,
    event_type,
    properties,
    ip_address,
    user_agent
FROM events_queue;"

Create Kafka topic for events

Set up a Kafka topic with optimal partitioning for high-throughput event streaming.

/opt/kafka/bin/kafka-topics.sh --create \
    --bootstrap-server localhost:9092 \
    --replication-factor 1 \
    --partitions 6 \
    --topic user_events \
    --config retention.ms=604800000 \
    --config compression.type=lz4

Create real-time analytics views

Set up materialized views for common analytics queries with automatic aggregation.

clickhouse-client --database=analytics --query="
CREATE TABLE IF NOT EXISTS hourly_stats (
    hour DateTime,
    event_type String,
    event_count UInt64,
    unique_users UInt64
) ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, event_type);"

clickhouse-client --database=analytics --query="
CREATE MATERIALIZED VIEW IF NOT EXISTS hourly_stats_mv TO hourly_stats AS
SELECT 
    toStartOfHour(timestamp) as hour,
    event_type,
    count() as event_count,
    uniq(user_id) as unique_users
FROM events
GROUP BY hour, event_type;"

clickhouse-client --database=analytics --query="
CREATE TABLE IF NOT EXISTS top_events_by_hour (
    hour DateTime,
    event_type String,
    event_count UInt64
) ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, event_count, event_type);"

clickhouse-client --database=analytics --query="
CREATE MATERIALIZED VIEW IF NOT EXISTS top_events_mv TO top_events_by_hour AS
SELECT 
    toStartOfHour(timestamp) as hour,
    event_type,
    count() as event_count
FROM events
GROUP BY hour, event_type
HAVING event_count > 10;"

Start Kafka Connect

Launch Kafka Connect in distributed mode to handle data pipeline operations.

/opt/kafka/bin/connect-distributed.sh /opt/kafka/config/connect-distributed.properties > /var/log/kafka-connect.log 2>&1 &

# Create systemd service for Kafka Connect
sudo tee /etc/systemd/system/kafka-connect.service > /dev/null << 'EOF'
[Unit]
Description=Kafka Connect
Requires=kafka.service
After=kafka.service

[Service]
Type=simple
User=root
Group=root
Environment=JAVA_HOME=/usr/lib/jvm/java-11-openjdk-amd64
ExecStart=/opt/kafka/bin/connect-distributed.sh /opt/kafka/config/connect-distributed.properties
Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl daemon-reload
sudo systemctl enable --now kafka-connect

Verify your setup

Test the complete data pipeline by producing sample events and verifying data flow through Kafka to ClickHouse.

# Check all services are running
sudo systemctl status zookeeper clickhouse-server kafka kafka-connect

# Test Kafka topic creation
/opt/kafka/bin/kafka-topics.sh --list --bootstrap-server localhost:9092

# Verify ClickHouse is accepting connections
clickhouse-client --query="SELECT version()"

# Test data pipeline with sample events
echo '{"timestamp":"2024-01-15 10:30:00.000","user_id":"user123","event_type":"page_view","properties":"{\"page\":\"/home\"}","ip_address":"203.0.113.10","user_agent":"Mozilla/5.0"}' | /opt/kafka/bin/kafka-console-producer.sh --bootstrap-server localhost:9092 --topic user_events

echo '{"timestamp":"2024-01-15 10:31:00.000","user_id":"user456","event_type":"click","properties":"{\"button\":\"signup\"}","ip_address":"203.0.113.20","user_agent":"Mozilla/5.0"}' | /opt/kafka/bin/kafka-console-producer.sh --bootstrap-server localhost:9092 --topic user_events

# Wait a few seconds for processing
sleep 5

# Verify data in ClickHouse
clickhouse-client --database=analytics --query="SELECT count() FROM events"
clickhouse-client --database=analytics --query="SELECT * FROM events LIMIT 5"
clickhouse-client --database=analytics --query="SELECT * FROM hourly_stats LIMIT 5"

# Check Kafka Connect status
curl -s localhost:8083/connectors

# Verify Kafka consumer group
/opt/kafka/bin/kafka-consumer-groups.sh --bootstrap-server localhost:9092 --describe --group clickhouse_consumer

Performance optimization and monitoring

Configure ClickHouse performance monitoring

Set up system tables and queries for monitoring pipeline performance and identifying bottlenecks.

clickhouse-client --database=analytics --query="
CREATE TABLE IF NOT EXISTS pipeline_metrics (
    timestamp DateTime,
    metric_name String,
    metric_value Float64,
    tags String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (timestamp, metric_name);"

# Create monitoring queries
clickhouse-client --database=analytics --query="
SELECT 
    'events_per_second' as metric,
    count() / 3600 as value,
    toStartOfHour(now()) as hour
FROM events 
WHERE timestamp >= now() - INTERVAL 1 HOUR;"

clickhouse-client --database=analytics --query="
SELECT 
    'partition_size_gb' as metric,
    sum(bytes_on_disk) / (1024*1024*1024) as size_gb,
    table
FROM system.parts 
WHERE database = 'analytics' AND active = 1
GROUP BY table;"

Set up automated performance optimization

Configure automatic table optimization and partition management for sustained performance.

Automated install script

Run this to automate the entire setup

#clickhouse #kafka #streaming #analytics #real-time

Related tutorials

databases Advanced

Configure PostgreSQL 17 SSL encryption and certificate-based authentication

Set up a private CA, issue server and client certificates, enforce TLS 1.2+ with strong ciphers, and configure pg_hba.conf for mutual TLS client certificate authentication in PostgreSQL 17.

45 min 4 distros 37 views
databases Intermediate

Configure PostgreSQL 17 connection pooling with PgBouncer for high availability

Deploy PgBouncer in front of PostgreSQL 17 with session, transaction and statement pooling modes, SCRAM-SHA-256 authentication, keepalived-based HA, and Prometheus monitoring for production workloads.

45 min 4 distros 144 views
databases Advanced

Setup ScyllaDB backup validation and automated restore testing

Configure automated ScyllaDB backups with nodetool snapshots, implement validation scripts to verify backup integrity, and set up systemd timers for automated restore testing with Prometheus monitoring.

45 min 4 distros 413 views
Browse all tutorials in our complete directory →

Don't want to manage this yourself?

We handle infrastructure for businesses that depend on uptime. Fully managed, with one fixed contact who knows your setup.

You get one fixed contact who knows your setup

At their desk in Rotterdam 12:20 · reachable in a message, no ticket form

+31 10 477 5362 WhatsApp