Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 36 ยท Projects and Practice

Project: Web Log Analytics

Build a web log analytics pipeline in Hive: external table over raw logs, partitioned ORC fact table, daily reports, error analysis and sessionization with window functions.

In this project you build the most classic Hive workload: turning raw web server logs into a fast, queryable warehouse. You will define a raw table, convert it to a partitioned ORC table, then write the reports a product team asks for: traffic per hour, top pages, error rates and visitor sessions.

The Hive DDL and loading steps are read-only because Hive cannot run here. Every analysis query runs in the editors on a small log table that you create yourself, using SQL that works the same in HiveQL (function names that differ are pointed out).

The plan

flowchart LR
    A["Raw logs in /data/raw/access/dt=..."] --> B["External table: access_raw (RegexSerDe)"]
    B -->|"INSERT ... SELECT, cast, clean"| C["access_log: ORC, partitioned by dt"]
    C --> D["Reports: traffic, pages, errors, sessions"]

Land raw, convert once, query the clean columnar copy.

Step 1: raw external table (Hive, read-only)

Each log line looks like 203.0.113.7 - - [2026-03-01 08:15:42] "GET /pricing HTTP/1.1" 200 5120 "Mozilla/5.0". An external table with RegexSerDe exposes the lines without moving the files:

SQL
CREATE EXTERNAL TABLE access_raw (
  ip STRING, ts STRING, method STRING, path STRING,
  status STRING, bytes STRING, agent STRING
)
PARTITIONED BY (dt STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'input.regex' = '^(\\S+) \\S+ \\S+ \\[([^\\]]+)\\] "(\\S+) (\\S+) [^"]*" (\\d{3}) (\\S+) "([^"]*)"$'
)
STORED AS TEXTFILE
LOCATION '/data/raw/access/';

MSCK REPAIR TABLE access_raw;   -- register the dt=YYYY-MM-DD folders

Step 2: typed ORC table (Hive, read-only)

SQL
CREATE TABLE access_log (
  ip STRING, ts TIMESTAMP, method STRING, path STRING,
  status INT, bytes BIGINT, agent STRING
)
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'ZSTD');

SET hive.exec.dynamic.partition.mode = nonstrict;

INSERT OVERWRITE TABLE access_log PARTITION (dt)
SELECT ip,
       CAST(ts AS TIMESTAMP),
       method, path,
       CAST(status AS INT),
       CASE WHEN bytes = '-' THEN 0 ELSE CAST(bytes AS BIGINT) END,
       agent, dt
FROM access_raw
WHERE ip IS NOT NULL;          -- drops lines the regex could not parse

ANALYZE TABLE access_log PARTITION (dt) COMPUTE STATISTICS FOR COLUMNS;

Three habits are at work: parse once, cast to real types, and gather statistics (ORC and Parquet, EXPLAIN). Because queries filter on dt, each report reads only the days it needs.

Step 3: sample data to explore

Create a small table with the same shape. The column types are SQLite's, but the data is what the Hive table would hold:

SQL Create the sample log table

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

Each editor starts from an empty database, so every later query below begins by recreating this data in a compact form. In a real Hive session you would simply query access_log.

Step 4: traffic reports

Requests and bandwidth per day (Hive: GROUP BY dt, a partition column, so only needed partitions are read):

SQL Daily traffic

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

In HiveQL, hour(ts) returns the hour of a timestamp, and date_format(ts, 'yyyy-MM-dd HH') buckets by hour.

Top pages and error rate:

SQL Top pages and error rate

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

In HiveQL, integer division with / gives a decimal, so write CAST(status / 100 AS INT) or floor(status / 100). SQLite divides integers as integers, which is why the code above works unchanged here.

Step 5: sessionization

A session is a run of requests from one visitor with no gap longer than 30 minutes. The standard technique uses LAG to detect gaps, then a running SUM of the "new session" flags to number the sessions. The query is the same in HiveQL, with unix_timestamp(ts) instead of strftime('%s', ts).

SQL Sessions with window functions

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

10.0.0.1 has two sessions (a two-hour gap splits them) and 10.0.0.2 has two as well (50 minutes apart). Session counts and length are the basis of engagement metrics.

Step 6: a daily summary table (Hive, read-only)

Dashboards should not rescan raw logs. Build a small aggregate table every day:

SQL
CREATE TABLE daily_summary (
  path STRING, hits BIGINT, errors BIGINT, bytes BIGINT
)
PARTITIONED BY (dt STRING)
STORED AS ORC;

INSERT OVERWRITE TABLE daily_summary PARTITION (dt = '2026-03-01')
SELECT path, COUNT(*), SUM(CASE WHEN status >= 400 THEN 1 ELSE 0 END), SUM(bytes)
FROM access_log
WHERE dt = '2026-03-01'
GROUP BY path;

INSERT OVERWRITE with an explicit partition makes the job idempotent: rerunning it for the same day replaces that day instead of duplicating rows. Schedule it with Oozie, Airflow or cron after the day's logs have landed.

Extensions to try

  • Add a user_agent classification with CASE WHEN agent LIKE '%Mobile%'.
  • Bucket access_log by ip for faster per-visitor joins (skew and bucket joins).
  • Track the top referrers or the funnel / then /pricing then /signup, counting visitors that reach each step.
  • Compare EXPLAIN for a query with and without the dt filter.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why convert raw text logs into a partitioned ORC table?

    Parsing and typing once, and reading only needed partitions and columns, is faster and cheaper.
  2. 2. How do you split a visitor's requests into sessions with window functions?

    The running sum of new-session flags gives each request its session number.
  3. 3. Why use INSERT OVERWRITE ... PARTITION (dt = ...) for the daily summary?

    Overwriting one partition makes the daily job safe to retry.
  4. 4. What does the RegexSerDe return for a line that does not match the pattern?

    Unmatched lines produce NULLs; that is why the conversion step filters on ip IS NOT NULL.

Summary

  • Land raw logs in an external table, parse them with a SerDe, and convert once into a typed, partitioned ORC table.
  • Daily traffic, top pages, hourly profiles and error rates are plain GROUP BY queries on partition and clean columns.
  • Sessionization is LAG, a gap flag and a running SUM.
  • Pre-aggregate into a summary table with idempotent INSERT OVERWRITE per partition.

Next: Project: Sales Data Warehouse