Stop Using Spark for Log Queries: I Built a Plug-and-Play Analytics Library with DuckDB in 20 Minutes

Stop Using Spark for Log Queries: I Built a Plug-and-Play Analytics Library with DuckDB in 20 Minutes

Let's start with my actual pain point. As a backend developer, I often need to temporarily analyze online logs: average latency for an endpoint on a specific day, request distribution for a user, error rates in a time window. The old way was either writing a script to read logs line-by-line or dumping data into MySQL to run SQL—the former is too slow, the latter is too heavy. Then I met DuckDB, an "in-process" analytical database that completely changed my workflow.

In this article, I'll take you from zero to finish a complete task: download 100k mock API access logs in CSV → query directly with DuckDB SQL → group endpoint latency stats → export the report. No database server installation needed, no Hadoop/Spark configuration needed, a simple Python pip install is all it takes.

Prerequisites

• Python 3.8+ installed (I use 3.11)
• Mac / Linux / Windows (DuckDB supports all)
• Basic SQL knowledge (SELECT, GROUP BY, ORDER BY) is enough
• No pre-installed databases required

Why DuckDB? Its biggest selling point is "in-process" execution—it runs embedded directly within your application process. No separate service process, no startup/connection/auth overhead. File reading finishes and it's done, memory is freed when not needed. For temporary analysis, ETL intermediate processing, or lightweight queries in unit tests, it's currently the best choice.

1. Install DuckDB

DuckDB offers clients for various languages (CLI, Python, Java, R, Rust, Go). We use the Python version for this tutorial because it's closest to daily development workflows and easy to integrate with pandas, fastapi, and others.

pip install duckdb

After installation, verify in a Python interactive session:

import duckdb
print(duckdb.sql("SELECT 'Hello DuckDB' AS msg").fetchall())

Expected output: [('Hello DuckDB',)]. If you see this, installation is successful, and you now have a local SQL engine.

Why not use system package managers? DuckDB recommends using the package manager for your language. The Python version bundles the complete C++ core, ready to use out of the box without the hassle of local C++17 compilation dependencies. If you really need to compile from source (e.g., to contribute), the official README states you need CMake, Python 3, and a C++17 compiler, then just run make. But 99% of users don't need this path.

2. Core Concept: Files as Tables

The feature that amazed me most in DuckDB is: Querying files directly as tables without importing them first.

Assume you have an api_logs.csv:

id,endpoint,method,status_code,response_time_ms,timestamp
1,/api/users,GET,200,45,2026-10-27 08:00:01
2,/api/orders,POST,201,120,2026-10-27 08:00:02
3,/api/users,GET,500,3200,2026-10-27 08:00:03

In DuckDB, one SQL line queries it:

import duckdb

## Direct SQL query against a CSV file
result = duckdb.sql("""
    SELECT endpoint, 
           COUNT(*) as total_requests,
           AVG(response_time_ms) as avg_time_ms,
           MAX(response_time_ms) as max_time_ms
    FROM 'api_logs.csv'
    GROUP BY endpoint
    ORDER BY avg_time_ms DESC
""")
print(result.fetchall())

This works because DuckDB has built-in automatic CSV schema inference. You don't need to define table structures in advance; it reads the first few rows to auto-detect column names and types. For standard CSVs with headers, it works out of the box.

The same applies to Parquet files:

SELECT * FROM 'data.parquet';

Even remote files (S3, HTTP) are supported:

SELECT * FROM 'https://example.com/data.csv';

The real value of this: You can explore data with SQL even before it's "loaded into a database". During troubleshooting, ad-hoc report generation, or data quality verification, this is 10x more efficient than traditional ETL flows.

3. Hands-on: API Log Analysis & Report Export

Theory aside, let's build something real. I'll simulate a common backend engineer scenario: exporting a daily API monitoring report.

3.1 Generate Mock Data

First, a small script to generate 100k mock logs:

import duckdb
import random
from datetime import datetime, timedelta

## Use DuckDB to generate mock data and store it as a Parquet file (much better compression than CSV)
duckdb.sql("""
    CREATE VIEW mock_logs AS
    WITH seq AS (
        SELECT unnest(generate_series(1, 100000)) AS id
    )
    SELECT 
        id,
        CASE (id % 5)
            WHEN 0 THEN '/api/users'
            WHEN 1 THEN '/api/orders'
            WHEN 2 THEN '/api/products'
            WHEN 3 THEN '/api/payments'
            ELSE '/api/health'
        END AS endpoint,
        CASE (id % 5)
            WHEN 0 THEN 'GET'
            WHEN 1 THEN 'POST'
            WHEN 2 THEN 'GET'
            WHEN 3 THEN 'POST'
            ELSE 'GET'
        END AS method,
        CASE (random() < 0.02)
            WHEN true THEN 500
            WHEN false THEN 200
        END AS status_code,
        CAST((random() * 500 + 10) AS INTEGER) AS response_time_ms,
        make_timestamptz(
            2026, 10, 1 + (id % 27), 
            CAST((id % 24) AS INTEGER), 
            CAST((id % 60) AS INTEGER), 0
        ) AS timestamp
    FROM seq;
""")

## Write to Parquet file (DuckDB built-in Parquet writer)
duckdb.sql("COPY mock_logs TO 'api_logs.parquet' (FORMAT PARQUET);")
print("✅ 100k mock logs generated: api_logs.parquet")

This uses core DuckDB SQL features:

• generate_series: Generate sequences (similar to PostgreSQL)
• unnest: Expand arrays
• CASE + random(): Simulate distributions
• COPY TO: Export query results to files

Why generate Parquet instead of CSV? Parquet is columnar storage, typically 1/5 to 1/10 the size of CSVs. DuckDB reads Parquet at "zero-copy" speed, far exceeding CSV parsing. In production, I recommend Parquet for all intermediate data.

3.2 Analysis Queries

Now we have 100k logs. Let's run some real analysis SQL:

## Scenario 1: Top 5 endpoints by average response time (excluding health checks)
print("=== Average Response Time by Endpoint ===")
duckdb.sql("""
    SELECT endpoint, method, status_code,
           COUNT(*) as requests,
           AVG(response_time_ms) as avg_ms,
           PERCENTILE_APPROX(response_time_ms, 0.99) as p99_ms
    FROM 'api_logs.parquet'
    WHERE endpoint != '/api/health'
    GROUP BY ALL
    ORDER BY avg_ms DESC
    LIMIT 5
""").show()

## Scenario 2: Daily 500 Error Rate
print("=== Daily 500 Error Rate ===")
duckdb.sql("""
    SELECT 
        CAST(timestamp AS DATE) as log_date,
        COUNT(*) as total,
        COUNT(*) FILTER (WHERE status_code = 500) as errors,
        ROUND(100.0 * COUNT(*) FILTER (WHERE status_code = 500) / COUNT(*), 2) as error_pct
    FROM 'api_logs.parquet'
    GROUP BY log_date
    ORDER BY error_pct DESC
    LIMIT 10
""").show()

Pay attention to DuckDB-specific syntax:

• GROUP BY ALL: Automatically groups by all non-aggregate columns in SELECT, saving typing.
• COUNT(*) FILTER (WHERE ...): Conditional aggregation, cleaner than CASE WHEN.
• PERCENTILE_APPROX: Approximate quantiles for P99/P95.
• .show(): A convenient DuckDB method to print tables directly in the terminal, no need for fetchall() + formatting.

3.3 Export Results

import duckdb

## Export analysis results to a separate Parquet report file
duckdb.sql("""
    COPY (
        SELECT 
            CAST(timestamp AS DATE) as log_date,
            endpoint,
            COUNT(*) as requests,
            AVG(response_time_ms) as avg_ms,
            PERCENTILE_APPROX(response_time_ms, 0.95) as p95_ms,
            PERCENTILE_APPROX(response_time_ms, 0.99) as p99_ms
        FROM 'api_logs.parquet'
        GROUP BY log_date, endpoint
        ORDER BY log_date, requests DESC
    ) TO 'daily_api_report.parquet' (FORMAT PARQUET);
""")
print("📊 Report exported: daily_api_report.parquet")

## Or export to CSV (easier for sharing with non-tech colleagues)
duckdb.sql("""
    COPY (
        SELECT endpoint, COUNT(*) as total_requests, AVG(response_time_ms) as avg_ms
        FROM 'api_logs.parquet'
        WHERE endpoint != '/api/health'
        GROUP BY endpoint
        ORDER BY total_requests DESC
    ) TO 'api_summary.csv' (FORMAT CSV, HEADER);
""")
print("📊 Summary report exported: api_summary.csv")

That concludes the full "Data Generation → SQL Analysis → Report Export" workflow. Less than 60 lines of code total, no external services.

4. Advanced Tips: Extensions & Cross-Database Queries

DuckDB's extension system allows connecting to more data sources.

4.1 Install & Load Extensions

## Install httpfs extension (enables remote file reading)
duckdb.sql("INSTALL httpfs;")
duckdb.sql("LOAD httpfs;")

## Install parquet writer (usually built-in, but good to check)
duckdb.sql("INSTALL parquet;")
duckdb.sql("LOAD parquet;")

Once httpfs is loaded, you can query remote files just like locals:

SELECT * FROM 'https://raw.githubusercontent.com/example/data/large_dataset.parquet';

4.2 Connect to Other Databases

DuckDB can even attach directly to PostgreSQL/MySQL/SQLite for cross-database JOINs without ETL:

-- Requires postgres_scanner extension
INSTALL postgres;
LOAD postgres;
ATTACH 'dbname=mydb host=localhost' AS pg (TYPE postgres);

-- You can now JOIN DuckDB tables with PG tables
SELECT d.endpoint, COUNT(*)
FROM 'api_logs.parquet' d
GROUP BY d.endpoint;

5. Common Issues & Pitfalls

Q1: Slow on large files?
DuckDB uses predicate pushdown and parallel reads for Parquet. If working with CSV, COPY to Parquet first. Files under 100MB in CSV are fine, but over 500MB, convert to Parquet.

Q2: Out of memory?
DuckDB is an in-process engine and uses as much RAM as possible by default. Limit this via SET memory_limit='4GB';. For very large data, DuckDB has a spill-to-disk mechanism, but chunking data ahead of time is recommended.

Q3: DuckDB vs pandas?
pandas is great for data cleaning and column manipulation; DuckDB is for SQL querying and aggregate analysis. If you prefer SQL, DuckDB can replace most pandas groupby/merge/join scenarios and usually runs faster. You can mix them too: duckdb.sql("SELECT * FROM my_pandas_df") lets you query a DataFrame directly as a table name.

Q4: Concurrency?
DuckDB is designed for single-process read/write concurrency (multi-threaded queries). It does not support multiple processes writing to the same database file at once. If your scenario requires multiple services sharing writes, use a traditional database server. DuckDB's sweet spot is "high-performance analysis within a single process".

6. Conclusion

We completed step-by-step:

1. pip install duckdb setup
2. Querying CSV/Parquet directly with SQL
3. Generating 100k mock logs and running statistical analysis (P99, grouped aggregations, error rates)
4. Exporting results to report files
5. Looking at extensions and cross-database query capabilities

DuckDB isn't meant to replace MySQL for business storage, but for temporary data analysis, log debugging, ETL intermediate processing, and local data validation, it saves the cost of setting up services and lets you start working with SQL immediately.

Next Steps:

• The official documentation's SQL reference guide is worth a browse: DuckDB's SQL dialect is closer to modern analytical needs than MySQL.
• Try INSTALL spatial; for geographic data analysis.
• Check out the Duck Lake format (DuckDB's own open-source lakehouse approach), which provides ACID and time travel on top of Parquet.
• If using Java, duckdb_jdbc and duckdb-native can be embedded directly in Spring Boot apps for embedded analytics.

Facing issues? DuckDB's Discord community is active, and the official docs are very clear. Give it a try.