如何在AWS Athena中提取httprequest.headers里X-Forwarded-For的值

问题场景 #

你当前的Athena查询用于统计/login路径的请求，返回了请求计数、请求来源国家和完整的请求头数组，但需要从返回的headers数组中单独提取X-Forwarded-For对应的IP值。

原始查询语句：

SELECT "count"(*) "count"
    , "httprequest"."country"
    , "httprequest"."headers"
FROM waf_logs_17
WHERE ("httprequest"."uri" LIKE '/login')
GROUP BY "httprequest"."clientip", "httprequest"."country","httprequest"."headers"
ORDER BY "count" limit 5

查询返回的headers格式示例：
[{name=Host, value=app.onlinecheckwriter.com}, {name=X-Forwarded-For, value=75.113.195.00}, {name=X-Forwarded-Proto, value=https}]

解决方案 #

利用Athena支持的Presto SQL数组处理函数，直接在查询中筛选并提取目标字段：

基础提取写法

SELECT 
  count(*) AS "count",
  "httprequest"."country",
  -- 提取X-Forwarded-For对应的IP值
  element_at(
    filter("httprequest"."headers", h -> h.name = 'X-Forwarded-For'),
    1
  ).value AS x_forwarded_for
FROM waf_logs_17
WHERE "httprequest"."uri" LIKE '/login'
GROUP BY "httprequest"."clientip", "httprequest"."country", "httprequest"."headers"
ORDER BY "count" 
LIMIT 5

带空值处理的写法

如果存在请求未携带X-Forwarded-For头的情况，可通过coalesce返回默认值避免结果出现null：

SELECT 
  count(*) AS "count",
  "httprequest"."country",
  coalesce(
    element_at(
      filter("httprequest"."headers", h -> h.name = 'X-Forwarded-For'),
      1
    ).value,
    '无X-Forwarded-For头'
  ) AS x_forwarded_for
FROM waf_logs_17
WHERE "httprequest"."uri" LIKE '/login'
GROUP BY "httprequest"."clientip", "httprequest"."country", "httprequest"."headers"
ORDER BY "count" 
LIMIT 5

代码说明 #

• filter("httprequest"."headers", h -> h.name = 'X-Forwarded-For')：遍历headers数组，筛选出name为X-Forwarded-For的结构体元素
• element_at(..., 1)：取筛选结果数组的第一个元素（单请求中该请求头通常仅出现一次）
• .value：提取结构体中的value字段，即目标IP地址
• coalesce(..., 默认值)：当筛选结果为空时返回指定默认值，避免查询结果出现null

内容的提问来源于stack exchange，提问作者jewelhuq