Use DuckDB to convert a compressed web access log in Combined Log Format to Parquet

access-log-to-parquet.sql

COPY (
WITH
  -- Read the raw log line by line by abusing CSV parser
  raw_log AS (
    FROM read_csv_auto('/tmp/log/access.log-20230904.gz', header=false, delim='\0')
)
, combined_log AS ( 
    SELECT regexp_extract(column0
                         , '^(\S+) (\S+) (\S+) \[(.*?)\] "([A-Z]+?) (.*?) HTTP/(.*?)" (\d+) (\d+) "(.*?)" "(.*?)"$'
                         , [ 'ip', 'identity', 'userid', 'timestamp', 'method'
                           , 'request', 'proto', 'status', 'bytes', 'referrer', 'agent']) AS fields
    FROM raw_log            
)
SELECT fields.ip
     , NULLIF(fields.identity, '-') AS identity
     , NULLIF(fields.userid, '-') as userid
     , strptime(fields.timestamp, '%d/%b/%Y:%H:%M:%S %z') as timestamp
     , fields.method
     , fields.request
     , fields.proto::DECIMAL(3,2) as proto
     , NULLIF(fields.status, '-')::USMALLINT AS status
     , NULLIF(fields.bytes, '-')::UINTEGER AS bytes
     , NULLIF(fields.referrer, '-') AS referrer
     , fields.agent
FROM combined_log
)
TO '/tmp/log/access.log-20230904.gz.parquet';