# ClickHouse Analytics Stack

> ClickHouse OLAP database with Grafana, Vector for log ingestion, and Tabix UI

- Category: Database Stacks
- Difficulty: intermediate
- Components: clickhouse, grafana, vector, tabix
- Tags: clickhouse, analytics, olap, grafana, logs, time-series
- Source: https://docker.recipes/databases/clickhouse-analytics-stack

## Overview

ClickHouse is a lightning-fast column-oriented database designed specifically for online analytical processing (OLAP), developed by Yandex to handle billions of rows per second with real-time query performance. Its columnar storage architecture and advanced compression algorithms make it exceptionally efficient for analytical workloads, time-series data, and log processing at massive scale. Unlike traditional row-based databases, ClickHouse excels at aggregating large datasets and executing complex analytical queries with sub-second response times. This analytics stack combines ClickHouse's raw analytical power with Vector's high-performance log ingestion, Grafana's rich visualization capabilities, and Tabix's intuitive SQL interface to create a complete analytics pipeline. Vector acts as the data collection layer, efficiently ingesting logs and metrics from various sources and streaming them into ClickHouse, while Grafana transforms the stored data into actionable dashboards and alerts. The integration enables real-time analytics workflows where data flows from collection through storage to visualization without leaving the stack. This combination is particularly valuable for organizations processing large volumes of time-series data, application logs, or business metrics who need both the performance of ClickHouse and the accessibility of visual dashboards. The stack provides data teams with SQL-native analytics capabilities while offering stakeholders familiar Grafana interfaces for monitoring and business intelligence.

## Features

- Column-oriented storage with advanced compression reducing storage costs by 10-100x compared to traditional databases
- Vector's rust-based log processing with support for 50+ data sources and transformation capabilities
- Tabix web-based SQL client with query builder, result visualization, and ClickHouse-specific optimizations
- Grafana ClickHouse datasource plugin enabling native integration with dashboard templating and alerting
- Real-time data ingestion pipeline supporting structured logs, metrics, and time-series data
- Materialized views in ClickHouse for pre-aggregated analytics and faster dashboard queries
- Vector's adaptive request concurrency automatically optimizing ingestion throughput to ClickHouse
- MergeTree engine family providing automatic data partitioning and primary key optimization

## Use cases

- Real-time application log analytics for debugging and performance monitoring across microservices
- Business intelligence dashboards analyzing user behavior, conversion funnels, and revenue metrics
- Infrastructure monitoring storing and visualizing metrics from servers, containers, and cloud services
- E-commerce analytics tracking product views, cart abandonment, and customer journey analysis
- IoT data processing for sensor readings, device telemetry, and predictive maintenance analytics
- Financial data analysis including trading analytics, risk assessment, and regulatory reporting
- Gaming analytics processing player events, retention analysis, and monetization tracking

## Prerequisites

- Minimum 8GB RAM recommended for ClickHouse with analytical workloads (2GB absolute minimum)
- Docker and Docker Compose with available ports 3000, 8123, 8124, and 9000
- Vector configuration file (vector.toml) defining data sources and ClickHouse sink settings
- Basic SQL knowledge for creating ClickHouse tables and understanding columnar database concepts
- Understanding of time-series data patterns and analytical query optimization techniques

## docker-compose.yml

```yaml
services:
  clickhouse:
    image: clickhouse/clickhouse-server:latest
    container_name: clickhouse
    restart: unless-stopped
    ports:
      - "${CH_HTTP_PORT:-8123}:8123"
      - "${CH_NATIVE_PORT:-9000}:9000"
    environment:
      - CLICKHOUSE_USER=${CH_USER:-default}
      - CLICKHOUSE_PASSWORD=${CH_PASSWORD}
      - CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1
    volumes:
      - clickhouse_data:/var/lib/clickhouse
      - clickhouse_logs:/var/log/clickhouse-server
    ulimits:
      nofile:
        soft: 262144
        hard: 262144

  tabix:
    image: spoonest/clickhouse-tabix-web-client:latest
    container_name: tabix
    restart: unless-stopped
    ports:
      - "${TABIX_PORT:-8124}:80"
    depends_on:
      - clickhouse

  vector:
    image: timberio/vector:latest-alpine
    container_name: vector
    restart: unless-stopped
    volumes:
      - ./vector/vector.toml:/etc/vector/vector.toml:ro
      - /var/log:/var/log:ro
    depends_on:
      - clickhouse

  grafana:
    image: grafana/grafana:latest
    container_name: clickhouse-grafana
    restart: unless-stopped
    ports:
      - "${GRAFANA_PORT:-3000}:3000"
    environment:
      - GF_SECURITY_ADMIN_PASSWORD=${GRAFANA_PASSWORD:-admin}
      - GF_INSTALL_PLUGINS=grafana-clickhouse-datasource
    volumes:
      - grafana_data:/var/lib/grafana
    depends_on:
      - clickhouse

volumes:
  clickhouse_data:
  clickhouse_logs:
  grafana_data:
```

## .env

```bash
# ClickHouse Analytics Stack
CH_HTTP_PORT=8123
CH_NATIVE_PORT=9000
TABIX_PORT=8124
GRAFANA_PORT=3000

# ClickHouse
CH_USER=default
CH_PASSWORD=clickhouse_password

# Grafana
GRAFANA_PASSWORD=admin
```

## Usage notes

- ClickHouse HTTP API at http://localhost:8123
- Tabix SQL client at http://localhost:8124
- Grafana at http://localhost:3000 (admin/admin)
- Create vector.toml config for log ingestion
- Add ClickHouse datasource in Grafana
- ClickHouse excels at analytics and log analysis

## Troubleshooting

- ClickHouse 'Memory limit exceeded' errors: Increase max_memory_usage setting or add more RAM to handle large analytical queries
- Vector failing to connect to ClickHouse: Verify ClickHouse is accepting connections on port 9000 and vector.toml has correct endpoint configuration
- Grafana ClickHouse datasource connection refused: Ensure ClickHouse HTTP interface is enabled on port 8123 and user has proper permissions
- Tabix showing 'CORS policy' errors: Configure ClickHouse http_server_default_response header to allow cross-origin requests
- Slow query performance in ClickHouse: Check table ENGINE type, ensure proper partitioning key, and verify queries use primary key columns
- Vector consuming high CPU during log ingestion: Reduce batch size in vector.toml and implement rate limiting for high-volume log sources

