Hive RegexSerDe 解析 Nginx 訪問日誌實戰

Hive RegexSerDe 解析 Nginx 訪問日誌實戰

環境

Hadoop 2.6.0 + Apache Hive 1.2.0

日誌樣本

192.168.1.128 - - [09/Jan/2015:12:38:08 +0800] "GET /avatar/helloworld.png HTTP/1.1" 200 1521 "https://write.blog.csdn.net/postlist" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/34.0.1847.131 Safari/537.36"
183.60.212.153 - - [19/Feb/2015:10:23:29 +0800] "GET /o2o/media.html?menu=3 HTTP/1.1" 200 16691 "-" "Mozilla/5.0 (compatible; EasouSpider; +https://www.easou.com/search/spider.html)"

九欄位：Client IP、User Identity、User、Timestamp、Request、Status、Size、Referer、User-Agent。欄位間以空格分隔，但 Request/Referer/User-Agent 含空格，需正則精準提取。

正則表達式

([^ ]*) ([^ ]*) ([^ ]*) (\[.*\]) (\".*?\") (-|[0-9]*) (-|[0-9]*) (\".*?\") (\".*?\")

建表語句

CREATE TABLE logs (
  host STRING,
  identity STRING,
  username STRING,
  time STRING,
  request STRING,
  status STRING,
  size STRING,
  referer STRING,
  agent STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (\\[.*\\]) (\\".*?\\") (-|[0-9]*) (-|[0-9]*) (\\".*?\\") (\\".*?\\")",
  "output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s"
)
STORED AS TEXTFILE;

載入數據與查詢

LOAD DATA LOCAL INPATH '/home/hadoop/hivetestdata/nginx.txt' INTO TABLE logs;

-- 查詢每小時訪問量超過 100 的 IP
SELECT substring(time, 2, 14) AS datetime, host, COUNT(*) AS cnt
FROM logs
GROUP BY substring(time, 2, 14), host
HAVING cnt > 100
ORDER BY datetime, cnt;

關鍵點

• RegexSerDe 內建於 Hive，無需額外 JAR。
• input.regex 需雙重轉義（Hive 字符串 + 正則），注意 \\[、\\"。
• output.format.string 定義 INSERT 時的序列化格式，通常保持與輸入一致。
• 時間欄位含 []，查詢時用 substring(time, 2, 14) 提取 09/Jan/2015:12 格式。