haproxy.clickhouse.sql

CREATE MATERIALIZED VIEW haproxy.access_logs_view
(
    remote_addr String,
    remote_port Int32,
    ts DateTime,
    frontend LowCardinality(String),
    tls Boolean,
    backend LowCardinality(String),
    server LowCardinality(String),
    time_request Int32,
    time_queue Int32,
    time_connect Int32,
    time_response Int32,
    time_active Int32,
    status Int16,
    bytes_read Int64,
    captured_request_cookie String,
    captured_response_cookie String,
    termination_state LowCardinality(String),
    active_conn Int32,
    frontend_conn Int32,
    backend_conn Int32,
    server_conn Int32,
    server_queue Int32,
    backend_queue Int32,
    retries Int32,
    host String,
    referrer String,
    method LowCardinality(String),
    path String,
    protocol LowCardinality(String)
)
ENGINE = MergeTree()
ORDER BY ts
POPULATE AS
WITH
    extractAllGroups(message, '^(\w+ \d+ \S+) (\S+) (\S+)\[(\d+)\]: (\S+):(\d+) \[(\S+)\] (\S+) (\S+)/(\S+) (\S+) (\S+) (\S+) *(\S+) (\S+) (\S+) (\S+) (\S+) \{([^}]*)\} "(\S+) ([^"]+) (\S+)" *$') as split,
    splitByChar('/', split[1][11]) as timestamps,
    splitByChar('/', split[1][17]) as connections,
    splitByChar('/', split[1][18]) as queue,
    splitByChar('|', split[1][19]) as headers
SELECT
    split[1][5] as remote_addr,
    split[1][6] as remote_port,
    parseDateTimeBestEffort(replaceOne(split[1][7], ':', ' ')) as ts,
    trim(TRAILING '~' FROM split[1][8]) as frontend,
    endsWith(split[1][8], '~') as tls,
    split[1][9] as backend,
    split[1][10] as server,
    timestamps[1] as time_request,
    timestamps[2] as time_queue,
    timestamps[3] as time_connect,
    timestamps[4] as time_response,
    timestamps[5] as time_active,
    split[1][12] as status,
    split[1][13] as bytes_read,
    split[1][14] as captured_request_cookie,
    split[1][15] as captured_response_cookie,
    split[1][16] as termination_state,
    connections[1] as active_conn,
    connections[2] as frontend_conn,
    connections[3] as backend_conn,
    connections[4] as server_conn,
    connections[5] as retries,
    queue[1] as server_queue,
    queue[2] as backend_queue,
    headers[1] as host,
    headers[2] as referrer,
    split[1][20] as method,
    split[1][21] as path,
    split[1][22] as protocol
FROM
    (SELECT message FROM haproxy.raw_logs)
WHERE notEmpty(split)