ClickHouse 极速分析型数据库完全指南 2026：列式存储 + MergeTree 深入 + 物化视图 + 生产调优

1
services:
2
  clickhouse:
3
    image: clickhouse/clickhouse-server:24.8-alpine
4
    container_name: clickhouse-server
5
    restart: unless-stopped
6
    ports:
7
      - "8123:8123"   # HTTP 接口 (Web / REST / BI 报表)
8
      - "9000:9000"   # Native TCP 接口 (高性能客户端驱动)
9
    environment:
10
      - CLICKHOUSE_DB=analytics
11
      - CLICKHOUSE_USER=default
12
      - CLICKHOUSE_PASSWORD=clickhouse123   # 生产必须重设强密码！
13
      - CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1
14
    ulimits:
15
      nofile:
16
        soft: 262144
17
        hard: 262144
18
    volumes:
19
      - ch_data:/var/lib/clickhouse
20
      - ch_logs:/var/log/clickhouse-server
21
    networks:
22
      - ch-net
23

24
  clickhouse-client:
25
    image: clickhouse/clickhouse-client:24.8-alpine
26
    container_name: clickhouse-cli
27
    command: ["sleep", "infinity"]
28
    networks:
29
      - ch-net
30
    depends_on:
31
      - clickhouse
32

33
volumes:
34
  ch_data:
35
  ch_logs:
36

37
networks:
38
  ch-net:
39
    driver: bridge

1
import datetime
2
import random
3
import clickhouse_connect
4
import pandas as pd
5

6
# 1. 创建高性能 Native TCP/HTTP 连接池
7
client = clickhouse_connect.get_client(
8
    host='localhost',
9
    port=8123,
10
    username='default',
11
    password='clickhouse123',
12
    database='analytics'
13
)
14

15
def generate_mock_events(n=50000) -> pd.DataFrame:
16
    """生成 5 万条模拟埋点事件"""
17
    event_types = ['click', 'view', 'purchase', 'add_to_cart']
18
    devices = ['iOS', 'Android', 'Web', 'macOS']
19

20
    now = datetime.datetime.now()
21
    data = {
22
        'event_date': [now.date()] * n,
23
        'event_time': [now - datetime.timedelta(seconds=random.randint(0, 3600)) for _ in range(n)],
24
        'user_id': [random.randint(10000, 99999) for _ in range(n)],
25
        'event_type': [random.choice(event_types) for _ in range(n)],
26
        'page_url': [f"/item/{random.randint(1, 500)}" for _ in range(n)],
27
        'device': [random.choice(devices) for _ in range(n)],
28
        'duration_ms': [random.randint(50, 5000) for _ in range(n)],
29
        'amount': [round(random.uniform(5.0, 500.0), 2) if random.random() < 0.1 else 0.00 for _ in range(n)]
30
    }
31
    return pd.DataFrame(data)
32

33
def batch_insert():
34
    df = generate_mock_events(50000)
35

36
    # 2. 极速批量写入 DataFrame
37
    start_time = datetime.datetime.now()
38
    client.insert_df('user_events', df)
39
    cost = (datetime.datetime.now() - start_time).total_seconds()
40
    print(f"✅ Successfully batch inserted 50,000 records in {cost:.3f} seconds!")
41

42
def run_analytical_query():
43
    # 3. 极速多维聚合查询
44
    query = """
45
    SELECT
46
        event_type,
47
        device,
48
        count() AS total_events,
49
        uniqExact(user_id) AS unique_users,
50
        round(avg(duration_ms), 2) AS avg_duration,
51
        sum(amount) AS total_revenue
52
    FROM user_events
53
    WHERE event_date = today()
54
    GROUP BY event_type, device
55
    ORDER BY total_events DESC
56
    """
57
    result_df = client.query_df(query)
58
    print("\n--- Realtime Aggregation Result ---")
59
    print(result_df)
60

61
if __name__ == '__main__':
62
    batch_insert()
63
    run_analytical_query()