ClickHouse Analytics กับ Home Lab Setup —

☁️ Cloud โดย อ.บอม กิตติทัศน์ เจริญพนาสิทธิ์ · เผยแพร่ 2026-05-28

ClickHouse Analytics Database

ClickHouse เป็น Columnar Database สำหรับ OLAP ประมวลผล Queries เร็วมาก รองรับข้อมูลหลายพันล้าน Rows ใช้ Column-oriented Storage บีบอัดข้อมูลได้ดี เหมาะกับ Analytics

Home Lab ให้ทดลอง ClickHouse ที่บ้าน ไม่ต้องจ่ายค่า Cloud ใช้ PC เก่าหรือ Mini PC ติดตั้งผ่าน Docker เรียนรู้ Data Engineering จริง

ClickHouse Installation

# === ClickHouse Installation สำหรับ Home Lab ===

# 1. Docker Installation (แนะนำ)
docker run -d \
  --name clickhouse \
  --ulimit nofile=262144:262144 \
  -p 8123:8123 \
  -p 9000:9000 \
  -v clickhouse_data:/var/lib/clickhouse \
  -v clickhouse_logs:/var/log/clickhouse-server \
  clickhouse/clickhouse-server:latest

# 2. Docker Compose (Production-ready)
# docker-compose.yml
# version: '3.8'
# services:
#   clickhouse:
#     image: clickhouse/clickhouse-server:latest
#     ports:
#       - "8123:8123"   # HTTP
#       - "9000:9000"   # Native
#     volumes:
#       - ./data:/var/lib/clickhouse
#       - ./logs:/var/log/clickhouse-server
#       - ./config:/etc/clickhouse-server/config.d
#       - ./users:/etc/clickhouse-server/users.d
#     environment:
#       CLICKHOUSE_DB: analytics
#       CLICKHOUSE_USER: admin
#       CLICKHOUSE_PASSWORD: secure_password
#     ulimits:
#       nofile:
#         soft: 262144
#         hard: 262144
#     deploy:
#       resources:
#         limits:
#           memory: 8G
#
#   grafana:
#     image: grafana/grafana:latest
#     ports:
#       - "3000:3000"
#     environment:
#       GF_INSTALL_PLUGINS: grafana-clickhouse-datasource
#     volumes:
#       - grafana_data:/var/lib/grafana

# 3. Native Installation (Ubuntu)
sudo apt-get install -y apt-transport-https ca-certificates
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754
echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update
sudo apt-get install -y clickhouse-server clickhouse-client

sudo systemctl start clickhouse-server
sudo systemctl enable clickhouse-server

# 4. ตรวจสอบ
clickhouse-client --query "SELECT version()"
curl http://localhost:8123/?query=SELECT+1

# 5. Performance Tuning (config.d/performance.xml)
# 
#   8000000000
#   8
#   100
#   5368709120
# 

echo "ClickHouse installed"
echo "  HTTP: http://localhost:8123"
echo "  Native: localhost:9000"
echo "  Client: clickhouse-client"

Table Design และ Data Loading

# === ClickHouse Table Design สำหรับ Analytics ===

# clickhouse-client
# หรือ curl -X POST http://localhost:8123 --data-binary @query.sql

# 1. Web Analytics Table
# CREATE TABLE web_events (
#     event_date Date,
#     event_time DateTime,
#     user_id UInt64,
#     session_id String,
#     event_type LowCardinality(String),
#     page_url String,
#     referrer String,
#     device LowCardinality(String),
#     browser LowCardinality(String),
#     country LowCardinality(String),
#     city String,
#     duration_ms UInt32,
#     is_bounce UInt8
# ) ENGINE = MergeTree()
# PARTITION BY toYYYYMM(event_date)
# ORDER BY (event_date, user_id, event_time)
# TTL event_date + INTERVAL 1 YEAR
# SETTINGS index_granularity = 8192;

# 2. Server Metrics Table
# CREATE TABLE server_metrics (
#     timestamp DateTime,
#     hostname LowCardinality(String),
#     metric_name LowCardinality(String),
#     value Float64,
#     tags Map(String, String)
# ) ENGINE = MergeTree()
# PARTITION BY toYYYYMMDD(timestamp)
# ORDER BY (hostname, metric_name, timestamp)
# TTL timestamp + INTERVAL 90 DAY;

# 3. Materialized View สำหรับ Real-time Aggregation
# CREATE MATERIALIZED VIEW hourly_page_views
# ENGINE = SummingMergeTree()
# PARTITION BY toYYYYMM(hour)
# ORDER BY (hour, page_url, country)
# AS SELECT
#     toStartOfHour(event_time) AS hour,
#     page_url,
#     country,
#     count() AS views,
#     uniq(user_id) AS unique_users,
#     avg(duration_ms) AS avg_duration
# FROM web_events
# GROUP BY hour, page_url, country;

# 4. Python Data Loading
# pip install clickhouse-connect pandas

import clickhouse_connect
import pandas as pd
import numpy as np
from datetime import datetime, timedelta

def setup_clickhouse():
    """Setup ClickHouse Connection และ Tables"""
    client = clickhouse_connect.get_client(
        host='localhost', port=8123,
        username='default', password='',
    )

    # สร้าง Database
    client.command("CREATE DATABASE IF NOT EXISTS analytics")

    # สร้าง Table
    client.command("""
        CREATE TABLE IF NOT EXISTS analytics.web_events (
            event_date Date,
            event_time DateTime,
            user_id UInt64,
            session_id String,
            event_type LowCardinality(String),
            page_url String,
            country LowCardinality(String),
            duration_ms UInt32
        ) ENGINE = MergeTree()
        PARTITION BY toYYYYMM(event_date)
        ORDER BY (event_date, user_id, event_time)
    """)

    return client

def generate_sample_data(n=100000):
    """สร้างข้อมูลตัวอย่าง"""
    np.random.seed(42)
    pages = ["/home", "/products", "/cart", "/checkout", "/blog", "/about"]
    countries = ["TH", "US", "JP", "SG", "MY", "ID", "VN", "PH"]
    events = ["page_view", "click", "scroll", "purchase"]

    dates = [datetime.now() - timedelta(days=np.random.randint(0, 90)) for _ in range(n)]

    data = {
        "event_date": [d.date() for d in dates],
        "event_time": dates,
        "user_id": np.random.randint(1, 10000, n),
        "session_id": [f"s{i}" for i in np.random.randint(1, 50000, n)],
        "event_type": np.random.choice(events, n),
        "page_url": np.random.choice(pages, n),
        "country": np.random.choice(countries, n),
        "duration_ms": np.random.randint(100, 30000, n),
    }

    return pd.DataFrame(data)

# client = setup_clickhouse()
# df = generate_sample_data(1000000)
# client.insert_df("analytics.web_events", df)
# print(f"Inserted {len(df)} rows")

print("ClickHouse Tables:")
print("  web_events: Web analytics events")
print("  server_metrics: Server monitoring")
print("  hourly_page_views: Materialized View")

Analytics Queries

# === ClickHouse Analytics Queries ===

queries = {
    "Daily Active Users": """
        SELECT
            event_date,
            uniq(user_id) AS dau,
            count() AS total_events,
            avg(duration_ms) AS avg_duration
        FROM analytics.web_events
        WHERE event_date >= today() - 30
        GROUP BY event_date
        ORDER BY event_date
    """,

    "Top Pages by Views": """
        SELECT
            page_url,
            count() AS views,
            uniq(user_id) AS unique_users,
            avg(duration_ms) AS avg_duration,
            countIf(is_bounce = 1) * 100.0 / count() AS bounce_rate
        FROM analytics.web_events
        WHERE event_date >= today() - 7
        GROUP BY page_url
        ORDER BY views DESC
        LIMIT 10
    """,

    "User Funnel": """
        SELECT
            countIf(event_type = 'page_view') AS views,
            countIf(event_type = 'click') AS clicks,
            countIf(event_type = 'scroll') AS scrolls,
            countIf(event_type = 'purchase') AS purchases,
            round(countIf(event_type = 'purchase') * 100.0 /
                  countIf(event_type = 'page_view'), 2) AS conversion_rate
        FROM analytics.web_events
        WHERE event_date >= today() - 30
    """,

    "Country Distribution": """
        SELECT
            country,
            uniq(user_id) AS users,
            count() AS events,
            round(avg(duration_ms) / 1000, 1) AS avg_seconds
        FROM analytics.web_events
        WHERE event_date >= today() - 30
        GROUP BY country
        ORDER BY users DESC
    """,

    "Hourly Traffic Pattern": """
        SELECT
            toHour(event_time) AS hour,
            count() AS events,
            uniq(user_id) AS users
        FROM analytics.web_events
        WHERE event_date = today()
        GROUP BY hour
        ORDER BY hour
    """,

    "Retention (Day 1, 7, 30)": """
        WITH first_seen AS (
            SELECT user_id, min(event_date) AS first_date
            FROM analytics.web_events
            GROUP BY user_id
        )
        SELECT
            first_date,
            count() AS cohort_size,
            countIf(dateDiff('day', first_date, event_date) = 1) AS day1,
            countIf(dateDiff('day', first_date, event_date) = 7) AS day7,
            countIf(dateDiff('day', first_date, event_date) = 30) AS day30
        FROM analytics.web_events
        JOIN first_seen USING (user_id)
        WHERE first_date >= today() - 60
        GROUP BY first_date
        ORDER BY first_date
    """,
}

print("ClickHouse Analytics Queries:")
for name, query in queries.items():
    lines = len(query.strip().split("\n"))
    print(f"  {name} ({lines} lines)")

# รัน Query
# client = setup_clickhouse()
# for name, query in queries.items():
#     result = client.query(query)
#     print(f"\n{name}:")
#     for row in result.result_rows[:5]:
#         print(f"  {row}")

Best Practices

Partition by Date: ใช้ PARTITION BY toYYYYMM(date) สำหรับ Time Series Data
ORDER BY: เลือก ORDER BY ตาม Query Pattern ที่ใช้บ่อยที่สุด
LowCardinality: ใช้ LowCardinality สำหรับ Columns ที่มีค่าซ้ำกันมาก
Materialized Views: ใช้ Materialized Views สำหรับ Pre-aggregate Data
TTL: ตั้ง TTL ลบข้อมูลเก่าอัตโนมัติ ประหยัด Storage
Grafana Dashboard: ใช้ Grafana + ClickHouse Plugin สำหรับ Visualization

ClickHouse คืออะไร

Open-source Columnar Database สำหรับ OLAP พัฒนาโดย Yandex ประมวลผล Queries เร็วมาก รองรับหลายพันล้าน Rows Column-oriented Storage บีบอัดดี Log Analytics Time Series BI

Home Lab คืออะไร

ตั้ง Server ที่บ้านเรียนรู้ Technology ใช้ PC เก่า Raspberry Pi Mini PC NAS ติดตั้ง Proxmox ESXi VMs Containers เรียน Networking Storage Databases ไม่ต้องจ่ายค่า Cloud

ClickHouse เร็วกว่า PostgreSQL อย่างไร

Columnar Storage อ่านเฉพาะ Columns ที่ต้องการ Vectorized Execution ประมวลผลทีละ Block บีบอัด 10-40x ลด I/O Analytical Queries เร็วกว่า 100-1000x ไม่เหมาะ OLTP

Hardware ขั้นต่ำสำหรับ ClickHouse Home Lab เท่าไร

CPU 4+ cores แนะนำ 8 RAM 8GB+ แนะนำ 16-32GB SSD 256GB+ HDD Cold Storage Docker ติดตั้งง่าย Mini PC Intel NUC Beelink หรือ Proxmox VM

สรุป

ClickHouse เป็น Columnar Database ที่เร็วมากสำหรับ Analytics ติดตั้งใน Home Lab ด้วย Docker Partition by Date ORDER BY ตาม Query Pattern LowCardinality ลด Storage Materialized Views Pre-aggregate TTL ลบข้อมูลเก่า Grafana Dashboard Visualization