Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 38 ยท Projects and Practice

Project: Web Log Analysis

Parse and analyze web server access logs: traffic per hour, error rates, slowest endpoints with percentiles, top visitors and sessions, using SQL that scales to GBs in DuckDB.

Web server logs are a perfect DuckDB workload: large, append-only, text, and full of questions. Which pages are slow? When do errors spike? Who is hammering the API? In this project you parse a log, then answer those questions with SQL. The pipeline shape is exactly what you would run on gigabytes of logs; here the data is small so you can run every step.

The real DuckDB cannot run on this site, so file-reading and regex statements are read-only. The analysis queries run on a small inline table with portable SQL.

Step 1: parsing the raw lines (read-only)

A typical access-log line looks like:

TEXT
203.0.113.7 - - [01/Mar/2026:09:05:12 +0000] "GET /products/pen?ref=ad HTTP/1.1" 200 5123 0.043

In DuckDB you load it with read_csv as a single column and pull out the fields with regexp_extract:

SQL
CREATE TABLE logs AS
WITH raw AS (
  SELECT column0 AS line
  FROM read_csv('access.log', columns = {'column0': 'VARCHAR'},
                delim = '\x01', header = false, quote = '', escape = '')
)
SELECT
  regexp_extract(line, '^(\S+)', 1)                          AS ip,
  strptime(regexp_extract(line, '\[([^\]]+)\]', 1), '%d/%b/%Y:%H:%M:%S %z') AS ts,
  regexp_extract(line, '"(\S+) ', 1)                         AS method,
  regexp_extract(line, '"\S+ (\S+?)(\?|\s)', 1)              AS path,
  regexp_extract(line, '" (\d{3}) ', 1)::INTEGER             AS status,
  regexp_extract(line, '" \d{3} (\d+)', 1)::BIGINT           AS bytes,
  regexp_extract(line, ' ([\d.]+)$', 1)::DOUBLE              AS seconds
FROM raw
WHERE line <> '';

Choosing an unused delimiter (\x01) makes read_csv treat each line as one field. Many logs are JSON lines instead, in which case read_json does the parsing for you. Real pipelines then save the parsed table as Parquet so later runs skip the regex work: COPY logs TO 'logs.parquet'.

For the rest of the project we start from a parsed table. The columns are: ts (text in ISO format), ip, path, status, bytes, ms (response time in milliseconds).

Step 2: traffic and errors per hour

SQL Requests and error rate per hour

Press Run (or Ctrl + Enter) to see the result here.

A boolean expression sums as 0 or 1 here. In DuckDB the clearer form is count(*) FILTER (status >= 500). Spikes in server_errors guide you to the hour to investigate.

Step 3: slowest endpoints

The average hides pain. Percentiles show what the unlucky users see. DuckDB has quantile_cont(ms, 0.95) and approx_quantile for large data. In portable SQL we can compute a nearest-rank percentile with a window:

SQL Latency per path: average, worst and 90th percentile

Press Run (or Ctrl + Enter) to see the result here.

For each path the 90th percentile is the first value at or above 90% of the sorted rows (nearest-rank). In DuckDB: SELECT path, count(*), avg(ms), quantile_cont(ms, 0.9) AS p90 FROM logs GROUP BY path. Notice /api/cart is the endpoint to optimize.

Step 4: top visitors and suspicious clients

SQL Heaviest clients and their error ratio

Press Run (or Ctrl + Enter) to see the result here.

10.0.0.5 fails to log in four times in a row: a brute-force pattern. SUM(COUNT(*)) OVER () is an aggregate over the aggregated rows, a window on top of GROUP BY. Alert rules built on such queries can run on a schedule.

Step 5: sessions per visitor

Define a session as activity with no gap longer than 30 minutes (see time series):

SQL Sessionize with LAG and a running sum

Press Run (or Ctrl + Enter) to see the result here.

Step 6: scaling up

Logs grow fast. With DuckDB:

  • Convert each day's logs to Parquet once: COPY (SELECT ... FROM raw) TO 'logs/day=2026-03-01/part.parquet'.
  • Query many days at once: FROM read_parquet('logs/*/*.parquet', hive_partitioning = true) WHERE day >= '2026-03-01', partition pruning keeps it quick (see incremental loads).
  • Use approx_count_distinct(ip) for unique visitors and approx_quantile(ms, 0.99) for tail latency on billions of rows.
  • Use SELECT ... FROM logs USING SAMPLE 1% while exploring.
  • Filter bots with a user_agent ILIKE '%bot%' condition and compare their share.
flowchart LR
    A["access.log files"] --> B["read_csv + regexp_extract"]
    B --> C["Parquet, partitioned by day"]
    C --> D["SQL: hourly traffic, p95 latency, sessions"]
    D --> E["Dashboard or alert"]

Parse once, store typed columns, query many times.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why look at percentiles instead of only the average response time?

    Tail latency (p90, p95, p99) shows what the slowest users experience.
  2. 2. How does the session query start a new session?

    LAG finds the gap; the running sum of the flags gives each session a number.
  3. 3. Why store parsed logs as Parquet?

    Parsing once and keeping typed columns speeds up all later queries.
  4. 4. What does SUM(COUNT(*)) OVER () do after a GROUP BY?

    Window functions run after grouping, so they can aggregate the grouped rows.

Summary

  • Parse text logs once with read_csv plus regexp_extract (or read_json), then save typed Parquet.
  • Hourly counts, error rates, per-endpoint percentiles and per-client ratios answer most operational questions.
  • Sessions come from LAG gaps and a running sum.
  • At scale use partitioned Parquet, approximate aggregates and sampling.

Next: Project: A Parquet Data Lake Pipeline