الترحيل من JSON إلى SQLite لتخزين OHLC — مذكرات هندسية

ملاحظات هندسة — المجلد 1

الترحيل من JSON إلى SQLite لتخزين OHLC

المشكلة

تعرض محطة رسومًا بيانية لشمعة OHLC عبر 7 أطر زمنية (1 دقيقة، 5 دقائق، 15 دقيقة، 1 ساعة، 4 ساعات، 1 يوم، 1 أسبوع) لأكثر من 30 أداة مالية. خلف كل رسم بياني توجد بيانات أسعار تاريخية يجب أن تكون:

• قابلة للاستعلام بسرعة كافية لتحميل الصفحة في أقل من 50 مللي ثانية
• محدثة كل دقيقة بعلامات جديدة من MT5
• مجمعة في أطر زمنية أعلى بشكل فوري
• محتفظ بها لمدة 3 أشهر، ثم يتم اقتطاعها

خزّن تنفيذنا الأول كل مجموعة أداة-إطار زمني كملف JSON منفصل:

data/klines/
  XAUUSD_1m.json    (2.1 MB, 28,000 صف)
  XAUUSD_5m.json    (0.5 MB, 5,600 صف)
  XAUUSD_1h.json    (0.1 MB, 720 صف)
  EURUSD_1m.json    (1.8 MB, 24,000 صف)
  ...
  // 30 أداة × 7 أطر زمنية = 210 ملفًا

عمل هذا بشكل جيد عند الإطلاق. ولكن بحلول الأسبوع الثالث، بدأت الشقوق في الظهور.

ثلاثة أنماط فشل

1. تضارب الكتابة

عندما يدفع MT5 علامة جديدة، يقوم عامل Python بفتح ملف JSON، وقراءة جميع السجلات البالغ عددها 28,000، وإلحاق واحدة، وإعادة تسلسل المصفوفة بأكملها إلى القرص، ثم إغلاق الملف. خلال الأسواق المتقلبة مع 6+ علامات في الثانية عبر 10 أدوات نشطة، يصبح إدخال/إخراج نظام الملفات هو عنق الزجاجة. يتم حظر مؤشر ترابط طلب Flask في انتظار قفل الملف، مما يتسبب في ارتفاع أوقات استجابة API من 50 مللي ثانية إلى أكثر من 2 ثانية.

2. إخفاقات الذرية

كتابة ملف JSON ليست ذرية. إذا تعطل الخادم في منتصف الكتابة (وهو ما حدث مرتين أثناء تقلب الطاقة في مثيل السحابة)، ينتهي الملف مقطوعًا — مما يؤدي إلى فقدان جميع البيانات منذ آخر نسخة احتياطية. كان علينا إعادة التشغيل من سجل MT5 للاسترداد، وهو ما استغرق أكثر من 45 دقيقة لكل أداة.

3. انهيار أداء الاستعلام

بناء شمعة مدتها ساعة واحدة من 60 شمعة مدتها دقيقة واحدة يعني تحليل 60 ملف JSON، ودمجها في الذاكرة، وتجميعها. بالنسبة لرسم بياني يظهر 100 شمعة كل ساعة عبر 6,000 دقيقة من البيانات، انتظرت الواجهة الأمامية 850 مللي ثانية في المتوسط. للسياق، تشير مؤشرات أداء الويب الأساسية من Google إلى أن أي شيء يزيد عن 100 مللي ثانية يمثل مشكلة.

ما الذي فكرنا فيه

فازت SQLite بثلاثة اعتبارات: إنها مضمنة (بدون عملية منفصلة)، ومتوافقة مع ACID (لا مزيد من فقدان البيانات)، وتستخدم 600 كيلوبايت من الذاكرة في وضع WAL — أي 0.06% من سعة خادمنا البالغة 1 جيجابايت.

الترحيل

تصميم المخطط

كانت الرؤية الرئيسية هي فصل العلامات الأولية عن الشموع المجمعة. نقوم بتخزين شموع الدقيقة الواحدة فقط كبيانات مصدر وحساب جميع الأطر الزمنية الأعلى عبر تجميع SQL:

CREATE TABLE kline_1m (
    symbol    TEXT NOT NULL,    -- XAUUSD, EURUSD
    ts        INTEGER NOT NULL, -- الطابع الزمني لـ Unix لفتح الشمعة
    open      REAL NOT NULL,
    high      REAL NOT NULL,
    low       REAL NOT NULL,
    close     REAL NOT NULL,
    volume    INTEGER DEFAULT 0,
    PRIMARY KEY (symbol, ts)
);

CREATE INDEX idx_kline_symbol_ts ON kline_1m(symbol, ts);

مع هذا المخطط، يصبح حساب أي إطار زمني أعلى استعلامًا واحدًا:

-- شموع الساعة الواحدة من بيانات الدقيقة الواحدة
SELECT
    (ts / 3600) * 3600 AS hour_ts,
    symbol,
    FIRST_VALUE(open) OVER w AS open,
    MAX(high) OVER w AS high,
    MIN(low) OVER w AS low,
    LAST_VALUE(close) OVER w AS close,
    SUM(volume) OVER w AS volume
FROM kline_1m
WHERE symbol = ? AND ts BETWEEN ? AND ?
WINDOW w AS (PARTITION BY (ts / 3600) * 3600 ORDER BY ts);

نص الترحيل

كتبنا نص ترحيل لمرة واحدة يقوم بـ:

1. قراءة كل ملف JSON على شكل أجزاء (وليس دفعة واحدة، لتجنب ارتفاع الذاكرة)
2. إزالة التكرارات حسب (الرمز، الطابع الزمني) — كانت ملفات JSON قد تراكمت بها 3% من التكرارات بسبب حالات إعادة التشغيل الحدودية
3. الإدراج في دفعات من 500 صف باستخدام معاملات BEGIN/COMMIT
4. التحقق من تطابق عدد الصفوف بعد الترحيل
5. الاحتفاظ بملفات JSON كنسخة احتياطية لمدة 72 ساعة، ثم حذفها

import json, sqlite3, os, glob

conn = sqlite3.connect("klines.db")
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA synchronous=NORMAL")

batch = []
total = 0

for fpath in sorted(glob.glob("data/klines/*.json")):
    symbol = os.path.basename(fpath).split("_")[0]
    with open(fpath) as f:
        rows = json.load(f)

    for row in rows:
        batch.append((
            symbol, row["ts"], row["o"], row["h"],
            row["l"], row["c"], row.get("v", 0)
        ))

        if len(batch) >= 500:
            conn.executemany(
                "INSERT OR IGNORE INTO kline_1m VALUES (?,?,?,?,?,?,?)",
                batch
            )
            conn.commit()
            total += len(batch)
            batch = []

# الدفعة النهائية
if batch:
    conn.executemany("INSERT OR IGNORE INTO kline_1m ...", batch)
    conn.commit()

print(f"تم ترحيل {total} صفًا")

استغرق تشغيل النص 12 ثانية على الخادم الإنتاجي. تحققنا من تطابق عدد الصفوف بين JSON وSQLite باستخدام استعلام موازٍ، ثم أزلنا دليل JSON بعد 72 ساعة.

النتائج

ما كنا سنفعله بشكل مختلف

1. البدء بـ SQLite من اليوم الأول. أهدرنا 3 أسابيع في تصحيح أخطاء أقفال ملفات JSON التي يعالجها SQLite بشكل أصلي. تكلفة الذاكرة البالغة 600 كيلوبايت لا تذكر حتى على خادم بسعة 1 جيجابايت.
2. استخدام وضع WAL فورًا. بدأنا بوضع سجل DELETE، الذي كان يمنع القراء أثناء الكتابة. التبديل إلى WAL (سجل الكتابة المسبقة) أعطانا قراءات متزامنة أثناء الكتابة — وهو أمر بالغ الأهمية لتقديم الرسوم البيانية أثناء وصول العلامات.
3. الإدراج المجمع. كان تنفيذنا الأول يقوم بإدراج فردي لكل علامة. أدى تجميع 500 صف لكل معاملة إلى تحسين إنتاجية الكتابة بمقدار 40 مرة.
4. إضافة مهمة cron للاحتفاظ بالبيانات من اليوم الأول. نسينا اقتطاع البيانات القديمة خلال الشهر الأول. الآن يتولى أمر بسيط مثل DELETE FROM kline_1m WHERE ts < strftime('%s','now','-3 months') في crontab هذا الأمر تلقائيًا.

لماذا ليس PostgreSQL؟

نتلقى هذا السؤال كثيرًا. PostgreSQL هي قاعدة بيانات ممتازة. ولكن بالنسبة لمحطة تداول على خادم واحد تحتاج إلى العمل على VPS بسعر 2 دولار شهريًا مع 1 جيجابايت من ذاكرة الوصول العشوائي، فهي الأداة الخاطئة. يبلغ الحد الأدنى لمساحة الذاكرة القابلة للاستخدام في PostgreSQL حوالي 200 ميجابايت للمخازن المؤقتة المشتركة وحدها. يعمل SQLite داخل العملية بحوالي 600 كيلوبايت. هذا فرق بمقدار 333 مرة لحالة الاستخدام الخاصة بنا.

المقايضة هي أن SQLite لا يتعامل مع الكتاب المتزامنين بشكل جيد. لكن نمط الكتابة لدينا هو كاتب واحد (عملية ضخ بيانات MT5 واحدة)، وهو بالضبط ما يتفوق فيه SQLite. إذا احتجنا يومًا إلى كتّاب متعددين، فسنقوم بتقييم PostgreSQL — ولكن على نطاقنا الحالي من 1-2 علامة/ثانية عبر 30 أداة، فإن SQLite ليس عنق الزجاجة.