Building a High-Performance EVE Online Market Data Pipeline with DuckDB Link to heading

Introduction Link to heading

EVE Online’s player-driven economy generates millions of market orders daily across New Eden’s trade hubs. For market traders, industrialists, and data analysts, accessing this treasure trove of economic data is essential for identifying arbitrage opportunities, tracking price trends, and understanding market dynamics.

This article examines the development of a lightweight yet powerful data pipeline that extracts real-time market data from EVE’s ESI API and loads it into DuckDB—a high-performance analytical database designed for OLAP workloads. We’ll explore why DuckDB is the perfect choice for EVE market analysis and how to build a scalable data ingestion system.

The Problem: EVE Market Data at Scale Link to heading

Understanding EVE’s Market Structure Link to heading

EVE Online’s market system spans:

• 60+ regions across high-sec, low-sec, null-sec, and wormhole space
• ~33,000 tradeable item types from ammunition to capital ships
• Millions of active orders with real-time price discovery
• Paginated API responses requiring multiple HTTP requests per region

Example: The Forge region (home to Jita, EVE’s largest trade hub) typically has 200,000+ active orders across 20+ API pages.

The Data Challenge Link to heading

Traditional approaches face several issues:

1. Relational Database Overkill: PostgreSQL/MySQL add unnecessary complexity for analytical workloads
2. CSV Limitations: No type safety, poor query performance, manual schema management
3. In-Memory Constraints: Pandas DataFrames consume excessive RAM for large datasets
4. Query Inefficiency: Aggregating millions of rows without proper indexing is slow

Why DuckDB? Link to heading

DuckDB is an embedded analytical database (think “SQLite for analytics”) that excels at our use case:

Key Advantages Link to heading

Performance Comparison Link to heading

For a typical Jita market snapshot (~200,000 orders):

DuckDB provides the best balance of load speed, query performance, and resource efficiency.

System Architecture Link to heading

High-Level Data Flow Link to heading

ESI API → Pagination Handler → DataFrame → DuckDB
   ↓                ↓              ↓           ↓
Paginated      Progress Bar    Type Safe   Columnar
 JSON            (tqdm)        Conversion   Storage

Component Breakdown Link to heading

1. API Client Layer Link to heading

def get_esi_data(url):
    r = requests.get(url)
    r.raise_for_status()
    return r.json()

Simple, focused function that:

• Handles a single HTTP GET request
• Raises exceptions for HTTP errors (4xx, 5xx)
• Returns parsed JSON

Design Decision: Separating single-page fetching from pagination logic enables easy retry logic and testing.

2. Pagination Engine Link to heading

total_pages = int(response.headers.get('X-Pages', 1))
for page in tqdm(range(2, total_pages + 1)):
    all_orders.extend(get_esi_data(page_url))

The ESI API uses HTTP headers to communicate pagination metadata:

• X-Pages: Total number of pages available
• Each page contains up to 1,000 orders
• Pages are 1-indexed (start at page 1)

Progressive Feedback: tqdm provides a real-time progress bar, critical for multi-minute downloads.

3. Data Loading Pipeline Link to heading

df = pd.DataFrame(orders_data)
conn.execute("CREATE OR REPLACE TABLE market_orders AS SELECT * FROM df")

DuckDB’s killer feature: zero-copy DataFrame ingestion. The FROM df syntax reads pandas DataFrames directly without serialization.

Implementation Deep Dive Link to heading

The Fetch Function: Handling ESI Pagination Link to heading

def fetch_all_market_orders(region_id, type_id=None):
    url = f"https://esi.evetech.net/latest/markets/{region_id}/orders/"
    url += "?datasource=tranquility&order_type=all"
    
    if type_id:
        url += f"&type_id={type_id}"

URL Construction Strategy:

• region_id: Numeric identifier (e.g., 10000002 for The Forge)
• order_type=all: Returns both buy and sell orders
• type_id: Optional filter for single item analysis

Pagination Loop:

response = requests.get(url + "&page=1")
total_pages = int(response.headers.get('X-Pages', 1))
all_orders = response.json()

for page in tqdm(range(2, total_pages + 1)):
    page_url = f"{url}&page={page}"
    all_orders.extend(get_esi_data(page_url))

Why start at page 2? We’ve already fetched page 1 to read the X-Pages header.

DuckDB Integration: Automatic Schema Inference Link to heading

conn = duckdb.connect(database=db_path)
conn.execute("CREATE OR REPLACE TABLE market_orders AS SELECT * FROM df")

What’s Happening Under the Hood:

1. DuckDB reads the DataFrame’s dtypes
2. Maps pandas types to SQL types:
   • int64 → BIGINT
   • float64 → DOUBLE
   • object → VARCHAR
   • datetime64 → TIMESTAMP
3. Creates columnar storage with compression
4. Builds internal indexes for query optimization

CREATE OR REPLACE: Idempotent operation—rerunning the script overwrites the table cleanly.

Sample ESI Response Structure Link to heading

{
  "duration": 90,
  "is_buy_order": false,
  "issued": "2024-01-15T12:34:56Z",
  "location_id": 60003760,
  "min_volume": 1,
  "order_id": 6514363498,
  "price": 5.47,
  "range": "region",
  "system_id": 30000142,
  "type_id": 34,
  "volume_remain": 9574126,
  "volume_total": 10000000
}

Key Fields:

• is_buy_order: Distinguishes bids from asks
• price: ISK per unit
• volume_remain: Current unfilled quantity
• location_id: Station/structure identifier
• type_id: Item type reference

Advanced Analytics with DuckDB Link to heading

Once loaded, DuckDB enables sophisticated market analysis with pure SQL:

1. Bid-Ask Spread Analysis Link to heading

WITH buy_orders AS (
  SELECT type_id, MAX(price) as best_buy
  FROM market_orders
  WHERE is_buy_order = true
  GROUP BY type_id
),
sell_orders AS (
  SELECT type_id, MIN(price) as best_sell
  FROM market_orders
  WHERE is_buy_order = false
  GROUP BY type_id
)
SELECT 
  b.type_id,
  b.best_buy,
  s.best_sell,
  (s.best_sell - b.best_buy) as spread,
  ((s.best_sell - b.best_buy) / b.best_buy * 100) as spread_pct
FROM buy_orders b
JOIN sell_orders s ON b.type_id = s.type_id
ORDER BY spread_pct DESC
LIMIT 10;

Identifies arbitrage opportunities where spread exceeds transaction costs.

2. Volume-Weighted Average Price (VWAP) Link to heading

SELECT 
  type_id,
  SUM(price * volume_remain) / SUM(volume_remain) as vwap
FROM market_orders
WHERE is_buy_order = false
GROUP BY type_id;

VWAP provides more accurate pricing than simple averages for thin markets.

3. Market Depth Analysis Link to heading

SELECT 
  type_id,
  is_buy_order,
  COUNT(*) as num_orders,
  SUM(volume_remain) as total_volume,
  AVG(price) as avg_price,
  STDDEV(price) as price_volatility
FROM market_orders
GROUP BY type_id, is_buy_order;

Market depth metrics indicate liquidity and price stability.

4. Station Competition Analysis Link to heading

SELECT 
  location_id,
  COUNT(DISTINCT type_id) as items_traded,
  COUNT(*) as total_orders,
  SUM(volume_remain * price) as total_market_value
FROM market_orders
GROUP BY location_id
ORDER BY total_market_value DESC
LIMIT 20;

Identifies major trade hubs and emerging markets.

Performance Optimization Strategies Link to heading

1. Incremental Loading Link to heading

For continuous data collection, use INSERT instead of CREATE OR REPLACE:

# First load
conn.execute("CREATE TABLE IF NOT EXISTS market_orders AS SELECT * FROM df")

# Subsequent loads
conn.execute("INSERT INTO market_orders SELECT * FROM df")

Add a timestamp column for tracking:

df['snapshot_time'] = pd.Timestamp.now()

2. Partitioning by Region Link to heading

For multi-region analysis, partition data:

CREATE TABLE market_orders_partitioned (
  snapshot_time TIMESTAMP,
  region_id INTEGER,
  -- other columns
) PARTITION BY (region_id);

Queries filtering by region skip irrelevant partitions.

3. Compression Link to heading

DuckDB automatically compresses columnar data. For long-term storage, export to Parquet:

conn.execute("COPY market_orders TO 'jita_2024_01_15.parquet' (FORMAT PARQUET)")

Parquet files achieve 10-20× compression vs CSV with faster load times.

4. Connection Pooling Link to heading

For high-frequency updates, reuse connections:

class MarketDataPipeline:
    def __init__(self, db_path):
        self.conn = duckdb.connect(database=db_path)
    
    def ingest(self, orders_data):
        df = pd.DataFrame(orders_data)
        self.conn.execute("INSERT INTO market_orders SELECT * FROM df")
    
    def close(self):
        self.conn.close()

Error Handling and Resilience Link to heading

HTTP Error Management Link to heading

def get_esi_data_with_retry(url, max_retries=3):
    for attempt in range(max_retries):
        try:
            r = requests.get(url, timeout=10)
            r.raise_for_status()
            return r.json()
        except requests.exceptions.RequestException as e:
            if attempt == max_retries - 1:
                raise
            time.sleep(2 ** attempt)  # Exponential backoff

ESI Rate Limiting: CCP implements rate limits. Exponential backoff prevents cascading failures.

Data Validation Link to heading

def validate_market_data(df):
    assert 'order_id' in df.columns, "Missing order_id column"
    assert df['price'].min() > 0, "Invalid negative prices found"
    assert df['volume_remain'].min() >= 0, "Invalid negative volume"
    return df

Sanity checks prevent corrupted data from entering the database.

Graceful Degradation Link to heading

if __name__ == "__main__":
    try:
        market_data = fetch_all_market_orders(REGION_ID)
        if market_data:
            load_data_to_duckdb(market_data)
    except Exception as e:
        logging.error(f"Pipeline failed: {e}")
        # Send alert, retry later, etc.

Real-World Use Cases Link to heading

1. Station Trading Bot Link to heading

Load market data every 5 minutes, identify arbitrage opportunities with <2% spread, execute profitable trades.

2. Market Trend Dashboard Link to heading

Historical snapshots enable time-series analysis:

SELECT 
  DATE_TRUNC('hour', snapshot_time) as hour,
  type_id,
  AVG(price) as avg_hourly_price
FROM market_orders
WHERE type_id = 34  -- Tritanium
GROUP BY hour, type_id
ORDER BY hour;

3. Industry Profitability Calculator Link to heading

Join market data with manufacturing recipes:

SELECT 
  blueprint_type_id,
  SUM(input_cost) as material_cost,
  output_price - SUM(input_cost) as profit_margin
FROM manufacturing_jobs
JOIN market_orders ON ...
GROUP BY blueprint_type_id;

4. Regional Price Comparison Link to heading

Load multiple regions, compare prices:

SELECT 
  type_id,
  region_id,
  MIN(price) as lowest_sell
FROM market_orders
WHERE is_buy_order = false
GROUP BY type_id, region_id
HAVING COUNT(*) > 5  -- Liquid markets only
ORDER BY type_id, lowest_sell;

Lessons Learned Link to heading

1. DuckDB Is Not a Replacement for Everything Link to heading

• ✅ Use for: Analytics, data science, ETL pipelines
• ❌ Don’t use for: High-concurrency OLTP, web application backends

2. Schema Evolution Requires Planning Link to heading

DuckDB’s automatic schema inference is convenient but brittle. Production systems should define explicit schemas:

CREATE TABLE market_orders (
  order_id BIGINT PRIMARY KEY,
  type_id INTEGER NOT NULL,
  price DOUBLE NOT NULL,
  -- etc.
);

3. Progress Feedback Is Essential Link to heading

Fetching 20+ pages takes minutes. Without tqdm, users assume the script has hung.

4. Pagination Edge Cases Exist Link to heading

• Empty regions return 0 pages (not 1)
• Expired orders may disappear mid-fetch
• ESI sometimes returns 500 errors under load

Robust production code handles these gracefully.

5. DuckDB’s Columnar Format Shines for Aggregations Link to heading

Calculating average prices across 200,000 orders:

• Row-based database: Reads entire table (slow)
• DuckDB: Reads only price column (fast)

Future Enhancements Link to heading

1. Time-Series Database Integration Link to heading

Export to TimescaleDB or InfluxDB for advanced temporal queries.

2. WebSocket Streaming Link to heading

ESI offers WebSocket feeds for real-time updates instead of polling.

3. Distributed Processing Link to heading

Use Dask or Ray for parallel multi-region ingestion.

4. Machine Learning Pipeline Link to heading

Train price prediction models:

import duckdb
features = duckdb.sql("""
  SELECT type_id, hour_of_day, day_of_week, avg_price, volume
  FROM market_snapshots
""").df()

# Train scikit-learn model

5. Data Quality Monitoring Link to heading

Track metrics:

• Missing order IDs
• Price anomalies (>3 standard deviations)
• API failure rates

Conclusion Link to heading

This EVE Online market data pipeline demonstrates DuckDB’s power for analytical workloads. By combining ESI’s comprehensive market API with DuckDB’s high-performance columnar storage, we’ve built a system that:

• ✅ Ingests 200,000+ orders in seconds
• ✅ Enables complex SQL analytics without database administration
• ✅ Scales from laptop prototypes to production pipelines
• ✅ Provides a foundation for algorithmic trading and market research

The design patterns shown here—pagination handling, zero-copy ingestion, columnar analytics—apply broadly to any data pipeline working with REST APIs and analytical queries.

For EVE market traders and data enthusiasts, this pipeline unlocks insights previously accessible only to dedicated market analysts with enterprise database infrastructure. DuckDB democratizes high-performance analytics.

Technical Specifications:

• Language: Python 3.8+
• Database: DuckDB 0.9+
• Dependencies: requests, pandas, duckdb, tqdm
• API: EVE ESI v1
• Performance: ~2 seconds to load 200,000 orders
• Storage: ~12 MB for Jita market snapshot (compressed)

Resources:

• DuckDB Documentation
• EVE ESI Market Endpoint
• DuckDB vs Pandas Performance
• EVE Market Data Discord