ClickHouse Configuration

CREATE TABLE umami.website_event_stats_hourly
(
    website_id UUID,
    session_id UUID,
    visit_id UUID,
    -- ... aggregated fields ...
    views SimpleAggregateFunction(sum, UInt64),
    min_time SimpleAggregateFunction(min, DateTime('UTC')),
    max_time SimpleAggregateFunction(max, DateTime('UTC')),
    created_at Datetime('UTC')
)
ENGINE = AggregatingMergeTree
    PARTITION BY toYYYYMM(created_at)
    ORDER BY (website_id, event_type, toStartOfHour(created_at), cityHash64(visit_id), visit_id)
    SAMPLE BY cityHash64(visit_id);

# HTTP protocol (default)
CLICKHOUSE_URL=http://localhost:8123/umami

# With authentication
CLICKHOUSE_URL=http://username:password@localhost:8123/umami

# HTTPS
CLICKHOUSE_URL=https://clickhouse.example.com:8443/umami

<clickhouse>
    <!-- Memory settings -->
    <max_memory_usage>10000000000</max_memory_usage>  <!-- 10GB -->
    <max_bytes_before_external_group_by>5000000000</max_bytes_before_external_group_by>
    
    <!-- Compression -->
    <compression>
        <case>
            <min_part_size>10485760</min_part_size>
            <min_part_size_ratio>0.01</min_part_size_ratio>
            <method>lz4</method>
        </case>
    </compression>
    
    <!-- Performance -->
    <max_concurrent_queries>100</max_concurrent_queries>
    <background_pool_size>16</background_pool_size>
    <background_schedule_pool_size>16</background_schedule_pool_size>
</clickhouse>

# Using clickhouse-client
clickhouse-client --query "CREATE DATABASE IF NOT EXISTS umami"
clickhouse-client --multiquery < /path/to/db/clickhouse/schema.sql

# Using Docker
docker exec -i clickhouse clickhouse-client --multiquery < schema.sql

-- List partitions
SELECT
    partition,
    name,
    rows,
    formatReadableSize(bytes_on_disk) AS size
FROM system.parts
WHERE table = 'website_event' AND active
ORDER BY partition DESC;

-- Drop old partition (careful!)
ALTER TABLE umami.website_event DROP PARTITION '202301';

SELECT
    table,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE database = 'umami'
GROUP BY table;

-- Analyze query performance
EXPLAIN SELECT * FROM umami.website_event WHERE website_id = 'xxx';

-- Check query log
SELECT
    query,
    query_duration_ms,
    read_rows,
    formatReadableSize(read_bytes) AS read_size
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 10;

-- Database size
SELECT
    database,
    formatReadableSize(sum(bytes)) AS size
FROM system.parts
WHERE active
GROUP BY database;

-- Table statistics
SELECT
    table,
    formatReadableSize(sum(bytes)) AS size,
    sum(rows) AS rows,
    max(modification_time) AS latest_modification
FROM system.parts
WHERE database = 'umami' AND active
GROUP BY table;

-- Active queries
SELECT
    query_id,
    user,
    elapsed,
    formatReadableSize(memory_usage) AS memory,
    query
FROM system.processes;

-- Merge statistics
SELECT
    table,
    count() AS merges,
    formatReadableSize(sum(bytes_read_uncompressed)) AS read,
    formatReadableSize(sum(bytes_written_uncompressed)) AS written
FROM system.merges
GROUP BY table;

-- Insert performance
SELECT
    table,
    count() AS inserts,
    sum(rows) AS rows,
    formatReadableSize(sum(bytes)) AS size
FROM system.query_log
WHERE type = 'QueryFinish' AND query LIKE 'INSERT%'
GROUP BY table;

# Install clickhouse-backup
curl -sfL https://github.com/AlexAkulov/clickhouse-backup/releases/latest/download/clickhouse-backup-linux-amd64.tar.gz | tar xzv

# Create backup
clickhouse-backup create

# List backups
clickhouse-backup list

# Restore backup
clickhouse-backup restore <backup-name>

# Export table data
clickhouse-client --query "SELECT * FROM umami.website_event FORMAT Native" > website_event.native

# Import table data
cat website_event.native | clickhouse-client --query "INSERT INTO umami.website_event FORMAT Native"

-- Freeze table (create snapshot)
ALTER TABLE umami.website_event FREEZE;

-- Copy frozen data
-- Data is in /var/lib/clickhouse/shadow/

-- Add TTL to table (1 year retention)
ALTER TABLE umami.website_event
MODIFY TTL created_at + INTERVAL 1 YEAR;

-- Different TTL for different columns
ALTER TABLE umami.website_event
MODIFY COLUMN ip_address String TTL created_at + INTERVAL 30 DAY;

-- Delete old data (lightweight delete)
ALTER TABLE umami.website_event
DELETE WHERE created_at < now() - INTERVAL 1 YEAR;

-- Optimize table to apply deletions
OPTIMIZE TABLE umami.website_event FINAL;