DuckDB Log Analytics for Crawl Analysis

DuckDB turns a raw access log into a queryable analytical database with a single SQL statement and no server to run. For crawl analysis that sits between the fragile one-off pipeline and the heavyweight cluster, it is often the sweet spot: you get real GROUP BY, window functions, and joins over multi-gigabyte logs on a laptop, reading the file in place without a load step. This guide builds a reproducible DuckDB workflow that parses combined-format lines, isolates verified crawler traffic, and emits the crawl-budget reports you would otherwise assemble by chaining awk and grep commands for log filtering.

The appeal is operational simplicity. Where an ELK Stack log ingestion pipeline needs Filebeat, a parser, and a running index, DuckDB is a single binary that reads the log where it lands. You trade always-on search for zero standing infrastructure, which is exactly the trade a periodic SEO audit wants.

What you will accomplish:

• Read compressed or plain access logs directly with read_csv, no import step.
• Parse combined-format lines into typed columns with a single regexp_extract pattern.
• Aggregate crawl distribution by status, section, and hour with plain SQL.
• Export a shareable crawl-budget report and persist a reusable .duckdb database.

The diagram below shows the flow: DuckDB reads the access log as rows of raw text, a regex projects each line into structured columns, SQL aggregates the result, and you export a report — all in one process.

Prerequisites

You need the DuckDB CLI (1.x or newer) and a combined-format access log. DuckDB ships as a single self-contained binary, so installation is a download-and-run affair with no service to manage. Confirm the version and that it launches an in-memory session:

duckdb --version
duckdb -c "SELECT 'ready' AS status;"

Expected Output:

v1.1.3 19864453f7
┌─────────┐
│ status  │
│ varchar │
├─────────┤
│ ready   │
└─────────┘

If you plan to read .gz logs directly, no extra step is required — DuckDB detects gzip by extension. To read logs sitting in object storage later, you would install the httpfs extension, but for local analysis the core binary is enough. Decide up front whether your logs are Apache or Nginx combined format; the regex below assumes the standard field order documented in the Apache vs Nginx log format differences, and a positional shift there is the most common cause of empty parsed columns.

Environment Setup: Reading Logs In Place

The core idea is to read each log line as a single text value, then parse it with SQL rather than asking the CSV reader to split on spaces — the combined format's bracketed timestamp and quoted request line break naive whitespace splitting. Point read_csv at the file, force a single column, and disable delimiter guessing by choosing a delimiter that never appears in a line.

-- Load every line as one VARCHAR column named "line"
CREATE TABLE raw AS
SELECT column0 AS line
FROM read_csv('/var/log/nginx/access.log',
              header = false,
              columns = {'column0': 'VARCHAR'},
              delim = '\x07',   -- a byte that never appears, so each row = one full line
              quote = '');
SELECT count(*) AS lines FROM raw;

Expected Output:

┌─────────┐
│  lines  │
│  int64  │
├─────────┤
│ 2841097 │
└─────────┘

Verification command. Before parsing, confirm the raw rows look like log lines and not a mangled split. SELECT line FROM raw LIMIT 1; should return a complete combined-format entry with its quoted user-agent intact. If you see a truncated fragment, the delimiter guess fired — re-run with the explicit delim above.

Pipeline Configuration: Parsing Lines Into Typed Columns

DuckDB's regexp_extract accepts a list of capture-group names and returns a struct, which lets you project the whole line into named columns in one pass. Build a view so every later query reads clean, typed fields instead of raw text.

CREATE VIEW logs AS
SELECT
  s.ip,
  strptime(s.ts, '%d/%b/%Y:%H:%M:%S %z')        AS request_time,
  s.method,
  s.path,
  CAST(s.status AS INTEGER)                       AS status,
  CAST(s.bytes  AS BIGINT)                         AS bytes,
  s.referer,
  s.ua
FROM (
  SELECT regexp_extract(
    line,
    '^(\S+) \S+ \S+ \[([^\]]+)\] "(\S+) (\S+)[^"]*" (\d{3}) (\d+|-) "([^"]*)" "([^"]*)"',
    ['ip','ts','method','path','status','bytes','referer','ua']
  ) AS s
  FROM raw
) t
WHERE s.status <> '';   -- drop lines the pattern could not parse

Expected Output: selecting a row now yields typed fields.

┌──────────────┬─────────────────────┬────────┬───────────────┬────────┬───────┐
│      ip      │    request_time     │ method │     path      │ status │ bytes │
├──────────────┼─────────────────────┼────────┼───────────────┼────────┼───────┤
│ 66.249.66.1  │ 2026-06-19 08:30:00 │ GET    │ /shoes?c=red  │  200   │  8421 │
└──────────────┴─────────────────────┴────────┴───────────────┴────────┴───────┘

Production Warning: Casting bytes to BIGINT fails if any line logs - for an empty body. The regex above already captures (\d+|-), so guard the cast with TRY_CAST(s.bytes AS BIGINT) if your logs mix numeric and dash values; TRY_CAST returns NULL instead of aborting the whole query on the first bad row.

Field-by-field breakdown. The path group deliberately captures everything up to the first space inside the quoted request, which preserves the query string — essential for detecting parameter crawl waste. The user-agent lands in ua, the field you match against to isolate crawlers, exactly as when you extract top bot user-agents from logs on the command line. Keeping request_time as a real TIMESTAMP (via strptime) is what makes hour-of-day and day-over-day crawl trends a one-line GROUP BY later.

Parsing Logic: Crawl Aggregations in SQL

With a typed logs view, the crawl-budget questions become ordinary SQL. Isolate Googlebot first, then slice it. A first-pass user-agent filter is enough for aggregation; confirm individual IPs with forward-confirmed reverse DNS as described in verifying Googlebot with reverse DNS before you act on the numbers.

-- Crawl distribution by status for Googlebot
SELECT status, count(*) AS hits,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct
FROM logs
WHERE ua ILIKE '%googlebot%'
GROUP BY status
ORDER BY hits DESC;

Expected Output:

┌────────┬───────┬──────┐
│ status │ hits  │ pct  │
├────────┼───────┼──────┤
│   200  │ 41980 │ 86.9 │
│   301  │  3902 │  8.1 │
│   404  │  1450 │  3.0 │
│   302  │   721 │  1.5 │
│   500  │   160 │  0.3 │
└────────┴───────┴──────┘

The window function sum(count(*)) OVER () gives each row its share of the total in the same pass — the kind of thing that turns into a two-stage pipe on the command line. To find which sections absorb the most crawl, group by the first path segment:

SELECT '/' || split_part(ltrim(path,'/'), '/', 1) AS section,
       count(*) AS crawl_hits
FROM logs
WHERE ua ILIKE '%googlebot%'
GROUP BY section
ORDER BY crawl_hits DESC
LIMIT 10;

Expected Output:

┌───────────┬────────────┐
│  section  │ crawl_hits │
├───────────┼────────────┤
│ /shoes    │      18204 │
│ /search   │       9120 │
│ /blog     │       6033 │
└───────────┴────────────┘

A crawler spending heavily on /search or faceted paths is wasting budget — the same signal you chase in finding crawl waste from URL parameters, now expressed as a HAVING clause instead of a sort-uniq chain.

Validation & Troubleshooting

Treat the parse as something to verify, not trust. A regex that silently fails to match drops rows, and a dropped-row rate you did not measure becomes wrong crawl numbers.

Health check: measure the parse rate. Compare parsed rows against raw rows; anything below ~99% means the pattern is missing a log variant.

SELECT (SELECT count(*) FROM logs)  AS parsed,
       (SELECT count(*) FROM raw)   AS total,
       round(100.0 * (SELECT count(*) FROM logs) / (SELECT count(*) FROM raw), 2) AS parse_pct;

Expected Output: parse_pct near 100. A value like 93.4 means a real chunk of traffic — often HTTP/2 request lines or lines with an extra field — is being discarded.

Recovery recipes for named failure modes:

• Empty result set / every column NULL. The field order does not match the regex. Nginx custom log_format directives reorder fields; align the pattern to your actual format before blaming the data.
• strptime conversion error. The timestamp locale or offset differs (for example +0000 vs Z). Adjust the format string, and normalize mixed offsets the way normalizing log timezones in Python does before comparing days.
• Out-of-memory on a huge log. DuckDB spills to disk, but set a bound explicitly with SET memory_limit='4GB'; and SET temp_directory='/var/tmp/duckdb'; so it uses disk instead of failing.
• Slow repeated queries. Persist the parsed view into a real table inside a database file so you parse once: duckdb crawl.duckdb then CREATE TABLE logs AS SELECT * FROM logs;. Subsequent sessions query the materialized table instantly.

Export the report. COPY writes any query result straight to CSV or Parquet for sharing or archival:

COPY (
  SELECT date_trunc('hour', request_time) AS hour, count(*) AS googlebot_hits
  FROM logs WHERE ua ILIKE '%googlebot%'
  GROUP BY hour ORDER BY hour
) TO 'googlebot_by_hour.csv' (HEADER, DELIMITER ',');

Expected Output: a googlebot_by_hour.csv file with one row per hour — the same series you would build to measure crawl rate by hour from logs, ready for a spreadsheet or dashboard.

Joining Logs to Reference Data In-Process

DuckDB's real advantage over a CLI pipeline is the join: it can combine your parsed log with any other file — a sitemap URL list, a CSV of known bot IP ranges, a product export — without loading either into a server. "Which crawled URLs are missing from my sitemap" becomes a one-line anti-join rather than a comm dance, and the join runs at columnar speed over the whole dataset.

-- Crawled paths absent from the sitemap: an anti-join, in one query
SELECT DISTINCT l.path
FROM logs l
LEFT JOIN read_csv('sitemap_paths.txt', columns={'path':'VARCHAR'}) s
  ON split_part(l.path,'?',1) = s.path
WHERE l.ua ILIKE '%googlebot%' AND s.path IS NULL
LIMIT 20;

Expected Output: the crawled-but-unlisted paths — orphan and parameter candidates — computed directly against the sitemap file, the same diagnosis as identifying orphan pages from log analysis but expressed as SQL over both sources at once. Because DuckDB reads the sitemap file in place too, there is no import step for either side; you point one query at two files and get the join. This is the capability that makes DuckDB more than "SQL over one log" — it is a local analytical engine that treats every file on disk as a table.

Persisting a Reusable Analysis Database

Re-parsing a multi-gigabyte log on every query wastes time, so the durable pattern is to parse once into a persistent DuckDB database file and query the materialized table thereafter. This turns an ad-hoc audit into a fast, reusable dataset that opens instantly in later sessions.

-- Parse once into a persistent database; later sessions query the table instantly
-- (run inside: duckdb crawl.duckdb)
CREATE TABLE crawl AS SELECT * FROM logs;   -- materialize the parsed view
CREATE INDEX idx_ua ON crawl(ua);            -- speed up repeated crawler filters

Expected Output: a crawl.duckdb file holding the fully parsed, typed data. Opening it in a new session and running a status-distribution query returns in milliseconds because the regex parse already happened. For a weekly crawl audit you parse the new week's log once, append it to the table, and every report thereafter is instant — the "materialize once, query many" economy that separates a repeatable pipeline from a one-off script, and the local-machine equivalent of the partitioned tables the BigQuery SEO log analysis guide builds at warehouse scale.

Reading Compressed and Remote Logs Directly

DuckDB reads gzipped logs natively and, with the httpfs extension, logs sitting in object storage — so the same query that analyzes a local .gz can run against a bucket without a download step. This is what lets a single-machine tool reach fleet-scale archives without becoming a fleet-scale system.

INSTALL httpfs; LOAD httpfs;
-- Query a gzipped log directly from object storage, no download
CREATE VIEW remote AS
SELECT column0 AS line
FROM read_csv('s3://fleet-logs/web-01/access-2026-06-19.log.gz',
              columns={'column0':'VARCHAR'}, delim='\x07', quote='');
SELECT count(*) FROM remote;

Expected Output: the line count read straight from the bucket, decompressed on the fly. A query over remote compressed input is CPU-bound on decompression and network-bound on transfer, so materialize hot data locally if you query it repeatedly — but for a one-off audit of an archived log, reading it in place from storage avoids ever staging a multi-gigabyte file. This reach into compressed and remote data is why DuckDB often handles the "occasional analysis of the archive" job that would otherwise seem to require a standing warehouse.

Exporting Results to Other Tools

An analysis is only useful if its output reaches the people and dashboards that need it, and DuckDB's COPY writes any query result straight to CSV, Parquet, or JSON for downstream consumption. This makes DuckDB a natural preprocessing stage: parse and aggregate the heavy raw log locally, then export a small, clean summary that a spreadsheet, a BI tool, or a warehouse can ingest without ever touching the multi-gigabyte source.

-- Export a compact daily crawl summary as Parquet for a dashboard or warehouse
COPY (
  SELECT request_time::DATE AS day,
         status,
         '/' || split_part(ltrim(path,'/'),'/',1) AS section,
         count(*) AS hits
  FROM logs WHERE ua ILIKE '%googlebot%'
  GROUP BY day, status, section
) TO 'crawl_summary.parquet' (FORMAT parquet);

Expected Output: a compact crawl_summary.parquet — a few thousand rows standing in for millions of log lines — that loads instantly into Looker Studio, a warehouse, or another DuckDB session. Parquet is columnar and typed, so the export preserves the schema and stays small; CSV is friendlier for a spreadsheet. Using DuckDB as the local heavy-lifting stage and exporting a summary is the pattern that lets a laptop-scale tool feed a team-scale dashboard, bridging the ad-hoc audit and the shared report without a standing pipeline in between.

Tuning Performance for Large Scans

DuckDB is fast by default, but a few settings matter when a log outgrows memory. The engine spills to disk automatically, yet you control the ceiling and the temp location, and for repeated analytical queries the biggest win is materializing the parsed data so the regex runs once rather than on every query. Knowing these levers keeps a large-log analysis from stalling or filling a disk.

SET memory_limit = '6GB';               -- cap RAM; DuckDB spills beyond it
SET temp_directory = '/var/tmp/duckdb'; -- where spill files live — needs space
SET threads = 8;                        -- parallelism across cores
-- Materialize once so the regex parse doesn't re-run per query
CREATE TABLE crawl AS SELECT * FROM logs;

Expected Output: queries that complete within the memory bound by spilling to the temp directory, and a materialized crawl table that subsequent queries hit directly at full columnar speed. The most common performance mistake is leaving the parse as a view and re-running the regex over the whole file on every query; materializing it once turns a slow, repeated parse into a one-time cost. Setting the memory limit and temp directory explicitly also prevents the two failure modes of a big scan — an out-of-memory abort and a filled disk — that otherwise surface only when the log is largest.

Handling Log-Format Variation Across Sources

Real fleets rarely emit one uniform format, and a DuckDB analysis that assumes a single regex silently drops the lines that do not match it. The robust approach detects the format per source and applies the right parse, or normalizes upstream so one pattern fits — the same format-drift problem the multi-server log centralization guide standardizes against, handled here at query time.

-- Measure the parse rate per source file to catch a format variant
SELECT filename,
       count(*) FILTER (WHERE regexp_matches(line, '^\S+ \S+ \S+ \[')) AS parsed,
       count(*) AS total
FROM read_csv('/data/logs/*.log', columns={'line':'VARCHAR'},
              delim='\x07', quote='', filename=true)
GROUP BY filename;

Expected Output: per-file parsed-versus-total counts, where a file with a low parsed ratio is using a format your regex does not match. The filename=true option tags each row with its source, so a single query over a whole directory pinpoints which node or source drifted. Measuring the parse rate rather than trusting one pattern is what keeps a multi-source DuckDB analysis honest — a silently-dropped format is invisible in the results but shows up immediately as a low parse ratio for the offending file.

When DuckDB Is the Right Choice — and When It Is Not

DuckDB sits at a specific point on the log-analysis spectrum, and using it well means knowing where that point is. It excels when a single analyst needs real analytical SQL — joins, window functions, grouping — over a log that fits on one machine's disk, with zero standing infrastructure and an answer in seconds. That covers the overwhelming majority of periodic SEO audits: a monthly crawl review, an ad-hoc "why did crawl drop" investigation, a one-off join against a sitemap. In those cases DuckDB is faster end to end than any alternative because there is nothing to set up, no service to keep running, and no ingestion lag between the log landing and the query returning.

It stops being the right tool at two boundaries. The first is continuous, multi-user querying: if a whole team needs always-on, low-latency access to the same crawl data with dashboards refreshing constantly, a standing system like the ELK Stack ingestion pipeline or a warehouse earns its operational cost, because DuckDB is a single-process engine, not a shared service. The second is genuine scale beyond one machine — petabytes of history, or concurrency across dozens of analysts — where a serverless warehouse like the one in the BigQuery SEO log analysis guide is built for exactly what DuckDB is not. The healthy mental model is that DuckDB replaces the awkward middle where you have outgrown awk but do not yet need a server; reaching for it there, and graduating deliberately when you cross one of those boundaries, is what keeps your log tooling matched to the actual need rather than over- or under-built.

Common Mistakes

• Letting read_csv guess the schema. Auto-detection tries to split the combined format on spaces and mangles the timestamp and request. Force a single VARCHAR column and parse with regex.
• Trusting the user-agent filter as verification. ILIKE '%googlebot%' counts spoofed traffic too. Use it for aggregation, but verify IPs by reverse DNS before making crawl-budget decisions.
• CAST instead of TRY_CAST on dirty fields. One - in the bytes column aborts the entire query. Use TRY_CAST so bad values become NULL and the report still runs.
• Re-parsing on every query. Leaving the parse as a view re-runs the regex over the whole file each time. Materialize it into a table (or a persistent .duckdb file) once.

Frequently Asked Questions

Is DuckDB fast enough for multi-gigabyte access logs?
Yes. DuckDB is a vectorized, columnar engine that reads and aggregates tens of millions of rows per second on a single machine, and it spills to disk when a query exceeds memory. For periodic crawl audits over daily or weekly logs it is typically faster end-to-end than standing up a search cluster, because there is no ingestion step — you query the file where it sits.

Do I need to import the log, or can DuckDB read it in place?
It reads in place. read_csv (and read_csv over a .gz file) streams the file directly, so a one-off analysis needs no load job at all. Persist to a .duckdb table only when you want to query the same parsed data repeatedly without re-parsing.

How does DuckDB compare to loading logs into ELK or BigQuery?
DuckDB wins on zero standing infrastructure and local, ad-hoc analysis; a search index like ELK wins when you need always-on, low-latency free-text search across many users, and a warehouse like BigQuery wins at petabyte scale and team-wide SQL access. Many teams use DuckDB for the audit and a warehouse for the archive.

Can I query compressed logs without decompressing first?
Yes — point read_csv at the .gz file and DuckDB decompresses on the fly. This keeps disk usage down on large archives, though a query over compressed input is CPU-bound on decompression, so materialize hot data if you query it often.

Related Guides

• Querying access logs with DuckDB — a focused walkthrough of the read_csv and regex parse step.
• Aggregating crawl stats with DuckDB SQL — status, section, and hour rollups in depth.
• Parsing 10GB logs with Python pandas efficiently — when you need row-level Python instead of SQL.
• ELK vs Vector.dev vs CloudWatch for SEO log pipelines — choosing a store-and-query layer for logs.

Part of the Log Parsing Workflows & CLI Toolchains series.