Dice Chess Wiki

06 ETL Pipeline - SQLite to PostgreSQL

3 min read

ETL Pipeline: SQLite to PostgreSQL

Наш фундамент (схема базы данных в PostgreSQL) успешно заложен. Я изучил предоставленную тобой базу данных dicechess.db. Оказалось, что данные лежат в таблице games, а сами ходы упакованы в колонку moves_json. Всего в таблице ровно 141,703 партии. Формат JSON полностью совпадает с тем, что я видел ранее.

Это отличные новости! Нам не нужно возиться с 140 000 отдельных файлов. Читать из SQLite гораздо удобнее и быстрее.

User Review Required

Ниже представлен обновленный план. Если он выглядит логично, мы приступим к написанию и запуску скрипта импорта.

Описание исходных данных

В таблице games (SQLite) есть:

  • Метаданные: game_id, white_player_username, black_player_username, tournament_id, result, allow_doubling, start_time, bet, time_limit, time_bonus, white_player_rating, black_player_rating.
  • Вспомогательные данные: metadata_json
  • Основные ходы: moves_json

Формат moves_json внутри выглядит так:

{
  "gameMoveHistoryStateMap": {
    "0": { "fen": "...", "dices": [], "gameMoveHistoryMove": null },
    "1": { "fen": "...", "dices": [...], "gameMoveHistoryMove": null },
    "2": { "fen": "...", "dices": [...], "gameMoveHistoryMove": {"from": "E2", "to": "E4"} }
  }
}

Proposed Changes

Мы создадим Python скрипт src/importer.py, который будет подключаться одновременно к двум базам (SQLite на чтение, PostgreSQL на запись).

1. Парсинг FEN (DFEN)

Из каждого состояния мы берем поле fen. Мы разделяем его по пробелам, чтобы получить normalized_fen (первые 4 группы: расположение фигур, цвет, рокировка, битое поле).

parts = fen.split(" ")
normalized_fen = " ".join(parts[0:4])

Затем мы считаем хэш: xxhash.xxh64(normalized_fen).intdigest().

2. Сборка микро-ходов в Turn

Поскольку в JSON записан каждый микро-ход (состояние до хода, после первого, после второго), скрипт должен объединять их в единый логический ход (Turn).

  • Начало хода: Цвет активного игрока не меняется, кубики брошены (dices не пустой). Мы фиксируем position_id и значение кубиков.
  • Микро-ходы: Мы собираем gameMoveHistoryMove (например, E2E4, G1F3) в список played_moves.
  • События: Если в moves_json мы видим изменение поля bank (ставка), мы генерируем GameEvent удвоения.
  • Конец хода: Цвет активного игрока меняется или ходы закончились. Ход закрывается и сохраняется.

3. Кэширование позиций и пакетная вставка (Batching)

Позиций будут миллионы. Чтобы импорт не занял неделю:

  1. Скрипт будет держать в памяти локальный dict хэшей позиций.
  2. Мы будем читать партии из SQLite блоками по 1000 штук.
  3. Для каждой 1000 партий мы будем использовать массовую вставку (INSERT ... ON CONFLICT DO NOTHING) в PostgreSQL.
  4. Мы будем использовать библиотеку tqdm для красивого прогресс-бара, чтобы ты видел, с какой скоростью переливаются твои 140 тысяч партий.

Verification Plan

Automated Tests

  • Перед запуском на всей базе мы прогоним импорт на первых 10 партиях (LIMIT 10) и убедимся, что они корректно разложились по таблицам players, games, turns, positions в PostgreSQL.

Manual Verification

  • После завершения импорта мы проверим SELECT COUNT(*) FROM games; в PostgreSQL (должно быть 141,703).
  • Выполним сложный аналитический запрос, чтобы убедиться, что структура работает идеально.

Graph View