Querying Access Logs with DuckDB

The single task this page solves: take a raw combined-format access log and end up with a typed, queryable table in DuckDB whose row count you trust. Get the read-and-parse step right and every crawl query afterward is ordinary SQL; get it wrong and you silently drop traffic and report confident, incorrect numbers.

The failure everyone hits first is asking DuckDB's CSV reader to split the log on spaces. The combined format is not space-delimited data — its timestamp is wrapped in brackets and its request line and user-agent are quoted strings full of spaces. So the fix is deliberate: read each line whole, then parse it with one regex. This mirrors the field-boundary problem covered in decoding the Apache combined log format, solved here in SQL.

Diagnosis: Confirm the Symptom in Real Output

If you let DuckDB auto-detect the schema, a single log line explodes into a jumble of columns split on every space, and the timestamp lands across two of them:

duckdb -c "SELECT * FROM read_csv_auto('/var/log/nginx/access.log') LIMIT 1;"

Expected Output (the symptom — misaligned columns):

┌─────────────┬─────────┬─────────┬──────────────┬─────────────┬───┐
│   column0   │ column1 │ column2 │   column3    │   column4   │ … │
│ 66.249.66.1 │    -     │    -    │ [19/Jun/2026 │ 08:30:00    │ … │
└─────────────┴─────────┴─────────┴──────────────┴─────────────┴───┘

The bracketed date split into [19/Jun/2026 and 08:30:00 is the tell: the reader treated the space inside the timestamp as a column boundary. Any query built on this is wrong.

Concept: Why Whitespace Splitting Fails Here

A combined log line has fields delimited by spaces except inside two structures — the [...] timestamp and the "..." quoted request and user-agent — where spaces are literal content. A generic CSV parser has no way to know that, so it over-splits. The robust approach is to stop treating the line as delimited data at all: load it as a single opaque string, then apply a regular expression whose grammar understands brackets and quotes. DuckDB's regexp_extract with a named-group list does exactly that in one projection.

Step-by-Step Fix

Step 1: Read each line as one column. Choose a delimiter byte that never occurs in a log line so the reader yields one row per line, untouched.

CREATE TABLE raw AS
SELECT column0 AS line
FROM read_csv('/var/log/nginx/access.log',
              header = false,
              columns = {'column0': 'VARCHAR'},
              delim = '\x07', quote = '');

Expected Output: CREATE TABLE succeeds; SELECT line FROM raw LIMIT 1; returns one intact log line including its quoted user-agent.

Step 2: Parse with a named-group regex. Project the raw line into a struct of named fields, then flatten to typed columns.

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

Expected Output: SELECT ip, status, path FROM logs LIMIT 1; returns typed values, e.g. 66.249.66.1 | 200 | /shoes?c=red.

Step 3: Query it. The table now answers crawl questions directly.

SELECT count(*) AS googlebot_hits
FROM logs
WHERE ua ILIKE '%googlebot%';

Expected Output:

┌────────────────┐
│ googlebot_hits │
├────────────────┤
│          48213 │
└────────────────┘

Edge-Case Handling

  • IPv6 clients. The (\S+) IP group already matches IPv6 addresses since they contain no spaces; no change needed. Downstream, treat the value as text, not an integer.
  • Extra trailing fields. Custom log_format directives that append response time or an upstream address add fields after the user-agent. The pattern anchors on the quoted UA and ignores anything after it, so it still matches — but capture the extra field explicitly if you need it.

Verification

Never trust a parse you have not measured. Compare parsed rows to raw rows; a healthy pattern parses ~99–100%.

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 pct;

Expected Output: pct at or near 100.00. A materially lower number means a log variant is slipping past the regex — inspect the unmatched lines with a WHERE regexp_matches(line, ...) = false filter before proceeding.

Why the Line-Whole Approach Is the Only Reliable One

The single decision that determines whether querying access logs with DuckDB works is treating each line as one opaque value and parsing it with a regex, rather than asking the CSV reader to split it into columns — and understanding why this is not just a preference but the only reliable approach clarifies the whole workflow. The combined log format is delimited by spaces except inside two structures where spaces are literal content: the bracketed timestamp and the quoted request and user-agent. A CSV reader has no concept of these structures; it sees spaces and splits on them, which shears the timestamp across two columns and breaks the request line at every internal space. No CSV-reader configuration fixes this, because the problem is that spaces mean two different things in the format and a delimiter-based parser cannot distinguish them.

The line-whole approach sidesteps the ambiguity entirely. By loading each line as a single string and applying a regular expression that explicitly models the format's grammar — matching the bracket-delimited timestamp and the quote-delimited fields as units — you parse with a tool that understands the structure the CSV reader cannot. The regex knows that the content between brackets is one field regardless of its internal spaces, and that the content between quotes is one field regardless of its spaces, so it extracts each field correctly where the delimiter split fails. This is why every reliable log parser, whether in DuckDB, Python, or any other tool, reads the line whole and applies a grammar-aware pattern rather than a delimiter split. Recognizing that the line-whole regex approach is not one option among several but the only one that correctly handles the combined format's structure is what saves you from the endless, futile attempts to configure a CSV reader into parsing a format it fundamentally cannot.

Measuring and Trusting the Parse Rate

A parse that silently drops lines produces confident, wrong analysis, so the discipline that makes DuckDB log querying trustworthy is measuring the parse rate — the fraction of raw lines the regex successfully matched — and treating anything below near-total as a problem to investigate rather than ignore. Because the regex simply fails to match lines it does not fit, those lines vanish from the parsed view with no error, so a pattern that handles ninety percent of your log format looks like it is working while silently discarding a tenth of your traffic. The only way to know is to compare the parsed row count against the raw row count.

The parse-rate check is a single query away and belongs at the start of any analysis, not as an afterthought. Comparing the count of successfully-parsed rows to the count of raw lines gives you the parse rate directly, and a rate near one hundred percent confirms the pattern fits your logs while a materially lower rate signals a format variant the regex is missing — an HTTP/2 request line, an extra field from a custom log format, a different timestamp format on some lines. When the rate is low, the fix is to inspect the unmatched lines, understand the variant, and widen the pattern to cover it, then re-measure until the parse rate is near total. This measure-and-widen loop is what keeps a DuckDB log analysis honest: the numbers you compute are only trustworthy if you know they are computed over essentially all your traffic, and the parse rate is the single metric that tells you whether they are. Making the parse-rate check the first step of any analysis, and never trusting results from a low or unmeasured parse rate, is the discipline that separates reliable log querying from confident misanalysis.

From Ad-Hoc Query to Reusable Dataset

The first time you query an access log with DuckDB, you parse it, run a query, and get an answer — but the more valuable pattern is to turn that one-time parse into a reusable dataset that answers many questions instantly, and recognizing when to make that shift is what elevates DuckDB from a query tool to an analysis platform. Re-parsing a large log on every query wastes the parse work repeatedly; materializing the parsed data once into a persistent table means the expensive regex parse happens a single time, and every subsequent query hits clean, typed, indexed storage that returns in milliseconds.

The shift is worth making as soon as you know you will ask the log more than one question. For a truly one-off inquiry, parsing in place and querying once is fine. But crawl analysis rarely asks just one question — you want the status distribution, then the crawl-by-section, then the day-over-day trend, then a join against the sitemap — and each additional question re-pays the parse cost if the data is left as a view. Materializing the parsed table once, into a persistent DuckDB database file, converts that repeated cost into a single upfront one, after which the whole battery of crawl questions runs instantly. Making the transition from ad-hoc parse to reusable materialized dataset at the point where you know the analysis is more than a single query is what makes DuckDB efficient for real crawl work, turning it from a tool you point at a log each time into a fast, standing dataset you query freely.

Trust the Parse Rate Before Any Number

The habit that makes DuckDB log querying reliable is refusing to trust any query result until the parse rate confirms the data is essentially complete. A regex that silently fails on some lines drops them from the parsed table with no error, so a query over that table returns a confident number computed on incomplete data. Checking the parse rate first — parsed rows against raw rows — is what tells you whether the numbers you are about to trust are computed over all your traffic or only the fraction the regex happened to match. Make the parse-rate check the first query of every analysis, and treat a low rate as a reason to fix the pattern before proceeding, because a result from an unmeasured parse is a result you cannot rely on.

Common Mistakes

  • Using read_csv_auto. Auto-detection splits on spaces and corrupts the timestamp and request. Force a single VARCHAR column.
  • CAST instead of TRY_CAST. A - in the bytes field aborts the whole query; TRY_CAST turns it into NULL and keeps the report running.

Frequently Asked Questions

Why not just set the CSV delimiter to a space?
Because spaces are valid content inside the bracketed timestamp and the quoted request and user-agent. A space delimiter over-splits those fields. Reading the line whole and applying one regex is the only reliable route for the combined format.

Does the regex approach scale to large logs?
Yes. regexp_extract runs inside DuckDB's vectorized engine over the whole table in one pass, and you can materialize the parsed view into a table so the regex runs only once. For row-level Python instead, see parsing 10GB logs with Python pandas efficiently.

Up next in the DuckDB Log Analytics for Crawl Analysis guide.