Parse Log Lines — Solution & ExplanationProblems / Parse Log Lines / EditorialApproach

Timestamp: regexp_extract(raw_line, '^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})', 1) anchors at the start of the line and captures the 19-character datetime, then CAST(... AS TIMESTAMP) converts the string into a real timestamp.

Level: \[(\w+)\] captures the word between literal brackets — the brackets must be escaped because [...] is regex syntax for a character class.

Message: \[\w+\] (.*)$ skips past the bracketed level and the single space, capturing the rest of the line.

Compute all three in a CTE, then filter level IN ('ERROR', 'WARN') and order by log_id.

Key InsightSemi-structured logs are parsed by anchoring on the rigid parts (position of the timestamp, the bracketed level) and capturing the free-form remainder. Doing the extraction in a CTE keeps the filter readable — the outer query works on named, typed columns instead of repeating three regex calls in the WHERE clause.Hints

Three separate regexp_extract calls are easier to debug than one giant pattern: '^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})' for the timestamp, '\[(\w+)\]' for the level, '\[\w+\] (.*)$' for the message.

Square brackets are regex metacharacters — escape them as \[ and \] to match the literal [LEVEL] delimiters.

regexp_extract returns a string; CAST it to TIMESTAMP (or .cast('timestamp') in PySpark) for event_time.

The WHERE clause references the derived level column — compute the parsed columns in a CTE first, then filter the CTE.

Solve Parse Log Lines yourself →