zero_/init-clickhouse.sql at master · melsayedx/zero_ · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
-- ============================================
-- LOG INGESTION PLATFORM - CLICKHOUSE SCHEMA
-- ============================================
--
-- High-performance log storage with vector search capabilities.
--
-- DATA ISOLATION STRATEGY:
-- - Each application has a unique app_id
-- - ClickHouse queries are filtered by app_id for isolation
-- - ORDER BY (app_id, timestamp, id) optimizes queries for app-level filtering
--
CREATE DATABASE IF NOT EXISTS logs_db;

USE logs_db;

-- PRODUCTION-OPTIMIZED LOGS TABLE
CREATE TABLE logs (
    -- Primary ID (UUID)
    id UUID DEFAULT generateUUIDv7() CODEC(ZSTD(19)),

    -- Application identifier
    app_id LowCardinality(String) CODEC(ZSTD(19)),

    -- Timestamps with optimal compression
    timestamp DateTime64(3) default now() CODEC(Delta, ZSTD(19)),

    -- Log level as Enum (1 byte per entry)
    level Enum8(
        'DEBUG' = 1,
        'INFO' = 2,
        'WARN' = 3,
        'ERROR' = 4,
        'FATAL' = 5
    ) default 'INFO' CODEC(ZSTD(19)),

    -- Log message with high compression
    message String CODEC(ZSTD(19)),

    -- Source/host information
    source LowCardinality(String) CODEC(ZSTD(19)),
    environment LowCardinality(String) CODEC(ZSTD(19)),

    -- Metadata as JSON
    metadata String CODEC(ZSTD(22)),  -- Higher compression for JSON

    -- Correlation IDs
    trace_id Nullable(String) CODEC(ZSTD(19)),

    -- User context
    user_id String default '' CODEC(ZSTD(19)),

    -- =====================================
    -- SKIP INDICES (Essential for performance)
    -- =====================================

    -- Full-text search on messages
    INDEX message_idx message TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 4,

    -- Fast app filtering
    INDEX app_idx app_id TYPE set(1000) GRANULARITY 4,

    -- Bloom filter indexes for high-cardinality lookups
    INDEX trace_idx trace_id TYPE bloom_filter(0.01) GRANULARITY 3,
    INDEX user_idx user_id TYPE bloom_filter(0.01) GRANULARITY 3,

        -- Database-level constraints
    CONSTRAINT check_app_id CHECK length(app_id) > 0 AND length(app_id) <= 32,
    CONSTRAINT check_message CHECK length(message) > 0 AND length(message) <= 2048,
    CONSTRAINT check_source CHECK length(source) > 0 AND length(source) <= 64,
    CONSTRAINT check_level CHECK level IN ('DEBUG', 'INFO', 'WARN', 'ERROR', 'FATAL'),
    CONSTRAINT check_timestamp CHECK timestamp >= toDateTime64('2020-01-01 00:00:00', 3),
    CONSTRAINT check_environment CHECK length(environment) > 0 AND length(environment) <= 64,

) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (app_id, timestamp, id)
TTL timestamp + INTERVAL 90 DAY
SETTINGS
    -- Performance optimized settings for 100k/sec ingestion
    index_granularity = 16384,                    -- Larger granularity for faster inserts
    min_bytes_for_wide_part = 20971520,          -- 20 MB threshold for wide format
    max_compress_block_size = 2097152,           -- 2 MB compression blocks (larger = faster)

    -- Merge optimization for high-throughput scenarios
    merge_max_block_size = 8192,                  -- Larger merge blocks
    merge_max_block_size_bytes = 104857600,      -- 100MB merge size limit

    -- Storage optimization
    storage_policy = 'default',                   -- Use default storage policy
    min_bytes_for_compact_part = 10485760,       -- 10MB compact threshold

    -- Write optimization
    write_ahead_log_max_bytes = 1073741824,      -- 1GB WAL size
    write_ahead_log_bytes_to_fsync = 4194304,    -- 4MB fsync threshold

    -- Memory settings for merges
    max_bytes_to_merge_at_max_space_in_pool = 1073741824,  -- 1GB max merge memory
    max_bytes_to_merge_at_min_space_in_pool = 134217728;   -- 128MB min merge memory


-- ============================================
-- LOG EMBEDDINGS TABLE - Vector Search Support
-- ============================================
-- Stores embeddings for semantic log search using ClickHouse native vector capabilities.
-- Uses HNSW index for fast approximate nearest neighbor search.

CREATE TABLE IF NOT EXISTS log_embeddings (
    -- Foreign key to logs table
    log_id UUID,

    -- Vector embedding (384 dimensions for all-MiniLM-L6-v2)
    embedding Array(Float32),

    -- Original text that was embedded (for debugging/verification)
    embedded_text String CODEC(ZSTD(19)),

    -- Metadata for filtering
    app_id LowCardinality(String) CODEC(ZSTD(19)),
    timestamp DateTime64(3) CODEC(Delta, ZSTD(19)),

    -- Embedding timestamp
    created_at DateTime64(3) DEFAULT now(),

    -- Vector similarity index using HNSW algorithm
    INDEX emb_idx embedding TYPE vector_similarity('hnsw', 'cosineDistance')

) ENGINE = MergeTree()
ORDER BY (app_id, log_id)
SETTINGS index_granularity = 8192;