# ClickHouse + Tabix

> ClickHouse high-performance columnar OLAP database with Tabix web UI for analytics and data exploration.

- Category: Database Stacks
- Difficulty: intermediate
- Components: clickhouse, tabix
- Tags: clickhouse, olap, analytics, columnar, big-data
- Source: https://docker.recipes/databases/clickhouse

## Overview

ClickHouse is a high-performance columnar database management system designed for online analytical processing (OLAP) workloads. Developed by Yandex in 2009 and open-sourced in 2016, ClickHouse excels at processing billions of rows in milliseconds through its innovative column-oriented storage architecture and advanced compression algorithms. Unlike traditional row-based databases, ClickHouse stores data in columns, making it exceptionally fast for analytical queries that typically scan large datasets but only need specific columns. Tabix provides a modern web-based interface for ClickHouse, offering visual query building, data exploration tools, and interactive dashboards. This web client transforms ClickHouse's command-line interface into an accessible graphical environment where users can write SQL queries, visualize results, and manage database schemas without technical expertise. Tabix includes features like query history, result formatting, and connection management that streamline database interactions. This combination delivers enterprise-grade analytical capabilities for organizations processing large volumes of time-series data, logs, or business metrics. Data engineers and analysts benefit from ClickHouse's sub-second query performance on massive datasets while business users can explore data through Tabix's intuitive interface, making advanced analytics accessible across different skill levels.

## Features

- Column-oriented storage with advanced compression reducing storage by 10x compared to traditional databases
- Real-time query performance processing billions of rows in milliseconds
- Native SQL support with extensions for analytical functions and window operations
- Tabix web interface with visual query builder and interactive result visualization
- MergeTree engine family optimized for time-series and analytical workloads
- Distributed query execution across multiple nodes for horizontal scaling
- Approximate query processing for ultra-fast statistical analysis
- Built-in materialized views for pre-aggregated analytics and real-time reporting

## Use cases

- Real-time business intelligence dashboards analyzing customer behavior and sales metrics
- Log analytics for monitoring application performance and system health at scale
- Time-series analysis for IoT sensor data and infrastructure monitoring
- Financial data analysis processing trading records and market data
- E-commerce analytics tracking user journeys and conversion funnels
- Media analytics measuring content engagement and advertising effectiveness
- Telecommunications data analysis for call detail records and network optimization

## Prerequisites

- Minimum 8GB RAM recommended for production workloads with large datasets
- Docker Engine 20.10+ and Docker Compose V2 for container orchestration
- Available ports 8123 (HTTP API), 9000 (native protocol), and 8080 (Tabix UI)
- Basic SQL knowledge for writing analytical queries and data modeling
- Understanding of columnar database concepts and OLAP workload patterns
- SSD storage recommended for optimal query performance on large datasets

## docker-compose.yml

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

  tabix:
    image: spoonest/clickhouse-tabix-web-client:stable
    container_name: tabix
    restart: unless-stopped
    environment:
      CH_HOST: clickhouse
      CH_PORT: 8123
    ports:
      - "8080:80"
    depends_on:
      - clickhouse
    networks:
      - clickhouse-network

volumes:
  clickhouse_data:
  clickhouse_logs:

networks:
  clickhouse-network:
    driver: bridge
```

## .env

```bash
# ClickHouse Configuration
CLICKHOUSE_USER=default
CLICKHOUSE_PASSWORD=changeme
```

## Usage notes

- Docs: https://clickhouse.com/docs
- Tabix UI at http://localhost:8080 | HTTP API at http://localhost:8123
- Native protocol on port 9000 - use for high-performance clients
- Test query: curl 'http://localhost:8123/?query=SELECT%201'
- Use MergeTree engine family for time-series and analytics workloads
- Optimized for OLAP - billions of rows in sub-second queries

## Troubleshooting

- ClickHouse container exits with 'Memory limit exceeded': Increase container memory limits and set max_memory_usage in ClickHouse configuration
- Tabix shows 'Connection refused' to ClickHouse: Verify CH_HOST environment variable matches ClickHouse service name and both containers are on same network
- Queries timeout on large datasets: Tune max_execution_time setting and consider using LIMIT clauses for initial data exploration
- High memory usage during queries: Configure max_memory_usage_for_user and optimize queries to use appropriate WHERE clauses
- Tabix interface loads but queries fail: Check ClickHouse user permissions and ensure CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT is enabled
- Poor query performance: Verify data is using MergeTree engine family and consider adding appropriate ORDER BY keys for your query patterns

