دليل تشغيل: مخطط JSON

CREATE TABLE events
(
    `timestamp` DateTime,
    `service`   LowCardinality(String),
    `level`     Enum8('DEBUG' = 1, 'INFO' = 2, 'WARN' = 3, 'ERROR' = 4),
    `message`   String,
    `host`      LowCardinality(String),
    `duration_ms` UInt32
)
ENGINE = MergeTree
ORDER BY (service, timestamp)

-- تأكد من أن أنواع الأعمدة تطابق المتوقع
DESCRIBE TABLE events FORMAT Vertical

-- أدرِج بيانات واستعلم للتحقق من أن المخطط يتعامل مع بياناتك
INSERT INTO events FORMAT JSONEachRow
{"timestamp":"2025-03-19 10:00:00","service":"api","level":"INFO","message":"request handled","host":"node-1","duration_ms":42}

SELECT service, level, duration_ms FROM events WHERE service = 'api'

CREATE TABLE events
(
    `timestamp`  DateTime,
    `service`    LowCardinality(String),
    `level`      Enum8('DEBUG' = 1, 'INFO' = 2, 'WARN' = 3, 'ERROR' = 4),
    `message`    String,
    `host`       LowCardinality(String),
    `duration_ms` UInt32,
    `attributes` JSON(
        max_dynamic_paths = 256,
        `http.status_code` UInt16,
        `http.method` LowCardinality(String),
        SKIP REGEXP 'debug\..*'
    )
)
ENGINE = MergeTree
ORDER BY (service, timestamp)

-- أدرج بيانات نموذجية وافحص المسارات المستنتجة
INSERT INTO events FORMAT JSONEachRow
{"timestamp":"2025-03-19 10:00:00","service":"api","level":"INFO","message":"request handled","host":"node-1","duration_ms":42,"attributes":{"http.status_code":200,"http.method":"GET","user.region":"eu-west","custom_tag":"abc"}}

SELECT JSONAllPathsWithTypes(attributes)
FROM events
FORMAT PrettyJSONEachRow

CREATE TABLE dynamic_events
(
    `id`   UInt64,
    `ts`   DateTime DEFAULT now(),
    `data` JSON(
        max_dynamic_paths = 512,
        `event_type` LowCardinality(String),
        `version` UInt8
    )
)
ENGINE = MergeTree
ORDER BY (data.event_type, ts)

INSERT INTO dynamic_events (id, data) FORMAT JSONEachRow
{"id": 1, "data": {"event_type": "click", "version": 2, "page": "/home", "button_id": "cta-1"}}
{"id": 2, "data": {"event_type": "purchase", "version": 1, "item_id": "SKU-99", "amount": 49.99, "currency": "USD"}}

-- تحقّق من المسارات التي اكتشفها ClickHouse وأنواعها
SELECT JSONAllPathsWithTypes(data) FROM dynamic_events FORMAT PrettyJSONEachRow

-- الاستعلام عن مسار محدد
SELECT data.page FROM dynamic_events WHERE data.event_type = 'click'

CREATE TABLE raw_events
(
    `id`        UInt64,
    `received`  DateTime DEFAULT now(),
    `payload`   String
)
ENGINE = MergeTree
ORDER BY (received)

INSERT INTO raw_events (id, payload) VALUES
(1, '{"type":"click","page":"/home"}'),
(2, '{"type":"purchase","item":"SKU-99","amount":49.99}')

-- تأكيد سلامة البيانات بعد الكتابة ثم القراءة
SELECT payload FROM raw_events WHERE id = 1

-- التحقق من أنه لا يزال بإمكانك تحليل الحقول عند الحاجة بشكل مخصص
SELECT JSONExtractString(payload, 'type') AS event_type FROM raw_events