Skip to content

ARCHITECTURE.md

Latest commit

 

History

History
104 lines (80 loc) · 7.26 KB

ARCHITECTURE.md

File metadata and controls

104 lines (80 loc) · 7.26 KB

Massive Data Architecture Summary

본 문서는 SQLP 대규모 데이터 생성을 위해 수립되었던 계획(Plan), 작업 내역(Task), 그리고 **최종 결과 가이드(Walkthrough)**를 하나로 통합한 문서입니다.

1. 계획 (Implementation Plan)

Data Generation Strategy: Streaming to CSV

대용량 데이터를 메모리에 모두 올려놓지 않고 일정 개수(예: 1만 건)마다 파일에 쓰는(Flush) 스트리밍 방식을 도입합니다.

  • 포맷 변경: 03_seed_data.sql에 INSERT 문을 쓰는 대신, output/data/ 디렉토리에 각 테이블명으로 된 users.csv, orders.csv 등의 쉼표(또는 파이프 |) 분리 파일을 생성합니다.

Bulk Loading with SQL*Loader (sqlldr)

수십만 건의 데이터를 INSERT 쿼리로 실행하면 Oracle 엔진에서 파싱 및 실행 오버헤드로 인해 엄청난 시간이 소요됩니다.

  • Control Files (.ctl): 파이썬 스크립트가 각 테이블에 맞는 SQL*Loader 제어 파일(users.ctl, orders.ctl 등)을 자동으로 생성하도록 합니다.
  • Load Script: install.sql 실행 시 DDL(테이블, 인덱스 생성)이 완료되면, 운영체제 쉘 스크립트(load_data.sh 또는 load_data.bat)를 통해 sqlldr 명령어를 일괄 실행하도록 구성합니다.

Expanded 30-Table Architecture

제공해주신 3가지 영역(핵심 커머스, 고객 참여, 분석/리워드)에 해당하는 30개 테이블을 모두 OOP Table 스키마에 정의합니다.

  • 의존성 순서 데이터 생성: 외래키 제약조건 위배를 막기 위해 위상 정렬(Topological Sort) 순서대로 제너레이터를 실행합니다.
    1. customers, staff, categories, suppliers
    2. products, customer_addresses
    3. orders, carts, wishlists
    4. order_items, payments, shipping, reviews
    5. point_transactions, inventory_transactions 등

Realistic Business Logic Generation

SQLP 시험에 적합한 데이터 분포와 튜닝 실습 환경을 위해 다음 로직을 Generator에 추가합니다.

  • Skewed Data (데이터 편향): 특정 날짜(연말, 이벤트 기간)나 특정 카테고리에 주문이 몰리도록 가중치(Weights)를 부여.
  • SCD Type 2: customer_grade_history 등에서 이력 데이터의 start_date와 end_date가 선분 이력(Continuous Time) 구조를 완벽히 가지도록 구현.
  • 계층형 구조: 카테고리(categories)와 사원(staff) 테이블에 재귀적 복합 관계(Self-Join)를 형성하기 위한 부모-자식 로직 보강.

2. 작업 내역 (Task List)

Phase 1: Streaming Exporter Architecture

  • Update OracleExporter to write data incrementally to .csv files instead of .sql.
  • Implement automatic generation of sqlldr Control Files (.ctl) for each table.
  • Generate shell scripts (load_data.sh and load_data.bat) to automate the bulk load process.

Phase 2: Expanded OOP Schema Definitions (src/schema_definition.py)

  • Define Core Commerce tables (12 tables: categories, suppliers, products, product_images, product_prices, customers, customer_addresses, staff, orders, order_items, payments, shipping).
  • Define Customer Engagement tables (7 tables: reviews, wishlists, carts, cart_items, complaints, returns, product_qna).
  • Define Analytics/Reward tables (11 tables: point_transactions, product_views, promotions, promotion_products, customer_grade_history, calendar, tags, product_tags, inventory_transactions, coupons, coupon_usage).

Phase 3: High-Volume Data Generators (src/generators/)

  • Modify BaseGenerator to support Python generators (yield) for chunked streaming to disk, keeping memory footprint low.
  • Implement/Update Generators for Core Commerce (Handling massive row counts and relationships).
  • Implement Generators for Engagement (Reviews, Carts, Q&A).
  • Implement Generators for Analytics (130k+ point transactions, 300k+ product views).
  • Apply specific business logic (SCD Type 2 for customer_grade_history, skewed distribution for orders).

Phase 4: Main Orchestration & Verification

  • Update src/main.py to orchestrate topological data generation and streaming.
  • Run main.py to generate the entire dataset and sqlldr scripts.
  • Verify generated files, CSV formats, and control files.

3. 결과 및 가이드 (Walkthrough)

We have successfully overhauled the data generator to support generating millions of rows without memory exhaustion, tailored specifically for Oracle SQL*Loader bulk loading.

1. Streaming Generator Architecture

Instead of appending massive lists of dictionaries in memory (which caused MemoryErrors for >100k rows), we implemented Python Generator (yield) functions in src/generators/massive_generator.py.

This enables main.py to stream data chunk by chunk directly into .csv files. The total execution time to generate ~200,000+ rows across core tables (like orders and order_items) is now under 1 minute with negligible memory overhead.

2. Oracle SQL*Loader Bulk Loading

To solve Oracle's slow execution of sequential INSERT statements, the OracleExporter now dynamically generates:

  • .csv Files: Placed in output/data/ (e.g., orders.csv, products.csv).
  • Control Files (.ctl): A custom control file is generated for each table to instruct SQL*Loader on how to map CSV fields to Oracle columns, including explicit DATE "YYYY-MM-DD HH24:MI:SS" mappings.
  • Automation Scripts: load_data.sh and load_data.bat are generated in the output/ directory to orchestrate the entire loading process in one click.

3. Comprehensive 30-Table Schema

We successfully implemented the complete 30-table OOP schema matching your specifications for SQLP exam practice:

  1. Core Commerce: Categories, Suppliers, Staff, Customers, Products, Orders, etc.
  2. Engagement: Reviews, Carts, Complaints, Q&A, Returns, etc.
  3. Analytics/Rewards: Point Transactions, Product Views, Promotions, Grade History, etc.

Note: For this initial run, the Engagement and Analytics table generators are currently emitting 0 rows (acting as structure stubs) so you can verify the pipeline. They can be trivially filled with the same yield logic.

How to Test the Massive Load

  1. Setup the Database Objects: Run @output/install.sql in SQL*Plus to create the user, tables, sequences, and constraints.
  2. Fix File Permissions (Docker Environment Only): If you copied the generated files into a Docker container using docker cp, the files may be owned by root. This causes a SQL*Loader-522: lfiopn failed for file error because the oracle user cannot write log files to the directory. To fix this, run the following command from your host machine before loading data: 
    docker exec -u 0 <container_name> chown -R oracle /path/to/directory
    (Example: docker exec -u 0 oracle26ai chown -R oracle /home/oracle/all_schema)
  3. Execute the Bulk Load: Open your terminal/command prompt, navigate to the output/ directory, and run: 
    # Mac/Linux
sh load_data.sh

# Windows
load_data.bat
    You will be prompted for the ECOMMERCE user password, and then you'll see SQLLoader streaming records into Oracle at extremely high speeds!*